I need to create the same database on many server instances

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 restor开发者_运维问答e 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

[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

Use usp_killDBConnections @DBName=''DbName''

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

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.