cloning a database with backing up possible?

Question

I need to write a tool to clone a database, instead of detaching it and copying the .mdf and .ldf, wouldn't it be better to just back it up and restore a newly created db?

Is there a way using SQL to create a database from a .bak?

Answer 1

May be it will be something like this:

RESTORE DATABASE [NewDB] 
    FROM  DISK = N'Path\file.bak' 
    WITH  FILE = 1,  
    MOVE N'NewDB_Data' TO N'C:\Data\MSSQL.1\MSSQL\Data\NewDB_Data.mdf',  
    MOVE N'NewDB_Log' TO N'C:\Data\MSSQL.1\MSSQL\Data\NewDB_Log.ldf',  
    NOUNLOAD,  STATS = 10

Answer 2

Easiest way if you are new to this I would say is to

a) Create a database in SQL Management Studio but instead of clicking "OK" to create it, script out the action as an SQL script and run it.

b) Restore the .bak file in management studio making sure you get the backup file locations pointing in the correct place and again script out the action instead of clicking OK.

c) Merge two files into 1 to give you a "clone database" sql script.

Answer 3

Yes, you can use backup as a method of cloning a database. For this, you can simply use the RESTORE command.

For example:

RESTORE DATABASE    DatabaseName
FROM DISK = N'C:\Path\To\Your\File.bak'

For further reference about parameters for the RESTORE command, have a look at the MSDN reference for it: Click!

Answer 4

Use c#.net to achieve functionality you want here is line of the article which talks about creating application to back and restore database : http://www.geekpedia.com/tutorial180_Backup-and-restore-SQL-databases.html

Answer 5

We use SQL Server 2005 and are continually copying and swapping databases for testing purposes. We ALWAYS now use backup and restore. Detaching, reattaching .mdf and .ldf files has very frequently caused SQL Server to become corrupt to the point of requiring re-installation, and a very ugly cleanup before that is even possible. As several others have suggested, using a .bak file is pretty straightforward.

Answer 6

You can do this with backup as already answered. No one has mentioned yet though that it is important to use the COPY ONLY option when doing these ad hoc backups to avoid mullering your main backups

BACKUP DATABASE YourDB
TO DISK = 'C:\Backups\YourDB.bak'
WITH COPY_ONLY; 

Answer 7

To extend on Maximilian Mayerl's answer, I would recommend using MSBuild and the ExecuteDDL task of the MSBuild Community Tasks library to automate this process.

You would start with a SQL script like this one (perhaps called CloneDb.sql):

USE master
GO

RESTORE DATABASE dbname
   FROM DISK = 'SOURCEDIR\dbname.bak'
   WITH REPLACE, FILE = 1,  
    MOVE N'dbname' TO N'DBDEVICEDIR\dbname.mdf',  
    MOVE N'dbname_log' TO N'DBDEVICEDIR\dbname_log.LDF',  
    NOUNLOAD,  
    STATS = 10
GO

In the MSBuild script, you'd create a target that includes a sequence like this:

<FileUpdate Files="$(BuildDir)\CloneDb.sql"
                Regex="SOURCEDIR"
                ReplacementText="$(SqlSafeActualBuildDir)\dbdeploy" />
    <FileUpdate Files="$(BuildDir)\CloneDb.sql"
                Regex="DBDEVICEDIR"
                ReplacementText="$(SqlSafeActualBuildDir)\dbdevices" />
    <ExecuteDDL Files="$(BuildDir)\CloneDb.sql" ConnectionString="Server=$(LocalDbServer);Database=master;Trusted_Connection=True;" />

With this in place, running "msbuild.exe CloneDb.proj /t:" from a Visual Studio command-line will clone your database in one step. You can put the command in a batch file for convenience.

I created a more elaborate version of this for my current project, where a team of over a dozen developers uses it to create local versions of the databases our project uses for their own development needs.