Tip

SQL Server backup and restore commands to limit downtime

Whatever you decide on for a SQL Server backup and restore solution, ensure that you can back up and restore your data in an acceptable amount of time. The last thing you want to do is tell your customers, "We are in the middle of a two-terabyte database restore. We will be online again in 16 hours."

Database administrators are responsible for protecting a company's most precious asset – its data. DBAs are not only responsible for providing efficient access to the data, but also for returning access to the data as quickly as possible in the event of a failure, with no or minimal data loss.

Most companies can tolerate very little downtime, but when it comes to data loss, the needs of a company vary. For example, most financial institutions can not tolerate data loss, while other companies can, such as a media institution. The media needs to be highly available in the event of a disaster but can tolerate data loss because the public is focused on news of that disaster, not on historical events. Disaster stories will come into their system continually.

Companies with low downtime requirements need to focus on high availability solutions, such as clustering, database mirroring, hardware mirroring and so on. Most other companies rely on the native SQL Server backup and restore commands to provide their data protection requirements.

In this article, we'll look at backup types and focus on using their commands in an efficient manner.

    Requires Free Membership to View

SQL Server backup overview

In SQL Server, you can back up a database or the transaction log. A backup creates an image of your data that you can restore to the source database or another database. There are three types of database backups: full, differential and copy-only.

  • A full backup is a copy of your backup that contains the state of the database at the point the database backup started. It includes the changes that occurred since that point, until the point where the data portion of the backup completed.
  • A differential backup contains all changes that occurred since the last complete backup. So, a differential backup done on Monday will contain all the data that has changed in the database since the last full backup was done on Sunday. A differential backup done on Tuesday will contain all data that has changed in the database since Sunday.
  • A copy-only backup is one that will not interrupt differential backups or log shipping chains.

Database backups

A database backup includes the state of the database as of the time you started the backup and any changes that occurred while you were backing up the data portion of the backup.

You can back up a complete database or one or more filegroups or files of the database. You can do a complete backup (called a full backup), a differential backup (all changes that occurred in the database, filegroup or file since the last full backup) or a copy-only backup (SQL 2005 only).

You can back up your database file or filegroup to a file, a tape device, a backup device, or the null device. The null device basically allows for backing up your database to nothing. This is useful for debugging, performance testing -- in the event you need to trick your database into thinking a backup has been done -- so you can dump the transaction log. Despite higher throughput speeds, backing up SQL Server to tape is increasingly falling out of popularity for a variety of issues.

Here are some examples:

BACKUP DATABASE AdventureWorks TO DISK='test.bak'

Here we are backing up the database AdventureWorks to a database backup file called test.bak. You can use any name or extension permitted for filenames. Each time we issue the following command, the new backup is appended to the existing backup. The backup file test.bak is a container for all backup being written to this file.

To see this issue the following commands:

RESTORE FILELISTONLY FROM DISK='test.bak' WITH
FILE=1

GO

RESTORE FILELISTONLY FROM DISK='test.bak' WITH
FILE=2

GO

You will see the database files contained in each of the backups. The WITH FILE parameter lets you view selected backups contained within your backup file.

To overwrite the backup file each time with your new backup, you need to use the following parameter:

BACKUP DATABASE AdventureWorks TO DISK='test.bak'
WITH INIT

The backup files will be default stored in:

C:\Program Files\Microsoft SQL
Server\MSSQL.X\MSSQL\BACKUP

In SQL Server 2005 where X is the number of your instance, and in:

C:\Program Files\Microsoft SQL
Server\MSSQL$InstanceName\ BACKUP

In SQL Server 2000:

To back up to a tape device you would use the following command:

BACKUP DATABASE AdventureWorks TO TAPE =
'\\.\tape0'

Backup devices

Backup devices are a name you give to one or more files or tape devices that you wish to back up to. You can use them to alias the name of a tape unit or backup file to simplify your backup statements. For instance, instead of:

BACKUP DATABASE AdventureWorks TO DISK='C:\
test.bak'

You can create a backup device and back up to it as illustrated:

sp_adddumpdevice 'DISK','DeviceName','C:\Program
Files\Microsoft SQL Server\MSSQL\Backup'

and then back up to it like this:

BACKUP DATABASE AdventureWorks to DeviceName

 

You can also create backup devices which map to network shares:

sp_addumpdevice 'disk', 'NetworkDevice',
'\\servername\sharename\Test.bak'

Or a tape drive:

sp_addumpdevice 'tape', 'TapeDevice','\\.\tape0'

The real power of using backup devices is that you can back up to multiple tape devices at one time and get much better backup and restore performance, i.e.:

BACKUP DATABASE AdventureWorks to Devicename1,
devicename3

WITH FORMAT

You can monitor the performance of backup devices using perfmon by using the counter SQLServer:BackupDevice:

Differential backups

As mentioned previously, a differential backup contains changes to the database since the last full backup occurred. These are helpful because differential backups take less time and less space than full backups. They take longer to restore from however. Here is the command to do a differential backup:

BACKUP DATABASE AdventureWorks TO DISK='test.bak'
WITH DIFFERENTIAL

File and filegroup backups

You can get better database performance by placing heavily updated objects on their own files or filegroups. Doing so will reduce I/O contention. It is also possible to place tables or objects that are read only on their own files and filegroups. Then back up these files and filegroups apart from the rest of the database.

Here is how you would backup a file:

BACKUP DATABASE [AdventureWorks] FILEGROUP =
'PRIMARY' TO DISK = 'C:\ AWFG.bak'

To illustrate how you would backup an individual file let's first add another database file to the primary filegroup.

Once we have the additional file in our filegroup in place we can now back it up.

BACKUP DATABASE [AdventureWorks] FILE = 'AW2' TO
DISK = 'C:\ AW2.bak'

This, of course, begs the question: Why would anyone want to back up

More on SQL Server backup and recovery:

a file or filegroup separately from the rest of the database? In a very large database, you may be able to place read-only tables on a separate filegroup and then only back them up once a month or on a schedule that fits your recovery requirements. You will need to back up the other files and filegroups that comprise your database, and in SQL Server 2000 keep the entire log backup chain since you did the file/filegroup backup. In SQL Server 2005, Microsoft relaxed this requirement and you only need to restore the tail of the log and any other logs to bring your database to the point in time recovery requirements for your SLA.

Transaction log backups

The transaction log contains a record of all open and committed transactions in your database, since the last transaction log backup (also called a dump). It is essential to back up your transaction log to maintain its size and to be able to do point in time recovery.

Here is an example of backing up your transaction log:

BACKUP LOG ADVENTUREWORKS TO
DISK='c:\logdump.bak' WITH INIT

You can only back up your transaction log if you have already performed a database backup and your database is in full or bulk logged recovery mode. Most DBAs use the database maintenance wizard to back up their transaction logs because this creates transaction log dumps with a naming convention including a date stamp. For example:

DatabaseName_backup_20071010.trn

Verifying your backups

Every good backup and recovery plan includes running checkdb before you back up your database and then verifying your database and transaction log backups were valid. The only complete test of this is to do test restores on a regular basis. You can verify your backups using the restore command with the verifyonly parameter. Here is an example:

Here is an example:

BACKUP DATABASE AdventureWorks TO
DISK='c:\temp.bak' WITH INIT

 

RESTORE VERIFYONLY FROM DISK =
'c:\temp.bak'

SQL Server 2005 DBCC and verify commands virtually guarantee that your restores will be successful.

Backing up the tail of the log

SQL Server allows you to back up the tail of the log. This is essentially the last committed transactions in your transaction log since the last log backup – in short, it means getting your transaction log backup good to the last drop.

Often, you encounter this feature in SQL Server 2005 when you go to restore a database backup into the same database. Here is an example:

CREATE DATABASE BackupTail

GO

BACKUP DATABASE BackupTail TO
DISK='c:\tailbackup.bak' WITH INIT

GO

 

RESTORE DATABASE BackupTail FROM
DISK='c:\tailbackup.bak'

GO

 

It gives the following message:

Server: Msg 3159, Level 16, State 1, Line 1

The tail of the log for the database "backuptail" has not been backed up. Use BACKUP LOG WITH NORECOVERY to back up the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Here is an example of backing up the tail of the log and then restoring it to this database.

CREATE DATABASE BackupTail

GO

 

BACKUP DATABASE BackupTail TO
DISK='c:\tailbackup.bak' WITH INIT

GO

 

BACKUP LOG BackupTail TO DISK ='c:\tailbackuplog.bak'
WITH INIT

GO

BACKUP LOG BackupTail TO DISK ='c:\tail.bak' WITH
NORECOVERY, INIT

RESTORE DATABASE BackupTail FROM
DISK='c:\tailbackup.bak' WITH NORECOVERY

GO

RESTORE LOG BackupTail FROM
DISK='c:\tailbackuplog.bak' WITH NORECOVERY

GO

RESTORE LOG BackupTail FROM DISK='c:\tail.bak'

GO

SQL Server restores

We have already seen how to restore a database and the log (and tail of the log) in the previous example. Note: When you use the norecovery parameter, you tell SQL Server to postpone complete recovery of your database so it can accept subsequent log restores. The final restore command completely recovers the database and makes it accessible to users.

Finally, we will look at a complicated example. We will create a database with a read\write filegroup and a read-only filegroup. We will back up the filegroups separately and then restore the read-only filegroup after we have restored the read-write filegroup.

Create database MultiFileGroup

GO

--adding a filegroup which will be a read/write filegroup

ALTER DATABASE MultiFileGroup ADD FILEGROUP
ReadWrite

GO

--adding a filegroup which will become a read-only filegroup

ALTER DATABASE MultiFileGroup ADD FILEGROUP
ReadOnly

GO

--adding files to our filegroup

