SQL Backup Strategies Part 2 How To Backup and Restore…

In a previous post I tried to detail some appropriate backup strategies.  This post builds on these concepts and provides some practical details on what to do.

Perform Full Database Backups

A full database backup is a page-level copy of the entire database to backup media. You can execute a full database backup using any recovery model (i.e Simple, Bulk-Logged or Full).

To perform a full backup use the following:

USE master;
GO
BACKUP DATABASE CompulinxDB
TO DISK = ‘D:\Backups\compulinxFULL.bak’
WITH RETAINDAYS = 7, INIT;

RETAINDAYS does not actually delete anything, it is just marking the file to tell SQL Server not to overwrite this file before the retain time is up (in the above case 7 days).

INIT This option indicates that SQL Server will overwrite any existing backups on the target media with new backups. In other words, the backup that you are taking with this statement will be
the initial backup on the media.

 

It is considered good practice to ‘stripe’ the backup to two files on separate disks (and even controllers).  So, the following syntax can be used

USE master;
GO
BACKUP DATABASE CompulinxDB

TO DISK = ‘D:\Backups\compulinxFULL.bak
DISK = ‘E:\Backups\compulinxFULL.bak
INIT;
GO

 

Differential Backup

The following can be used to make differential backups. Here the differential is appended to media containing the full backup:

USE master;
GO
BACKUP DATABASE CompulinxDB

TO DISK = ‘D:\Backups\compulinxFull.bak’
WITH DIFFERENTIAL,
RETAINDAYS = 7, NOINIT;
GO

Notice the use of the command NOINT. This option indicates that SQL Server will append this backup to any other backups on the target media. This option allows you to take multiple backups and target them to the same media set.

 

Perform Transaction Log Backups

Transaction log backups allow the DBA to manage the transaction log size while not requiring the overhead of taking frequent database backups. This is especially useful for large databases that are only moderately volatile. Before you will be able to take a valid Transaction log backup, you must do two things:

1. Make sure that the recovery model is set to Full or Bulk-Logged
2. Take a full database backup that will act as the initial point in the recovery process.

Try using the following T-SQL syntax:

USE master;
GO
BACKUP LOG CompulinxDB
TO DISK = ‘D:\Backups\compulinxTLOG.bak’
WITH INIT;
GO

Notice the use of LOG. If you wanted to take a subsequent backup of the transaction log and append it to the existing media, the statement would look like this:

USE master;
GO
BACKUP LOG CompulinxDB
TO DISK = ‘D:\Backups\compulinxTLOG.bak’
WITH NOINIT;
GO

At this point, if the database were damaged due to a corruption or loss of a data device, you would have to capture the orphaned log. You can do this with the following:

USE master;
GO
BACKUP LOG CompulinxDB
TO DISK = ‘D:\Backups\compulinxTLog.bak’
WITH NOINIT, NO_TRUNCATE, NORECOVERY;
GO

The above syntax has some new points to consider. NO_TRUNCATE will make a copy of the log but does not truncate the log. NORECOVERY allows you to capture a trailing log before making a restore. The database will be placed into a ‘restoring state’. Remember the database will not be accessible until a restore is made.

The database will look something like the following:

 

 Partial Database Backups

In part one I mentioned backing up filegroups; ‘Breaking a large database into files or filegroups for backup allows you to back up portions of it on a rotating schedule when it might be too time-consuming to back up the entire database at once’. Perhaps only a small portion of the database is changing. If this is the case we can backup a filegroup (the group of data files that is dynamic) and therefore make a partial backup. You can also make the non-volatile data read only.

USE master;
GO
BACKUP DATABASE CompulinxDB READ_WRITE_FILEGROUPS
TO DISK = ‘E:\Backups\compulinxDB_Partial.bak’
WITH INIT;
GO

Notice the use of READ_WRITE_FILEGROUPS. This causes SQL to backup only the primary filegroup and any other read/write filegroups in the collection.

 

