Copying database under a different name in MSSQL

We support MS SQL Server 2005 Express. There is one production database on it. Our customer needs a copy of that database under a different name for testing purposes. There is no Copy Database task in Express and some people suggest BACKUP/RESTORE. Can anyone provide a cookbook recipe for doing that? Should we use COPY ONLY or what else to keep in mind so that the original database stays up and happy?

more ▼

asked Oct 23, 2009 at 06:51 AM in Default

avatar image

21 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

From Books Online (the help file)

BACKUP DATABASE AdventureWorks TO AdventureWorksBackups ;


RESTORE DATABASE TestDB FROM AdventureWorksBackups WITH MOVE 'AdventureWorks_Data' TO 'C:\MySQLServer\testdb.mdf', MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\testdb.ldf'; GO


HTH, Stu

more ▼

answered Oct 23, 2009 at 07:42 AM

avatar image

Stuart Ainsworth
86 1 2 1

(comments are locked)
10|1200 characters needed characters left

You are right to be considering COPY ONLY if the production database is set to Full Recovery mode and log backups are occuring frequently.

As MSDN/BOL states

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005.

The syntax for this is

BACKUP DATABASE database_name TO <backup_device>  WITH COPY_ONLY

If taking a backup isn't going to affect the backup strategy in place, then you can follow the steps that Stuart Ainsworth gave.

more ▼

answered Oct 23, 2009 at 12:02 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 23, 2009 at 06:51 AM

Seen: 15875 times

Last Updated: Oct 23, 2009 at 06:51 AM

Copyright 2018 Redgate Software. Privacy Policy