2

I need to clone a database in a single instance of SQL Server 2012.

I have tried the Copy Database tool in SSMS, but it fails with file access error of which I've spent all day reading around to no avail. I'm running out of time, and I need to get this database cloned, ensuring that the clone is entirely independent of the original.

The source database, DB1 needs to retain much of its data, so Generate Scripts is also no good.

Also my version of SQL does not have the Schema Compare tool.

Can I do a full backup, but then restore to a different database name? I tried it on a dev machine, but some aspect such as it retaining the original logical name concern me (I might not need to be concerned - please advise!). Or are there any other ways to do this?

marked as duplicate by RolandoMySQLDBA, Paul White sql-server Jan 12 '15 at 16:54

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • That question deals with multiple instances, or the requirement for. Also experts are clearly warning against production server usage with the solution, of which this is a production machine. – EvilDr Jan 12 '15 at 15:35
  • Schema Compare tool is available in SSDT for free now. – Shawn Melton Jan 12 '15 at 15:45
  • 1
    No, he only has a single instance - like you. If this is production, you should specify that. The question linked provides the answer to your question, namely "you can backup the databases you need for Data manipulation and restore them with databaseName_test." – LowlyDBA Jan 12 '15 at 15:45
3

Yes, backup & restore is a perfectly reasonable way to copy a database. You will need to use the MOVE option to re-name the files. Here is an example using a backup of a database called DBA and restoring it to DBA_Test

RESTORE DATABASE [DBA_Test] 
FROM  DISK = N'DBA_backup_2015_01_11_001025_4030475.bak' 
WITH  FILE = 1,  
MOVE N'DBA' TO N'D:\DATA\DBA_test.mdf',  
MOVE N'DBA_log' TO N'E:\LOG\DBA_test_log.ldf',  
NOUNLOAD,  STATS = 5

Kin's concern in the link mentioned by @JohnM is valid. You will be creating an additional database on the instance that will be competing for resources (depending on it's usage) with your other databases. However if you are doing this to create another production database that you need anyway this isn't really a big deal.

I'd only be concerned if you are using it to create a test environment on a production server. In that case you need to decide if the risks of bringing down your production database while working with your test environment outweigh the costs of another server/instance. For example if you are testing and blow up your test transaction log you will still cause problems for your production database.

If you need a cheap environment remember the Express is free and if you need a full blown copy the Developer license is usually only around $50-60. Of course that license is a single person license only, not for production use, but it is equivalent in functionality to a full Enterprise license.

  • Thanks Kenneth. I assume this also won't affect the restoring state of the original database? – EvilDr Jan 12 '15 at 15:54
  • @Kenneth: It would also be good to add in your answer about copy_only backup just to inform the OP that he can take backup without affecting log chains – Shanky Jan 12 '15 at 15:58
  • 1
    There will not be any affect on the original database as long as you are careful to make the database name you are restoring to and the file names different from the original. In this case anywhere where I have DBA_test in my script. Also as @Shanky said you probably will want to take a copy only backup of your original DB to avoid messing with the log chains which could cause problems if you end up needing those backups for a DR situation. – Kenneth Fisher Jan 12 '15 at 16:01
  • Lovely, thank you. I still vote to keep this question open from the "duplicate" as it avoids people having to separate the relevant data (and a nice clean example of code) from the noise that surrounds it. – EvilDr Jan 12 '15 at 17:04

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