... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)


Copying a SQL Server database

The following T-SQL sequence will make a copy of a database using the BACKUP and RESTORE commands:

backup database mydb to disk = 'c:\myfile.dat'

restore database mydbcopy from disk = 'c:\myfile.dat'
with move 'mydb_dat' to 'c:\my_dbcopy.mdf'
, move 'mydb_log' to 'c:\my_dbcopy.ldf'

The magic names referenced in the MOVE...TO clauses are logical filenames.  You can determine these by running sp_helpfiles in the source database (or querying the sysfiles table).  The following commands will do the same thing using file copies instead:

exec sp_detach_db @dbname='mydb', @skipchecks='true'

exec master..xp_cmdshell 'copy c:\mydb.mdf c:\mydbcopy.mdf'
exec master..xp_cmdshell 'copy c:\mydb_log.ldf c:\mydbcopy_log.ldf'

exec sp_attach_db @dbname='mydb', @filename1='c:\mydb.mdf', @filename2='c:\mydb_log.ldf'

exec sp_attach_db @dbname='mydbcopy', @filename1='c:\mydbcopy.mdf', @filename2='c:\mydbcopy_log.ldf'

This is slightly messier, but runs a bit faster (say, 40%).

Blog Archive