2

I can't seem to find any SQL that will clone one database in SQL Server within the same server.

Let's say I have a database called MyDB. I simply want to make a copy of MyDB to MyDb2. I thought that this would work:

BACKUP DATABASE MyDB TO MyDB2;

But I get this error when I try to execute it:

Backup device 'DbTestBack' does not exist. To view existing backup devices, use the sys.backup_devices catalog view. To create a new backup device use either sp_addumpdevice or SQL Server Management Studio.

Does anyone know what the best way to do this is? I want an exact duplicate of the original including security permissions.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leslie Hanks
  • 2,347
  • 3
  • 31
  • 42
  • 6
    (1) **BACKUP** to a file on the server, (2) **RESTORE** that `.bak` to the new `MyDB2` database, (3) you're done! – marc_s Oct 30 '13 at 10:25
  • 1
    Related: http://stackoverflow.com/q/18866321/1578713 – Appulus Oct 30 '13 at 10:26
  • Depending on what you want to do, database snapshots may work http://technet.microsoft.com/en-us/library/ms175158.aspx – ta.speot.is Oct 30 '13 at 10:26
  • I understand the backup and restore option. What I really want is a simple solution and it would seem this would be possible. If not, then I will end up going this route. – Leslie Hanks Oct 30 '13 at 10:28
  • @marc_s Do you have some sample code to do this? While your list of actions makes it seem simple, but it does not address the issue of how to deal with the new data/log file location? I am writing a script that will know nothing of the environment it is running on and I don't know SQL well enough to know how to read the file locaiton from the DB and modify it for the new DB name. – Leslie Hanks Oct 30 '13 at 10:49
  • The [SQL Server Books Online](http://technet.microsoft.com/en-us/library/ms130214.aspx) has **extensive** documentation on **all** T-SQL commands with tons of samples..... – marc_s Oct 30 '13 at 13:09
  • This runs on specific version only, please check your version is not express or with all privileages. – yuvraj Jan 04 '21 at 02:31

2 Answers2

7

A simple way is taking a back up copy of current DB and restoring it. You Can do this in single step with a simple script

backup database MyDB 
to disk='D:\MyDB.bak';


restore database MyDB2
from disk='D:\MyDB.bak'
WITH move 'MyDB_Data' to  'D:\MyDB2_Data.mdf',
move 'MyDB_log' to  'D:\MyDB2_Data.ldf';
GO

Note: I made an assumption on your current data file and log file name (MyDB_Data, MyDB_log), you need to check them and make correct

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
None
  • 5,582
  • 21
  • 85
  • 170
0

DBAtools is your friend here.

Use Copy-DbaDatabase

ie.

Copy-DbaDatabase -Source SRV1 -Destination SRV1 -Database myDB -BackupRestore -SharedPath \\<<your temporary server location such as c:\temp>>
Fandango68
  • 4,461
  • 4
  • 39
  • 74