SQL Server – How to Copy an Existing Database

azure-sql-databasesql server

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?

Best Answer

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'
Related Question