Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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 '13 at 15:08

1 Answer 1

up vote 2 down vote accepted

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'; 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.