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.

SQL 2008 Backup Strategies

For some reason I have found the SQL recovery models and backup strategies a strange mix of being confusing but interesting. To help understand the subject of backup/restore and SQL’s different recovery models I thought I’d share my understanding with the world at large. Maybe if anyone out there is reading this you can contribute as well.

The backup strategy you use depends on a variety of recoverability considerations:

• What is the level of transaction volume. Does the database change minute-by-minute or say hour-by-hour?
• What is considered to be an acceptable recovery time?
• What is considered an acceptable level of data loss? Maybe you need to return to an exact moment in time.
• How big can a backup be?

A backup strategy will require you to make decisions as to which kind of backup to make. And there are several. So you will need a fundamental understanding of these backup types.

Full Database Backup

A full database backup will truncate the transaction log and then copy every remaining data page and transaction log page to the backup media. The transaction log truncation will be non-reorganizing, meaning that no attempt is made to defrag/compact the log. It is simply truncated to the point of the last required transaction. Most backup strategies require a full database backup as the baseline for recovery. Remember that the log file will be truncated! From what I've read there is little point on having multiple log files. Keep just one. Also place the transaction log on a separate physical structure from the database. That way a loss of the disk containing the data files will not affect the log file. This may also help performance as log files are written to sequentially. Also, use RAID 1 so the log will be available in case of device loss. Regular backups will mean that not only will the log file not get too big but this will help prevent fragmentation.

Differential Backup

A differential backup will store all of the database pages that have been modified since the last full database backup. Note that this is a true differential backup and not an incremental backup. This means that each differential backup is inclusive of all transactions executed since the last full database backup and not simply since the last differential backup.

File or Filegroup Backup

If you are dealing with a very large database, you can back up individual files or 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. If there is a failure affecting only one file or filegroup, only that portion and subsequent transaction logs would need to be restored. The log file is not in a filegroup.

Transaction Log Backup

This backup type will perform a non-reorganizing backup of the transaction log and store the transactions to the backup media. The backup types mentioned above store copies of the data pages at a particular time. This type of backup stores the actual transactions statements. When you restore using the full or differential backups, using the transaction log backup as well will involve replaying (if that’s the right word?) or re-executing the transactions on the log backup again which would be written back to the database. This process could take some time.

Recovery Models

Now that we understand (I hope) the different backup types, you need consider SQL’s three recovery models. Recovery is all about how the log file is treated by the SQL server on a day-to-day basis and what is made available for backup. Remember its all about the log file! Recovery in this context is about the level of logging and log retention.

You can determine the recovery model (which by default will be full) in the following way:

1. Connect the SQL Server Management Studio to the correct instance hosting the database
2. Expand the Databases folder and locate the right database
3. Right click the database and select Properties
4. Select Options
5. Decide on the recovery model by selecting Recovery Model

 

You can alter the recovery model using the following statement:

alter database TESTDB
set recovery Full

You should realize that the recovery model you choose will impact on the backup method you choose.

Simple

Simple means that the log file will be truncated each time the data pages and log pages held in RAM are flushed are written to disk (checkpointed).  This keeps the log file small (there’s no point in backing up the log, in fact you can’t) which is good. But, you will not be able to recover to a point in time. You wouldn’t usually use this one. You might if it was a read-only database or you were developing a database application perhaps.

Bulk-Logged

The bulk-logged recovery model uses less disk space than a full logging solution by performing minimal transaction logging for the following operations:

• SELECT INTO
• bulk-load
• CREATE INDEX
• All operations involving text and image data types

A database that is in bulk-logged recovery mode cannot be recovered to a specific point in time if a bulk transaction has occurred. You still require log backups. A bulk insert (where you might be inserting a million rows into a table) would cause the log file to become very large if every transaction was recorded (if in Full mode) and would have performance implications. So you can switch to bulk logged from full just before the bulk operation. Once complete you set the recovery model back to full.  The bulk operation would be logged as a kind of summary statement. So every transaction would be recorded while being in Full mode, then a summary of the bulk operation and then a continuation of all transactions when in Full mode again. What about the point-in-time recoveries? If the database is in the bulk-logged recovery model and no bulk actions have occurred since the last full backup, the database can be restored to a point in time. If, however, a bulk action has occurred, it can only be fully restored. So, it minimally logs bulk transactions but fully logs other transactions.

Full

The full recovery model is what you would use most of the time. It will give you the best recoverable opportunity at the expense of logging overhead however. Microsoft recommend that you use this model over the other two. The full recovery model will log every transaction to the log and is persistent after a checkpoint. A transaction is a change and any change on the database will cause an entry to be added to the log! A read does not cause a change so this of course will not cause an entry to be made to the log.

 

OK, so we are happy with the different backup types and with the different recovery models. The recovery models really describe how the transaction log is written to and whether the log truncates after a checkpoint or after a backup. With this combined knowledge we can consider the following backup strategies:

 

Strategy One: Simple

This strategy is suitable under the following conditions:

1. The database is relatively small
2. The database does not change minute-by-minute (less volatile)

