<p>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?</p>

[{'answer_id': 1617, 'author': 'Justin Walgran', 'author_id': 173, 'author_profile': 'https://Stackoverflow.com/users/173', 'pm_score': 3, 'selected': False, 'text': '<p>It is possible to skip the step of creating the empty database. You can create the new database as part of the restore process.</p>\n\n<p>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</p>\n\n<p>There are two other options you could explore:</p>\n\n<ol>\n<li>Detach the database, copy the .mdf file and re-attach.</li>\n<li>Use SQL Server Integration Services (SSIS) to copy all the objects over</li>\n</ol>\n\n<p>I suggest sticking with backup and restore and automating if necessary.</p>\n'}, {'answer_id': 1646, 'author': 'brendan', 'author_id': 225, 'author_profile': 'https://Stackoverflow.com/users/225', 'pm_score': 2, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>\nDeclare @OldDB varchar(100)\nDeclare @NewDB varchar(100)\nDeclare @vchBackupPath varchar(255)\nDeclare @query varchar(8000)\n\n\n/*Test code to implement \nSelect @OldDB = 'Pubs'\nSelect @NewDB = 'Pubs2'\nSelect @vchBackupPath = '\\\\dbserver\\C$\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\pubs.bak'\n*/\n\nSET NOCOUNT ON;\n\nSelect @query = 'Create Database ' + @NewDB\nexec(@query)\n\nSelect @query = '\nDeclare @vBAKPath varchar(256)\ndeclare @oldMDFName varchar(100)\ndeclare @oldLDFName varchar(100)\ndeclare @newMDFPath varchar(100)\ndeclare @newLDFPath varchar(100)\ndeclare @restQuery varchar(800)\n\nselect @vBAKPath = ''' + @vchBackupPath + '''\nselect @oldLDFName = name from ' + @OldDB +'.dbo.sysfiles where filename like ''%.ldf%''\nselect @oldMDFName = name from  ' + @OldDB +'.dbo.sysfiles where filename like ''%.mdf%''\nselect @newMDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''ROWS''\nselect @newLDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''LOG''\n\nselect @restQuery = ''RESTORE DATABASE ' + @NewDB + \n' FROM DISK = N'' + '''''''' + @vBAKpath + '''''''' + \n'' WITH MOVE N'' + '''''''' + @oldMDFName + '''''''' +  \n'' TO N'' + '''''''' + @newMDFPath + '''''''' +  \n'', MOVE N'' + '''''''' + @oldLDFName + '''''''' +  \n'' TO N'' + '''''''' + @newLDFPath + '''''''' +  \n'', NOUNLOAD, REPLACE, STATS = 10''\n\nexec(@restQuery)\n--print @restQuery'\n\n\nexec(@query)\n\n\n\n\n\n</code></pre>\n"}, {'answer_id': 1776, 'author': 'caryden', 'author_id': 313, 'author_profile': 'https://Stackoverflow.com/users/313', 'pm_score': 0, 'selected': False, 'text': '<p>The <strong>Publish to Provider</strong> functionality has worked great for me.  See <a href="http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx" rel="nofollow noreferrer">Scott Gu\'s Blog Entry</a>.</p>\n\n<p>If you need something really robust look  at redgate software\'s tools <a href="http://www.red-gate.com/" rel="nofollow noreferrer">here</a>...if you are doing much SQL at all, these are worth the $$.</p>\n'}, {'answer_id': 1793, 'author': 'Brett Veenstra', 'author_id': 307, 'author_profile': 'https://Stackoverflow.com/users/307', 'pm_score': 3, 'selected': True, 'text': "<p>Backup and Restore is the most straight-forward way I know.  You have to be careful between servers as security credentials don't come with the restored database.</p>\n"}, {'answer_id': 544998, 'author': 'Yordan Georgiev', 'author_id': 65706, 'author_profile': 'https://Stackoverflow.com/users/65706', 'pm_score': 0, 'selected': False, 'text': '<pre><code>::================ BackUpAllMyDatabases.cmd ============= START\n::BackUpAllMyDatabases.cmd\n:: COMMAND LINE BATCH SCRIPT FOR TAKING BACKUP OF ALL DATABASES \n\n::RUN THE SQL SCRIPT VIA THE COMMAND LINE WITH LOGGING \nsqlcmd -S localhost -e  -i "BackUpAllMyDatabases.sql" -o Result_Of_BackUpAllMyDatabases.log\n\n::VIEW THE RESULTS\nResult_Of_BackUpAllMyDatabases.log\n\n::pause\n::================ BackUpAllMyDatabases.cmd ============= END\n\n\n--=================================================BackUpAllMyDatabases.sql start\nDECLARE @DBName varchar(255)\n\nDECLARE @DATABASES_Fetch int\n\nDECLARE DATABASES_CURSOR CURSOR FOR\n    select\n        DATABASE_NAME   = db_name(s_mf.database_id)\n    from\n        sys.master_files s_mf\n    where\n       -- ONLINE\n        s_mf.state = 0 \n\n       -- Only look at databases to which we have access\n    and has_dbaccess(db_name(s_mf.database_id)) = 1 \n\n        -- Not master, tempdb or model\n    --and db_name(s_mf.database_id) not in (\'Master\',\'tempdb\',\'model\')\n    group by s_mf.database_id\n    order by 1\n\nOPEN DATABASES_CURSOR\n\nFETCH NEXT FROM DATABASES_CURSOR INTO @DBName\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    declare @DBFileName varchar(256)    \n    set @DBFileName = @DbName + \'_\' + replace(convert(varchar, getdate(), 112), \'-\', \'.\') + \'.bak\'\n--REMEMBER TO PUT HERE THE TRAILING \\ FOR THE DIRECTORY !!!\n    exec (\'BACKUP DATABASE [\' + @DBName + \'] TO  DISK = N\'\'D:\\DATA\\BACKUPS\\\' + \n        @DBFileName + \'\'\' WITH NOFORMAT, INIT,  NAME = N\'\'\' + \n        @DBName + \'-Full Database Backup\'\', SKIP, NOREWIND, NOUNLOAD,  STATS = 100\')\n\n    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName\nEND\n\nCLOSE DATABASES_CURSOR\nDEALLOCATE DATABASES_CURSOR\n\n--BackUpAllMyDatabases==========================end\n\n--======================RestoreDbFromFile.sql start\n-- Restore database from file\n-----------------------------------------------------------------\nuse master\ngo\n\ndeclare @backupFileName varchar(100), @restoreDirectory varchar(100),\n@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),\n@databaseDataFile varchar(100), @databaseLogFile varchar(100),\n@databaseName varchar(100), @execSql nvarchar(1000)\n\n-- Set the name of the database to restore\nset @databaseName = \'ReplaceDataBaseNameHere\'\n-- Set the path to the directory containing the database backup\nset @restoreDirectory = \'ReplaceRestoreDirectoryHere\' -- such as \'c:\\temp\\\'\n\n-- Create the backup file name based on the restore directory, the database name and today\'s date\n\n@backupFileName = @restoreDirectory + @databaseName + \'-\' + replace(convert(varchar, getdate(), 110), \'-\', \'.\') + \'.bak\'\n\n\n-- set @backupFileName = \'D:\\DATA\\BACKUPS\\server.poc_test_fbu_20081016.bak\'\n\n-- Get the data file and its path\nselect @databaseDataFile = rtrim([Name]),\n@databaseDataFilename = rtrim([Filename])\nfrom master.dbo.sysaltfiles as files\ninner join\nmaster.dbo.sysfilegroups as groups\non\n\nfiles.groupID = groups.groupID\nwhere DBID = (\nselect dbid\nfrom master.dbo.sysdatabases\nwhere [Name] = @databaseName\n)\n\n-- Get the log file and its path\nselect @databaseLogFile = rtrim([Name]),\n@databaseLogFilename = rtrim([Filename])\nfrom master.dbo.sysaltfiles as files\nwhere DBID = (\nselect dbid\nfrom master.dbo.sysdatabases\nwhere [Name] = @databaseName\n)\nand\ngroupID = 0\n\nprint \'Killing active connections to the "\' + @databaseName + \'" database\'\n\n-- Create the sql to kill the active database connections\nset @execSql = \'\'\nselect @execSql = @execSql + \'kill \' + convert(char(10), spid) + \' \'\nfrom master.dbo.sysprocesses\nwhere db_name(dbid) = @databaseName\nand\nDBID &lt;&gt; 0\nand\nspid &lt;&gt; @@spid\nexec (@execSql)\n\nprint \'Restoring "\' + @databaseName + \'" database from "\' + @backupFileName + \'" with \'\nprint \' data file "\' + @databaseDataFile + \'" located at "\' + @databaseDataFilename + \'"\'\nprint \' log file "\' + @databaseLogFile + \'" located at "\' + @databaseLogFilename + \'"\'\n\nset @execSql = \'\nrestore database [\' + @databaseName + \']\nfrom disk = \'\'\' + @backupFileName + \'\'\'\nwith\nfile = 1,\nmove \'\'\' + @databaseDataFile + \'\'\' to \' + \'\'\'\' + @databaseDataFilename + \'\'\',\nmove \'\'\' + @databaseLogFile + \'\'\' to \' + \'\'\'\' + @databaseLogFilename + \'\'\',\nnorewind,\nnounload,\nreplace\'\n\nexec sp_executesql @execSql\n\nexec(\'use \' + @databaseName)\ngo\n\n-- If needed, restore the database user associated with the database\n/*\nexec sp_revokedbaccess \'myDBUser\'\ngo\n\nexec sp_grantdbaccess \'myDBUser\', \'myDBUser\'\ngo\n\nexec sp_addrolemember \'db_owner\', \'myDBUser\'\ngo\n\nuse master\ngo\n*/\n--======================RestoreDbFromFile.sql\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/1600', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/230/']