How to Restore

OK, now we know how to set the different recovery methods for SQL, the different backup methods that use these recovery types and actually how to implement a backup. However, a backup is only as good as knowing how to restore the database.

 

How to Perform a Full Database Restore

There may be several reasons why you need to perform a full database restore. These include the following:

1. You need to restore a database to single point-in-time
2. You need to restore a database because the database is damaged
3. You need to move the database to a different server altogether

To demonstrate this we need to do the following:

1. Take a full database backup (make a baseline backup)
2. Next we have to modify the data in some way (perhaps by deleting a row?)
3. Then performing the restore so that we get our original database again.

So, to take a full database backup,

1. Make sure the database recovery model is set to Full (see the post before this one for details). I’m using the AdventureWorks DB. It’s a little big but there you go!
2. Take a full database backup using the following syntax (also shown above)

USE master;
GO
BACKUP DATABASE AdventureWorks
TO DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH RETAINDAYS = 7, INIT;

3.    Using the the following T-SQL code determine the first name of an employee with the last name of Abel

USE AdventureWorks;
GO
SELECT FirstName
FROM person.Contact
WHERE LastName = 'Abel';
GO

The answer that should be returned is Catherine. I used to go out with a Catherine…

4.   Let’s say Catherine wants to change her first name (perhaps to Irene, I won’t say it…). You can use the following to do this:

USE AdventureWorks;
GO
Update Person.Contact
SET FirstName = 'Irene'
Where LastName = 'Abel'

5.   Now make a differential backup which will record the change of Catherine to Irene. You can do this using the following (this is also shown above). This should only take 0.684 seconds (or there a bouts!)

USE master;
GO
BACKUP DATABASE AdventureWorks

TO DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH DIFFERENTIAL,
RETAINDAYS = 7, NOINIT;
GO

6.   Now we need to restore the database using the full database so the first name is Catherine once more. To do this using the interface, simply right click your database and select restore:

 

 

7.   Select Database and the following window will appear:

 

 

 

8.   Using the backup history, you can select the correct backup or you can find it using the ellipses button on the right. Whatever you choose, select the full database checkbox only. Not the differential.

 

 

9.   Click the Options page to see the restore options. As we are restoring over the top of an already existing database, select the Overwrite option. This prevents you from accidentally overwriting a database. The default is off. Click OK.

 

 

10.  If you run the query to find the first name of the customer Abel, it should be Catherine.

 

 

A. Full Backup Restore (without differential)

You can do the restore without using the interface, by using the following T-SQL code.

USE master;
GO
RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\Backups\ADWORKSFULL.bak’
WITH FILE = 1,
REPLACE,
GO

Notice the use of FILE. The file value refers to a backup set file number. This option allows you to specify a specific backup in a media set based on its position number. This value was actually shown in the figure under point 7 above. You need this information to ensure that you are restoring the correct backup from the media if there are multiple backups stored on the same media. To determine the different backup set file numbers, try the following:

RESTORE Headeronly
FROM DISK= ‘E:\Backups\ADWORKSFULL.bak’
GO

Using REPLACE this restore will overwrite the existing AdventureWorks database on this server with the Full database backup. The first name of customer Abel is now Catherine.

 

B. Restore with Differential

Since we took a differential database backup after the customer name was updated to Irene we can restore the database using both the baseline full backup and the differential using the following:

USE master;
GO
RESTORE DATABASE AdventureWorks
FROM DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH FILE = 1,
REPLACE,
NORECOVERY;
GO

This looks almost identical to our initial restore code except that we use NORECOVERY. This will put the database into a recovery state allowing us to then include the differential backup (allowing us to get the updated record that has changed customer Catherine to Irene). Just refresh the database in the interface and you will see. We can now include the differential backup while the AdventureWorks is in a recovery state. Remember you can ignore any previous differential backups since the ‘last’ differential is the only one you need. I have taken 2 differential backups following the full backups so the ‘position’ number equals 3. Its this file number that I’m interested in.

