CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2008-r2
Prasanna Kumar J
Prasanna Kumar J

Reputation: 327

How to copy Database from existing to new database sql script using sql server 2008 R2?

i need sql script for copy database with data in sql server 2008 r2.I tried the below script but not working in sql server 2008.

DBCC CLONEDATABASE ('MyDatabase', 'MyDatabase_Copy')

Upvotes: 0

Views: 2138

Answers (1)

Alberto Martinez
Alberto Martinez

Reputation: 2670

If you want to make a copy of the full database to another one you can make a full backup of the existing one and restore it as another database.

To perform a backup you can do doing something like this:

BACKUP DATABASE MyDatabase TO DISK='C:\Backup directory\MyDatabase.bak'
  WITH INIT, FORMAT, SKIP

Then you restore that backup in the destination database (either overwriting an existing database or creating a new one):

RESTORE DATABASE MyDatabase_Copy FROM DISK='C:\Backup directory\MyDatabase.bak'
  WITH REPLACE

If the destination database already exists you could need to use the MOVE option in the restore step (or perform a DROP DATABASE MyDatabase_Copy before restoring).

Upvotes: 2

Related Questions