How to Script Copying a database in SQL Server

This always seems to be an issue that crops up. Copying a database sounds like something that should be simple to do, in practice there are a number of steps, and varying methods to do so. I had a couple co-workers who wanted a nightly refresh of test data, they were manually copying the database which was taking quite a bit of time so I put this together for them.

I’ve broken the copy into two parts since depending on your needs you many find each useful on their own.

Part 1) How to script a backup of a MS SQL Server Database:
There’s lots of examples on how to do this on the internet, I’ve used variables and separated mine into exec statements since many of the backup and restore commands don’t accept variables as input (annoying!). Here’s the script:

--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
use master
go
declare @dbToCopy varchar(1000), @sql varchar(1000), @dbLogsDir varchar(5000), @dbDatabaseDir varchar(5000)
--You will need to point this at your log file directory
set @dbLogsDir = 'c:\temp\'
--You will need to point this at your DB file directory
set @dbDatabaseDir = 'c:\temp\'
set @dbToCopy = 'dbToCopy'
set @sql = 'DBCC SHRINKDATABASE (N''' + @dbToCopy + ''', 0,TRUNCATEONLY)'
exec(@sql)
set @sql = '''' + @dbLogsDir + @dbToCopy + '_LogBAK.LDF'''
set @sql = 'backup log ' + @dbToCopy + ' to disk = ' + @sql
exec(@sql)
set @sql = '''' + @dbDatabaseDir + @dbToCopy + '_Data.BAK'''
set @sql = 'backup database ' + @dbToCopy + ' to disk = ' + @sql
exec(@sql)

If you run that you will have your backup log file and db. To attach it as a different DB name use this:

--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
use master
go
declare @dbToCreate varchar(1000), @filePrefix varchar(1000), @sql varchar(1000), @dbLogsDir varchar(5000), @dbDatabaseDir varchar(5000)
set @dbLogsDir = 'c:\temp\'
set @dbDatabaseDir = 'c:\temp\'
set @filePrefix = 'dbToCopy'
--Assuming your DB's logicalName is 'DATABASE_Data' and your log's name is
--'DATABASE_Log' @filePrefix will need to be 'DATABASE'.  This script doesn't
--support logical names that vary more than this (you would have to extend
--it to get that)
set @dbToCreate = 'dbToCreate'
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
  set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
--needed to kick users out of our to be created db and keep them out.
--(backup restore can create un restore-able files if you don't do this)
  exec(@sql)
end
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
  set @sql = 'drop database ' + @dbToCreate
  exec(@sql)
end
set @sql = 'restore database ' + @dbToCreate + ' from disk = ''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
set @sql = @sql + ' with replace, move ''' + @filePrefix + '_Data'' to ''' + @dbDatabaseDir + @dbToCreate + '_Data.MDF'',move ''' + @filePrefix + '_Log'' to ''' + @dbLogsDir + @dbToCreate + '_Log.LDF'''
exec(@sql)
set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET multi_user'
exec(@sql)

-- These may fail on your server as xp_cmdshell is probably disabled.
--For security reasons it is probably best left disabled,
--but if you do want it on (and have admin privilege) this is how you do it.
set @sql = 'xp_cmdshell ''del ' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF'''
--this will delete the log file back up from the earlier step
exec(@sql)
set @sql = 'xp_cmdshell ''del ' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
--this will delete the db file back up from the earlier step
exec(@sql)

I decided to take all of this and create an easily run stored procedure from it, this is the stored procedure:

create procedure spCopyDatabaseEx(@dbToCopy varchar(1000), @dbToCreate varchar(1000), @filePrefix varchar(1000), @dbLogsDir varchar(1000), @dbDatabaseDir varchar(1000)) as
--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
declare @sql varchar(1000)
set @sql = 'DBCC SHRINKDATABASE (N''' + @dbToCopy + ''', 0,TRUNCATEONLY)'
exec(@sql)
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
	set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
	exec(@sql)
end
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
	set @sql = 'drop database ' + @dbToCreate
	exec(@sql)
end
set @sql = '''' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF'''
set @sql = 'backup log ' + @dbToCopy + ' to disk = ' + @sql 
exec(@sql)
set @sql = '''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
set @sql = 'backup database ' + @dbToCopy + ' to disk = ' + @sql 
exec(@sql)

set @sql = 'restore database ' + @dbToCreate + ' from disk = ''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
set @sql = @sql + ' with replace, move ''' + @filePrefix + '_Data'' to ''' + @dbDatabaseDir + @dbToCreate + '_Data.MDF'',move ''' + @filePrefix + '_Log'' to ''' + @dbLogsDir + @dbToCreate + '_Log.LDF'''
exec(@sql)
set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET multi_user'
exec(@sql)

set @sql = 'xp_cmdshell ''del ' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF'''
exec(@sql)
set @sql = 'xp_cmdshell ''del ' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
exec(@sql)
go

To try it run this:

exec spCopyDatabaseEx 'northwind', 'northwindNew', 'Northwind', 'e:\mssql\logs\', 'e:\mssql\data\'

Best of luck! As always feel free to ask questions / make suggestions. I’m happy to answer your questions.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: