Cloning a SQL Server Database

Yesterday, I wrote about Ola Hallengren's SQL Server scripts. They're an awesome resource, especially for Access developers. Here are just a few reasons to use these scripts:

They're bulletproof.
They're powerful.
They're easy to install.
They're easy to use.
They're well-documented.
They have lots of options but very few required parameters.
They run on all versions of SQL Server, including Express.
They're free!

Why bother?

Most of my clients are medium-size businesses without a full-time database administrator. I've often found myself fulfilling that role by default for these types of organizations. I know just enough to be dangerous with SQL Server, so it's nice to be able to defer to an expert for things like sensible defaults when setting up backups and performing ongoing maintenance.

"Doesn't SQL Server Management Studio have wizards for these operations?" you ask. It does, but I don't like using wizards when I'm developing. Wizards make documentation and repeatability hard. And they make automation impossible.

I would much rather spend the time up-front working through a text-based script that I can commit to version control. Then, I can begin the iterative process of tweaking the script, committing the changes to version control, and retesting the script.

Cloning a production database for debugging

I should start by warning you that not all production environments are the same. I know my environment, but I don't know yours. That said, assuming you have the proper database rights, the following two scripts will allow you to quickly clone a database for debugging purposes.

For example, say you are trying to recover from an error in an application and some of the live data is wrong. You may want to test a few different approaches to how you might go about fixing the data before you start messing with the production database. Depending on the environment, you may not be able to use the latest backup. Heck, there might not even be a backup. In cases like those, it's nice to be able to "clone" the live production database.

Visual Studio apparently has a tool for this. I tried using it, but I found it overly complicated for what I was trying to do. I was getting errors copying the schema over before I even started copying data. I never even bothered troubleshooting the error because I thought there had to be a simpler approach.

Immediate backup and restore

I realized that all I really wanted to do was make a backup of the live database and restore it to a different database name. Then it was a simple* matter of relinking the tables in the front-end of my Access application to the new "debug" database.

* I say simple because I have a form that manages this relinking. Depending on your setup, relinking the tables in your front-end Access database may be the most complicated part of this process!

Here are the commands I used. Note that the backup command is using the Ola Hallengren scripts referenced earlier. You must install at least Ola Hallengren's DatabaseBackup script to run the commands below.

Backup

Step 1 is to back up the database. Pay special attention to the @Directory value. The drive letter is relative to the SQL Server device; this is often different than the device that is running SQL Server Management Studio (SSMS). Also, the SQL Server service user must have read/write privileges to the @Directory.

The code below, when executed, will create a file named G:\SqlDumps\Accounting_Debug.bak. The @CopyOnly parameter is important because it tells SQL Server to ignore this particular backup for scheduling purposes. From the Microsoft docs:

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

EXECUTE master.dbo.DatabaseBackup
  @Databases = 'Accounting'  -- <-- Database Name (replace this value)
, @Directory = 'G:\SqlDumps' -- <-- Backup Folder (replace this value)
, @BackupType = 'FULL'
, @Verify = 'N'
, @Compress = 'N'
, @CheckSum = 'N'
, @CopyOnly = 'Y'
, @DirectoryStructure = Null
, @FileName = '{DatabaseName}_Debug.{FileExtension}'

Restore

The other half of the operation is the restore of the database. The key here is that we are restoring it to a database with a different name (I've appended _Debug to the database name to distinguish from the live data).

The command below corresponds with the Backup command from above. You should be able to just run a find on "Accounting" and replace it with the name of your database. Likewise for "G:\SqlDumps".

That said, you should never blindly run any code you get off the internet without understanding what it's doing. The good news is that these two commands are pretty straightforward, so they should be easy to figure out.

WARNING: The REPLACE flag in the T-SQL below will automatically replace the database named in the RESTORE DATABASE line. I recommend keeping the "_Debug" suffix so that there is no chance of accidentally overwriting the live, production database.

USE [master]
RESTORE DATABASE [Accounting_Debug]
  FROM  DISK = N'G:\SqlDumps\Accounting_Debug.bak'
  WITH  FILE = 1
  ,  MOVE N'Accounting' TO N'G:\SqlDumps\Accounting_Debug.mdf'
  ,  MOVE N'Accounting_log' TO N'G:\SqlDumps\Accounting_Debug_log.ldf'
  ,  NOUNLOAD
  ,  REPLACE
  ,  STATS = 5

GO

Final thoughts

If you find yourself cloning databases regularly, you may want to package the two commands above into a standalone Stored Procedure. I'll leave that as an exercise for the reader.

In terms of performance, the Backup script ran for about 6 seconds on a ~400MB database in my production environment. The Restore command for that same database took about 31 seconds. That's less than one minute for a full clone of a 400MB database. That seems pretty good to me.

Image courtesy of ImgFlip.com memegenerator