x

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

Henno gravatar image

Henno
21 1 1 1

(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 FILELISTONLY 
   FROM AdventureWorksBackups ;

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

http://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx

HTH, Stu

more ▼

answered Oct 23, 2009 at 07:42 AM

Stuart Ainsworth gravatar image

Stuart Ainsworth
86

(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

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

The MSSQL server is being backed up by running this command every night (via Windows Scheduled Tasks): expressmaint -S (local) -D ALL_USER -T DB -R c:\reports -RU WEEKS -RV 1 -B D:\SQL -BU DAYS -BV 7 -V > D:\sql\log.log

Would it affect this backup strategy?
Oct 23, 2009 at 05:08 PM Henno
Henno: do you know what recovery mode the databases are in?
Oct 26, 2009 at 02:29 PM Kev Riley ♦♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1951
x346
x241

asked: Oct 23, 2009 at 06:51 AM

Seen: 9436 times

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