With this strategy transaction log growth is kept under control, you won’t have to backup the transaction logs but this will mean there may be a small amount of data loss.

How do you do it ?

1. Set the recovery model to simple
2. Take full backups on a schedule of your choice (every night perhaps)
3. If there is a failure you will have to restore the most recent full database backup. That’s it.

Simple recovery means no transaction logs to use in the restore process. You won’t be able to return to a point-in-time and data loss will probably occur. But this of course depends on how dynamic the database is.

 

Strategy Two: The Database Only Backup Strategy

This strategy is suitable under the following conditions:

1. Low transaction volume
2. The transaction log be on a separate hard disk from database. Hardware failure of the database does not affect the log.

With this strategy, the transaction log is truncated because of a full database backup.

How do you do it?

1. Set the recovery model to Full or Bulk-Logged.
2. Take full database backups on your preferred schedule (perhaps every night)
3. If there is a database disk failure, begin by backing up the orphaned log
4. Restore from the most recent full backup followed by a restore of the orphaned log.

As you can see, database only backup can be restored to a point-in-time where the that time starts from the last full backup to time of disaster. The orphaned log would have transactions from the last full backup to time of disaster. As long as the log stays safe on another disk from the database your OK. If you lose the log though you lose transactions from the last full backup to time of disaster. As long as you make regular full backups and you have low transaction volume you should be OK.

 

Strategy Three: The Transaction Log Backup Strategy

This strategy is suitable under the following conditions:

1. Higher transaction volumes (causing increased log growth)
2. Longer restore time is acceptable

Instead of backing up the database file as a way of truncating the log, you backup the transaction log file. Backing up the transaction log will truncate log and keep its size under control. Although the backup up time will be relatively quick to do, the restore process will take time.

How do you do it?

1. Set the recovery model to Full or Bulk-Logged.
2. Take a full database backup that will act as the transaction log baseline (perhaps at 1:00AM)
3. Take regularly scheduled full database backups with periodic log backups in between (perhaps every at 6 hour intervals; 7:00 AM, 1:00 PM, 7:00 PM)
4. If there is a database disk failure, begin by taking a backup of the orphaned log immediately
5. Restore the most recent full database backup, followed by each of the subsequent log backups in the order that they were taken.
6. Finally, restore the orphaned log.

 

So if your first full backup was taken on Monday at 1:00 AM and a disk failure occurred at 6:00 PM on Tuesday you would take the following steps:

1. Immediately take a backup of the orphaned log file
2. Restore in the following order:
   • Full backup from Tuesday 1:00AM
   • T-log backup from Tuesday 7:00 AM
   • T-log backup from Tuesday 1:00 PM
   • Orphaned log at Tuesday 6:00 PM
3. Pray

 

Strategy Four: The Differential Backup Strategy

The transaction log strategy described above can be slow. The more you have the longer it will take to restore the database to the point of failure. If the changes made to a database are restricted to a particular number or subset of data pages, you could take differential backups instead of full backups. The transaction logs would then need to be restored only from the point of the latest differential backup.

1. Set the recovery model to Full or Bulk-Logged.
2. Take a full database backup that will act as the transaction log baseline.
3. Take periodic full database backups as needed (perhaps once once a week)
4. Take differential backups between the full database backups to record only the data pages that have been modified since the last full database backup
5. Take transaction log backups between the differential backups to record the individual transactions between each of the differentials.
6. If there is a database disk failure, begin by taking a backup of the orphaned log.
7. Restore the most recent full database backup followed by the most recent differential backup.
8. Restore all transaction log backups taken since the last differential backup in the order that the backups were taken.
9. Finally, restore the orphaned log.

Assuming the above model, a disaster at 1:00 Wednesday would require the following steps:

1. Immediately take a backup of the orphaned log file
2. Restore in the following order:
   • Full backup from Monday 1:00 AM
   • Differential from Tuesday 6:00 PM
   • Transaction log from Wednesday 10:00 AM
   • Orphaned log at Wednesday 1:00 AM
3. Beer

Pre-requisites for Installing SQL Server 2008 on Windows Server 2008 R2 Using PowerShell

First step is to allow execution of scripts by changing the execution policy:

[PS] Set-ExecutionPolicy unrestricted (then say ‘Yes’)

 

Then copy and paste the following commands to install the pre-requisite operating system components needed

[PS] Add-WindowsFeature AS-NET-Framework,web-server,Web-Static-Content,Web-Default-Doc,Web-Dir-Browsing,Web-Http-Errors,Web-Http-Redirect,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Basic-Auth,Web-Windows-Auth,Web-Client-Auth,Web-Cert-Auth,Web-Filtering,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Tools,Web-Mgmt-Compat,Web-Metabase,Web-WMI,Web-Lgcy-Scripting,Web-Lgcy-Mgmt-Console –restart

Troubleshooting Exchange 2010 Management Tools Startup Issues

Pretty good article on how to fix PowerShell errors

http://blogs.technet.com/b/jribeiro/archive/2010/04/05/troubleshooting-exchange-2010-management-tools-startup-issues.aspx