CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008
Gaurav Gupta
Gaurav Gupta

Reputation: 167

How to create clone of database programatically

I want to create clone of database programatically. My requirement is, I have a default master template of database, I want to create a new database when my application will execute and this new database will be copy of existing master database, just its name will be changed.

Upvotes: 0

Views: 1785

Answers (2)

Szymon
Szymon

Reputation: 43023

One option is to create a backup of the master database and restore it under a different name using a script:

RESTORE DATABASE [CopyOfMaster] FROM  
DISK = N'C:\Temp\Master.bak' 
WITH  FILE = 1,  
MOVE N'Master' TO N'C:\Temp\copy.mdf',  
MOVE N'Master_log' TO N'C:\Temp\copy_log.ldf',  
NOUNLOAD
GO

Explanation of parameters:

  • DISK - path and file of your backup file
  • MOVE - you need one line for each file group in the original database. You specify new file name and path for each file group

More reading on MSDN

Upvotes: 2

user2930590
user2930590

Reputation:

You need to script your master template database and save the script to a file. Use SQL Server Management Studio, right click on your master template database and select 'script database as'. You will have to change the database name in the script to some identifier which you can perform a string replace to change the name at runtime.

In your code you can load the script file and execute to create your database with a different name. You can make use of the SqlCommand to execute the loaded script (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx)

You can also make use of the command line 'sqlcmd' utility or SQL Server Management Object(SMO). Although I have not explored any of those options.

Upvotes: 0

Related Questions