SQL copy only back and restore

If you would like to create an ad-hoc backup without disrupting your current backup jobs you will have to use the new copy only option for SQL 2005. This is command line only operation that you can’t do within the SQL gui window.

We will assume the following:

  • old database name: DataBase1
  • old database logical name: Database1
  • old database transaction log name: Database1_log
  • new database name: DataBase2

To create the back up open a query windows and excute a statement as follows:

backup database DataBase1 to disk=’c:\database1.bak’ with copy_only

To restore it do the following in the query window:

restore database [DataBase2]
from disk = N’c:\Database2.bak’
with file = 1
,move N’Database1′
to N’c:\Database1.mdf’
,move N’Database1_log’
to N’C:\Database1_log.ldf’
, nounload, stats = 10

This is place a new database called DataBase2 with the MDF and LDF in the c: directory. You can of course place it wherever you desire but I shortened this for clarification.

Tags :

About the Author

Leave a Reply

%d bloggers like this: