I need to create the same database on many server instances

Question

SQL Server 2008, Visual Studio 08 and C#

The task is to create the same database on multiple servers. If it were only two or three tables I would have done it manually but there are more than 50 tables in the database..

So I thought why not create a backup and restore the backup file wherever needed

error

the file is in use! ( although the backup is not in use, the error is saying that the actual db is in use! YES IT IS, i cannot close the server each time i want to restore the backup on other servers!)

So what should I do, please give your ideas

And also note

Whatever you say should be achievable using SMO objects also

thank you

Answer 1

Prior to running the restore, you can run the stored procedure 'sp_who2' and it will list the current connections to the database. If you are going to overwrite the DB anyways, for each of those connections you can issue a 'kill ' to forceably close the connections.

I also recall there being some "close all existing connections" option when using the restore GUI in Sql management studio.

Answer 2

Use usp_killDBConnections @DBName=''DbName''

before you run restore operation. The command will kill all DB connection before Restore, which is important during restoration.

Answer 3

[REVISED - I need to learn to read better]

I'm not certain about SMO Objects, but given that SMO can work like other SQL Server functionality, what we do for our project is to use a Database Project that deploys to our servers automatically. This probably requires Database Edition (VS 2008) or higher...premium in 2010. If you have that, it's definitely a nice option to create a DB project. Then, you just set it up to do a schema compare (and you might be able to do a data compare as well if you need that...?) during deployment. Auto-deployment is harder to setup initially, but once it's setup, you're good to go with single click deployments...we use TFS to deploy right now, but I hear good things about TeamCity:

http://www.jetbrains.com/teamcity/

Kevin