Sql-server – how to copy sql server database on same server

copydatabasesql server

I've got a SQL Server 2008 and want to make a copy of a database so I've got a 2nd Version of the database for testing on the same server.

The database copy wizard is not able to copy the database, it always sends funny error Messages about missing objects (using SMO copy).

When I try to make a backup and restore it under a different database name it still keeps the file names of the original database and overrides this (crashing the original database).

So how do I copy a SQL database? Shutdown SQL Server, copy the physical files and attach them? Maybe a command line tool for database copy? Shouldn't there be an easy way to make a copy?

Best Answer

First you need to determine the logical names of your database files by executing sp_helpdb 'DBNAME' and look at the [Name] column. You need to identify the data and log files. Next you need to execute a full copy_only database backup. The copy only option is not strictly necessary but it prevents the log chain from being broken on the original database. Next you need to execute a database restore using the MOVE option to give the actual physical files new names so they don't conflict with your original database.

Here is an example that copies the [Scratch] database to the [Scratch_New] database. You will need to adjust the actual backup and data paths based on your SQL installation.

sp_helpdb 'Scratch'

-- From the resultset of this procedure I learned that the logical 
-- data file name is 'Scratch' and the logical log file name is 'Scratch_log'.

BACKUP DATABASE [Scratch] 
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Scratch_Full_Copy.bak' WITH COPY_ONLY 

RESTORE DATABASE [Scratch_New] 
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Scratch_Full_Copy.bak'
  WITH MOVE 'Scratch' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ScratchNew.mdf',
       MOVE 'Scratch_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ScratchNew_Log.ldf';
Related Topic