Take the 2-minute tour ×

SQL Azure has a really handy command for copying databases:

CREATE DATABASE NewDatabase AS COPY OF ExistingDatabase

Is there anything similar in SQL Server 2008 R2? I just want a command to do a simple local copy without any DTS, SQL Server Agent, etc. Ideally, I'd like to create multiple copies efficiently.

share|improve this question

1 Answer 1

Probably the best you can do is backup the database and restore it under a different name, moving the files to new locations:

backup database ExistingDatabase 
  to disk='c:\...\ExistingDatabase.bak' 
  with copyonly, format;

restore database NewDatabase
  from disk 'c:\...\ExistingDatabase.bak' 
  with move ExistingDatabase to 'NewDatabase.MDF'
  , move ExistingDatabase_log to 'NewDatabase_log.LDF';

Azure databases are not physical, are only logical and therefore something like AS COPY OF can easily be done. But non-Azure databases are real, physical, and you need to take care of all the details involved in a 'copy' operation, like file location and such.

Also SQL Azure has to allow for the AS COPY OF to overcome the lack of a model database. With SQL Server you can create a template structure in model and all subsequent CREATE DATABASE will inherit this template structure.

share|improve this answer

Your Answer


