What is the best way to copy a database?

Question

When I want to make a copy of a database, I always create a new empty database, and then restore a backup of the existing database into it. However, I'm wondering if this is really the least error-prone, least complicated, and most efficient way to do this?

Answer 1

It is possible to skip the step of creating the empty database. You can create the new database as part of the restore process.

This is actually the easiest and best way I know of to clone a database. You can eliminate errors by scripting the backup and restore process rather than running it through the SQL Server Management Studio

There are two other options you could explore:

1. Detach the database, copy the .mdf file and re-attach.
2. Use SQL Server Integration Services (SSIS) to copy all the objects over

I suggest sticking with backup and restore and automating if necessary.

Answer 2

Here's a dynamic sql script I've used in the past. It can be further modified but it will give you the basics. I prefer scripting it to avoid the mistakes you can make using the Management Studio:

 Declare @OldDB varchar(100) Declare @NewDB varchar(100) Declare @vchBackupPath varchar(255) Declare @query varchar(8000)   /*Test code to implement  Select @OldDB = 'Pubs' Select @NewDB = 'Pubs2' Select @vchBackupPath = '\\dbserver\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\pubs.bak' */  SET NOCOUNT ON;  Select @query = 'Create Database ' + @NewDB exec(@query)  Select @query = ' Declare @vBAKPath varchar(256) declare @oldMDFName varchar(100) declare @oldLDFName varchar(100) declare @newMDFPath varchar(100) declare @newLDFPath varchar(100) declare @restQuery varchar(800)  select @vBAKPath = ''' + @vchBackupPath + ''' select @oldLDFName = name from ' + @OldDB +'.dbo.sysfiles where filename like ''%.ldf%'' select @oldMDFName = name from  ' + @OldDB +'.dbo.sysfiles where filename like ''%.mdf%'' select @newMDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''ROWS'' select @newLDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''LOG''  select @restQuery = ''RESTORE DATABASE ' + @NewDB +  ' FROM DISK = N'' + '''''''' + @vBAKpath + '''''''' +  '' WITH MOVE N'' + '''''''' + @oldMDFName + '''''''' +   '' TO N'' + '''''''' + @newMDFPath + '''''''' +   '', MOVE N'' + '''''''' + @oldLDFName + '''''''' +   '' TO N'' + '''''''' + @newLDFPath + '''''''' +   '', NOUNLOAD, REPLACE, STATS = 10''  exec(@restQuery) --print @restQuery'   exec(@query)