2

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?

1
  • Create a folder for the database files for this copy of the database. Detach, copy, attach or backup and restore using this folder for the database files.
    – joeqwerty
    Oct 18, 2013 at 15:08

2 Answers 2

4

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'; 
0

I use SSMS to clone the DBs.

  1. Create a backup of the DB you want to copy, and call it e.g. OriginalDB
  2. Right-click on OriginalDB, select task, then Restore DB.
  3. In the option Destination for restore, put the new name of the DB (e.g. NewDB) and in the option Source for restore select OriginalDB.

This will create a NewDB which is an exact copy of OriginalDB.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .