Copying a local SQL Server database (not on Azure)

Question

To back up my SQL Azure database periodically I have been using the simple tSQL statement

CREATE DATABASE FiveModes10_12_12 AS COPY OF FiveModes

But for this project (analysing some experimental results) my database is a local SQL Server 2012 one and I cannot run that statement (I get the error Incorrect syntax near 'COPY'). Looking at the documentation for the CREATE DATABASE command for SQL Server 2012 there is no AS COPY OF option. What is the simplest alternative?

Answer 1

For long lived copies take e database backup instead:

backup database FiveModes to disk = 'path to .bak file' with init, copy_only;

You can the restore this when needed:

restore database FiveMode from disk = 'path to .bak file';

You can even restore the backup as a new database, but you'll need to move the location of the MDF/LDF files during the restore:

restore database FiveMode_12_12 from disk = 'path to .bak file'
   with move 'FiveMode' to '<path to place the new copy of MDF>',
   move 'FiveMode_log' to '<path to place the new copy of LDF>'

If you need only short lived copy (eg. create a copy, do some experiment, revert to the copy) you can use database snapshots, but it requires Enterprise license. See Database Snapshots:

create database FiveModes10_12_12  on
  (Name = 'FiveMode', FileName = '<path to MDF snapshot file>')
as snapshot of FiveModes;

But be aware that snapshots are not at all like Azure copies, is very detrimental to keep them along for long time. For long time copies use backups.