Making a database copy in SQL Server

There are many ways of doing this, mine is just one. I find this a quick method when you want an exact copy, and the database can be taken offline for a few moments.

• First, mark the source db as offline in the management console.
• Then, in windows explorer, make a copy of the data and log file.
• Put the source db back online.
• Rename the copies to your desired new database name.
• Link them to a new db instance.

EXEC  sp_attach_db @dbname = 'newdb', @filename1 = 'c:\Program  Files\Microsoft SQL Server\MSSQL\Data\newdb_data.mdf', @filename2 =  N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb_log.ldf'

• However the logical filename will still be the old database (revealed by sp_helpfile). So to rename the logical filename…

USE MASTER
GO

ALTER  DATABASE newdb
MODIFY FILE (NAME = olddb_data,  NEWNAME='newdb_data')
GO

ALTER DATABASE newdb
MODIFY FILE (NAME = olddb_log,  NEWNAME='newdb_log')
GO

And hey presto, your duplicate is ready to go!