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

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 2

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.