2

I am learning how to administrate MS Sql server 2012, come cross a question and its suggested solution for copying an existing database for different name.

Administer a windows azure sql database named Orders, you need to create a copy of this table and renamed as Order_Reporting

CREATE DATABASE Order_Reporting COPY OF Orders 

I was wondering whether such useful query could also be used in standard ms sql server some how?

  • When you say you want to make a copy, do you want to copy the schema or the schema and the data? These are two different techniques. – Mike Fal Jun 8 '15 at 21:36
7

I was wondering whether such useful query could also be used in standard ms sql server some how?

No, CREATE DATABASE .. COPY OF .. is ONLY applicable to Azure SQL Database

As a side note,

Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process. You can monitor the copy process with the sys.dm_database_copies and sys.databases views. The sys.dm_operations_status view can be used as well as it returns the status of database operations including database copy.

In order to create a copy of current database in SQL Server, you have to backup the database and then restore it with a different name

e.g.

backup database OriginalDB
to disk = 'D:\backup\OriginalDB_full.bak'
with init, stats =10;

restore database new_db_name
from disk = 'D:\backup\OriginalDB_full.bak'
with stats =10, recovery,
move 'logical_Data_file' to 'D:\data\new_db_name.mdf',
move 'logical_log_file' to 'L:\log\new_db_name_log.ldf'
1

Starting with SQL Server 2014 SP2 you can use the DBCC CLONEDATABASE command to duplicate an empty database. Here is a good primer on it

Clone a SQL Server Database Using DBCC CLONEDATABASE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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