ALTER DATABASE MultiFileGroup ADD FILE ( NAME =
'ReadOnly', FILENAME = 'ReadOnly.ndf') TO FILEGROUP
[ReadOnly]

GO

--adding files to our file group

ALTER DATABASE MultiFileGroup ADD FILE ( NAME =
'ReadWrite', FILENAME = 'ReadWrite.ndf') TO FILEGROUP
[ReadWrite]

GO

USE MultiFilegroup

GO

--creating three different tables on each filegroup

CREATE TABLE table1(pk INT NOT NULL) ON
[PRIMARY]

GO

CREATE TABLE table2(pk INT NOT NULL) ON
[READWRITE]

GO

CREATE TABLE table3(pk INT NOT NULL) ON
[ReadOnly]

GO

--inserting data into tables

DECLARE @counter INT

SET @counter=1

WHILE @counter<=10

BEGIN

INSERT INTO table1 VALUES(@counter)

INSERT INTO table2 VALUES(@counter)

INSERT INTO table3 VALUES(@counter)

SELECT @counter=@counter+1

END

GO

DECLARE @counter INT

SET @counter=1

WHILE @counter<=10

BEGIN

INSERT INTO table1 VALUES(@counter)

INSERT INTO table2 VALUES(@counter)

INSERT INTO table3 VALUES(@counter)

SELECT @counter=@counter+1

END

GO

--backing up the database

BACKUP DATABASE MultiFileGroup to disk='c:\MultiFileGroupFull1.bak' WITH INIT

GO

--setting the read-only filegroup to be read-only

ALTER DATABASE MultiFileGroup set single_user WITH
ROLLBACK IMMEDIATE

GO

ALTER DATABASE MultiFileGroup MODIFY
FILEGROUP [ReadOnly] READONLY

GO

ALTER DATABASE MultiFileGroup SET MULTI_USER

GO

--backing up the now read-only filegroup

BACKUP DATABASE MultiFileGroup FILEGROUP ='readonly' TO
DISK='c:\readonlybackup.bak'

GO

DECLARE @counter INT

SET @counter=1

WHILE @counter<=10

BEGIN

INSERT INTO table1 VALUES(@counter)

INSERT INTO table2 VALUES(@counter)

SELECT @counter=@counter+1

END

GO

--backing up the primary and read/write file groups

BACKUP DATABASE MultiFileGroup filegroup ='readWrite'
TO DISK='c:\readWrite.bak'

GO

BACKUP DATABASE MultiFileGroup filegroup ='Primary'
to disk='c:\Primary.bak'

GO

--backing up the log

BACKUP LOG MultiFileGroup TO DISK='c:\tail.bak' WITH
INIT, NORECOVERY

BACKUP DATABASE MultiFileGroup FILEGROUP
='readonly' TO DISK='c:\readonlybackup.bak'

GO

DECLARE @counter INT

SET @counter=1

WHILE @counter<=10

BEGIN

INSERT INTO table1 VALUES(@counter)

INSERT INTO table2 VALUES(@counter)

SELECT @counter=@counter+1

GO

USE master

RESTORE DATABASE multifilegroup FILEGROUP
='Primary' FROM DISK='c:\Primary.bak'

RESTORE DATABASE multifilegroup FILEGROUP
='ReadWrite' FROM DISK='c:\ReadWrite.bak'

--result

Processed 16 pages for database 'multifilegroup', file
'ReadWrite' on file 1.

Processed 2 pages for database 'multifilegroup', file
'MultiFileGroup_log' on file 1.

The roll forward start point is now at log sequence number (LSN) 22000000045700001. Additional roll forward past LSN 22000000047900001 is required to complete the restore sequence.

This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

RESTORE DATABASE ... FILE= successfully processed 18 pages in 0.294 seconds (0.477 MB/sec).

RESTORE DATABASE multifilegroup FILEGROUP
='ReadOnly' FROM DISK='c:\readonlybackup.bak'

--result

Processed 16 pages for database 'multifilegroup', file 'ReadOnly' on file 1.

The roll forward start point is now at LSN 22000000045700001. Additional roll forward past LSN 22000000047900001 is required to complete the restore sequence.

This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

RESTORE LOG multifilegroup FROM DISK='c:\tail.bak'

GO

SELECT * FROM multifilegroup.dbo.table1

As you can see your database is accessible.

In this article we have examined some of the more common backup and restore commands you will encounter as a DBA. When you create a backup and recovery plan, make sure you'll be able to restore your databases in a timely manner. This may involve backing up to a backup device that contains many files or tape devices. You may also have to resort to differential backups, third-party backup compression software. If your data is such that you can create read-only file groups, then you can park static data on these file groups. You can then back them up on a different schedule than the file groups containing more volatile data, and, hence, reduce your backup times.

ABOUT THE AUTHOR
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books devoted to merge replication and Microsoft search technologies. Hilary Cotter can be contacted at hilary.cotter@gmail.com.

This was first published in November 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.