USE master;
GO
RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\Backups\ADWORKSFULL.bak’
WITH FILE = 3
GO

We do not use the REPLACE option as we are using the differential and not the full backup. Also notice that there is no indication that this is a differential. A query should show that the customer is Irene.

 

C. Restore with Full Backup, Differential and T-Log

To do this delete the backup file first and lets start from scratch. Once deleted, make sure our customer record is set back to Catherine. Then take another full backup.

USE AdventureWorks;
GO
Update Person.Contact
SET FirstName = 'Catherine'
Where LastName = 'Abel'

 

USE master;
GO
BACKUP DATABASE AdventureWorks
TO DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH RETAINDAYS = 7, INIT;

Now that we have our initial backup once more, lets change the customer name to Irene, check and take a differential backup

USE AdventureWorks;
GO
Update Person.Contact
SET FirstName = 'Irene'
Where LastName = 'Abel'

 

USE AdventureWorks;
GO
SELECT FirstName
FROM person.Contact
WHERE LastName = 'Abel';
GO

 

USE master;
GO
BACKUP DATABASE AdventureWorks

TO DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH DIFFERENTIAL,
RETAINDAYS = 7, NOINIT;
GO

Now you can check the File Position numbers, and you should see two files.

RESTORE Headeronly
FROM DISK= 'E:\Backups\ADWORKSFull.bak'
GO

OK, now we can change the customer name again (perhaps to Letitia…) and after take a T-Log backup.

USE AdventureWorks;
GO
Update Person.Contact
SET FirstName = 'Letitia'
Where LastName = 'Abel'

 

USE master;
GO
BACKUP LOG AdventureWorks
TO DISK = 'E:\Backups\ADWORKSTLOG.bak'
WITH INIT;
GO

Two .bak files now exist. Lets make a final change to our database. Change Letitia to Magda and check. Then we can backup the T-Log

USE AdventureWorks;
GO
Update Person.Contact
SET FirstName = 'Magda'
Where LastName = 'Abel'

 

USE AdventureWorks;
GO
SELECT FirstName
FROM person.Contact
WHERE LastName = 'Abel';
GO

 

USE master;
GO
BACKUP LOG AdventureWorks
TO DISK = 'E:\Backups\ADWORKSTLOG.bak'
WITH NOINIT;
GO

Now if you check the file position numbers for the T-Log you should see two entries:

RESTORE Headeronly
FROM DISK= 'E:\Backups\ADWORKSTLOG.bak'
GO

OK, so to recap the name changed from Catherine to Irene to Letitia to Magda. Say we want to restore the whole thing. Remove the database. Then restore the database using the full backup. AdventureWorks will be put into into restoring mode.

USE master;
GO
RESTORE DATABASE AdventureWorks
FROM DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH FILE = 1,
REPLACE,
NORECOVERY;
GO

Now that's done, use the last differential. Check the file position numbers:

RESTORE Headeronly
FROM DISK= 'E:\Backups\ADWORKSFULL.bak'
GO

In my case position 2.

USE master;
GO
RESTORE DATABASE AdventureWorks
FROM DISK = 'E:\Backups\ADWORKSFULL.bak'
WITH FILE = 2
GO

Now, that's been sorted I apply the T-Log backup. Use position 1 then 2 in that order

USE master;
GO
RESTORE LOG AdventureWorks
FROM DISK = ‘E:\Backups\ADWORKSTLOG.bak’
WITH FILE = 1,
NORECOVERY;
GO

 

USE master;
GO
RESTORE LOG AdventureWorks
FROM DISK = ‘E:\Backups\ADWORKSTLOG.bak’
WITH FILE = 2,
RECOVERY;
GO

The final log is restored with the RECOVERY option to make the database accessible to users. In a real recovery scenario, this will usually be the orphaned log.