I'm one of those souls who started as a developer and got forced into DBA roles, but I continue to do active development. In support of that I get asked to go get copies of client databases and restore them to our test servers. This way we can test changes and develop deployment procedures.

I ran into problems with an issue with taking backups ad-hoc and fouling up maintenance plans and causing unrestrained log growth. Then I read about COPY_ONLY backups and thought that they would be a life saver. They are. Then I ran into challenges actually dealing with them. I looked around for a beginners guide to COPY_ONLY. Not finding exactly what I wanted I wrote a document for the rest of our team. I thought that this would be valuable to others in the community.

Backups and restores are tied into what is called the "recovery model" in SQL Server. A full discussion of recovery models is well beyond the scope of this document, but you can read more in Books Online.

It is fairly typical for most production databases to run in the "Full" recovery model setting. This provides the best operation in case of some type of outage where SQL Server must do some emergency restart. Power outages come to mind. SQL Server does a good job of cleaning itself up when this happens. Often your data is intact. This, however, has an impact on backups. Rather it's the other way around.

A full backup that is taken in full recovery starts what is called a log backup chain. What happens is that the full backup is the start of the chain and each log backup is tiny, by comparison, and fairly quick. To restore the database to some point in time one starts by restoring the last full backup and then the log backups in order, one by one, until the desired point in time is reached. To get the database right up to current you restore the last full backup and all the log backups.

If no full backups have been taken since the database entered full recovery then there is no internal chain. The log portion of the database can be fairly lean and growth is limited. Once a full backup is taken then the log backup chain starts. The log portion can't clean itself up until a log backup is taken. The log just grows and grows until it consumes all available disk space. (For SQL Server Express it might be that the maximum file size is reached with space still available on the drive.)

If we come along and grab a full backup and the client is using a third party backup solution or has a good maintenance plan we now have triggered something that will just come back to bite us soon. The reactionary solution is to set databases to "Simple" recovery and accept the risk of database corruption. Now there is a new option that helps solve the problems.

SQL Server 2005 introduced the COPY_ONLY backup option but did not include it in the backup dialog in SSMS. It is included as a check box in SQL 2008 SSMS. So to use it in 2005 you have to write a SQL statement that does the backup. SSMS is just a pretty GUI that writes the SQL statement for you anyway.

It would seem that it would be pretty easy to add this one option to a SQL statement generated by SSMS. There is a catch. Unfortunately the restore dialog in the 2005 SSMS does not understand the backups made with COPY_ONLY. This means that if you are going to script the backup you must script the restore.

The Backup

Let's look at one of these backup scripts.

BACKUP DATABASE [PlayTestEtc]
 TO DISK = N'E:\SQL\Backup\Play.bak'
 WITH
 INIT,
 NAME = N'PlayTestEtc-Full Database Backup',
 DESCRIPTION = N'Description of backup',
 COPY_ONLY,
 STATS = 1
GO

We will look at each parameter in turn.

BACKUP DATABASE

This is what we are going to do. And that is followed by the name of the database. The square brackets are optional.

TO DISK

Some people are still using special tape drives for backups but we are going to write our backup to the disk drive. The N following the equal sign says that this is a Unicode constant. The full path to the file is given.

WITH

Here we start telling what options we want.

INIT

We don't want more than one backup to be in one file. SQL Server will do that. It just keeps appending more backups onto the end of your file.

NAME

Can be up to 128 characters and identifies this backup.

DESCRIPTION

This can be up to 255 characters. Like other documentation options in other places the NAME and DESCRIPTION help you keep things straight.

COPY_ONLY

The essential parameter for this whole discussion

STATS

Specifies the percentage after which SQL Server spits out a progress message. The acceptable range is 1 to 100.

The Restore

Let's look at a restore the same way.

RESTORE DATABASE [PlayTestEtc]
 FROM DISK = N'E:\SQL\Backup\Play.bak'
 WITH FILE = 1,
 MOVE N'PlayTestEtc' TO N'E:\SQL\2005\PlayTestEtc.mdf',
 MOVE N'PlayTestEtc_log' TO N' E:\SQL\2005\PlayTestEtc_log.ldf',
 REPLACE, 
 STATS = 1
GO

RESTORE DATABASE

Yes we are restoring a database and the name of it is in the square brackets.

FROM DISK

The complete path and name of the file being used by the restore.

WITH

The options for the restore start here.

FILE = 1

We want the first backup within the file. Remember that it's possible to put multiple backups in the same physical file.

MOVE x TO y

The backup file contains information about "logical files" and "physical files". It is often true that the organization of file structures will be different between the client production server and our local test machine. The MOVE option allows you to deal with this. Use this option to map the logical file in the backup to its physical location on our test machine. It's important that you do this otherwise it's possible to corrupt our local server.

REPLACE

Allows you to overwrite an existing database on the server. Often we will change the name of the database on the local server. You can get a "safety check" which will cause the restore to fail if the following conditions are true

From BOL:

• The database named in the RESTORE statement already exists on the current server, and
• The database name is different from the database name recorded in the backup set

So now you can see where this option is essential if you are changing the name and optional if you are not.

STATS

This controls the reporting of progress just the same as was done during the backup.

How do you know what the logical files are?

That is an excellent question. You could have made note of the logical files when you did the backup. That presumes that you were the one that made the backup in the first place.Have you ever had your boss hand you a backup file and say, "Restore this quick. The client is showing up in 15 minutes." I could tell you to consult the database documentation but you would stop reading because you were laughing too hard. Instead you should ask the backup file itself.

RESTORE FILELISTONLY 
 FROM DISK = N'E:\SQL\Backup\Play.bak'

Yields something that looks like this:

Note that the physical names shown are the ones from the server on which the backup was made.

Where are my current physical files?

That can be determined by running the following query:

USE [PlayTestEtc]
SELECT [name], [physical_name] 
 FROM sys.database_files

And you would see this:

Conclusion

The scripts shown are examples. This discussion has been kept short to illustrate the point and get you started down the right road. Both the BACKUP and RESTORE commands have many parameters not shown here.

Some databases may have more than just the two segments. For example many DBA's like to have the tables, views, and stored procedures in the PRIMARY file group and another file group (typically on another hard drive) for the indexes. Further you can have multiple physical files that make up the log segments.

This is not meant to be a full discussion of backup and restore. Rather this is how to deal with a day-to-day situation that can frustrate the heck out of you the first time you come across it.