SQL Server Backup And Restore Planning

The importance of regularly backing up SQL Server databases is often stressed, but merely taking backup is not enough to restore a database if a disaster strikes. Having a plan to restore the backup with minimal downtime and data loss is equally important.

In other words, if a database becomes corrupted or inaccessible and the database owner wants to bring it back online within an hour with minimum data loss, it's important to have a robust backup plan.

Backup and Restore Plan

In the event of SQL Server crash, database corruption, etc., you can consider using Log Shipping, Database Mirroring, and other High Availability (HA) techniques to maximize database availability and minimize data loss. But, having a well-defined and tested backup and restore plan is vital for quick disaster recovery. This plan comprises developing a Service Level Agreement (SLA), a document to set acceptable expectations regarding possible data loss, the period of downtime, and the cost of implementing the backup and restore.

An SLA is an agreement between a database administrator and the database owner that provides a level of commitment regarding the availability of the database and its data.

To plan an SLA, it's important to understand the backup and restore requirements (which we will cover in the following sections).

To have an efficient backup and restore plan, let's create a test database, take a backup of the new database, and restore it.

Backing Up SQL Database

Before proceeding with backing up a SQL database, it is important to determine the backup requirements,

What should be the level of server on which the database will reside?
Losing a week of development changes can be handled, but losing a week of production database changes cannot be accepted. So, you'll need to decide whether you want to keep the database on a development server, production, or test server?
Do we need to back up every database?
If the data is frequently refreshed every few days, you may not need to back up that database. This can help you save resources and time required for taking database backups from another server.
Deciding the backup type depending on how much data loss is acceptable?
You may need to take transaction and log backups in addition to full database backups to avoid downtime and loss of data. This requires using the High Availability (HA) technique and following a rigorous backup regime', but this can cost you a lot of resources, time, and money.
How often and when does a database need to be backed up?
Another factor to consider when backing up a database is when it should be carried out. You should take a full backup at a time when the database is least used and plan log and transactional backups around the full backup schedule. The Log (or transaction log) backups should be scheduled before normal database use to capture all the data changes.

How to Perform SQL Database Backup?

In this section, we will discuss the step-wise instructions on taking full backups. Full database backup is an important component of disaster recovery strategy for any database. That is because the full backup helps restore the database (in the event of catastrophic hardware failure, data corruption, etc.), keeping most of the data intact.

Execute the following query to create a sample database, let's say, DatabaseBackup,

CREATE DATABASE [DatabaseBackup] ON PRIMARY (
NAME = N 'DatabaseBackup', FILENAME = N 'C:\SQLData\DatabaseBackup.mdf',
SIZE = 510000KB, FILEGROWTH = 102300KB
) LOG ON (
NAME = N 'DatabaseBackup_log', FILENAME = N 'C:\SQLData\DatabaseBackup_log.ldf',
SIZE = 102300KB, FILEGROWTH = 10230KB
) GO

Now, let's discuss the process of taking full backup of the 'DatabaseBackup' database,

Using SSMS

Step 1

Open SSMS and connect to an instance of your server.

Step 2

In the Object Explorer window, expand Databases, and then right-click on a database you want to back up, click Tasks > Backup. This will open a dialog box named 'Back Up Database'.

Figure 1 - Select Back Up Task

Step 3

In a General section, do the following,

Choose the type of backup from the Backup type drop-down list.
Select Database under Backup Component.

Note
Choose Files and filegroups' option under Backup component when you need to create backup for databases having more than one filegroup.
The information identifying the backup set, including db name and description fields, is displayed in the Backup set section. Also, there's an option to set the expiration date of the database backup file. Setting this date will let the SQL Server know for how long it should keep the backup file.
Next, specify the backup media under the Destination section for storing the backup.

Figure 2 - Specify Database Backup Details
Click the Add button to open 'Select Backup Destination' window. In this window, click Browse. From the 'Locate Database Files' window, find the SQL backups directory you have created and then specify the name for the backup file, for instance, DatabaseBackup as shown in the figure below,

Figure 3 - SQL Database Backup File Configuration
Once the backup file is configured, click OK. Again click OK from the Select Backup Destination dialog box. This will open the 'Back Up Database' page. Now click on the Options section under 'Select a Page'. This will open the Options page with options as shown in the below image,

Figure 4 - Configuration Options for SQL Database Backup

Step 4

In the Options page screen, perform these steps,

From the Overwrite media section, choose to append the new backup to an existing backup set or overwrite the backup set that already exists with the new one.
From the Reliability section, select any one option to validate whether the backup is corrupt or not. These options are as follows,

Verify backup when finished: It checks if the backup set is complete after completing the backup process.
Perform checksum before writing to media: This option allows SQL Server to validate a checksum operation on the backup file's data before writing it to the storage media.
Continue on error: Selecting this option instructs SQL Server to proceed with the backup operations even if an error occurs during the backup operation.

Executing these steps will create a full backup of the SQL database. Now, let's discuss the steps to take full backup using Transact-SQL (T-SQL) script.

Using T-SQL

Run the following query to take full backup of the newly created database,

USE master
GO
BACKUP DATABASE [DatabaseBackup] TO
DISK = N'C:\SQLBackups\FolderName\DatabaseBackup_Full_Native_2.bak'
WITH FORMAT, INIT, NAME = N'DatabaseBackup-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

In the above code, the first line instructs SQL Server to take a full backup of the database named 'DatabaseBackup'. In the second line, the database is backed up to disk, and a complete path of the resulting backup file is provided. Let's go through each of the parameters,

FORMAT
It lets SQL Server know if the media header information should be overwritten or not. Selecting this option will remove any information that already exists when the backup is initialized.
INIT
This option tells SQL Server that if a command is executed twice, overwrite the existing backup set with the newly created one.
NAME
It helps identify the backup set.
SKIP
This option makes SQL Server skip the expiration check on the backup set.
NOUNLOAD
This parameter tells SQL Server to avoid unloading the tape from the drive on completion of the backup operation.
STATS
It defines at what time interval SQL Server should update about "backup progress" messages. For example, defining 'stats=15' will make the Server send a status message after each 15 percent of the backup completion.

Now that we have created a full database backup, it's time to understand the database restore requirements and how it is performed.

Restoring SQL Database

Important! You cannot restore individual tables using the native SQL Server restore tools. You'll need to restore the complete database to extract a specific table or other database objects. Also, performing restore using the native SQL tools might result in data loss. However, using third-party tools, such as Stellar Repair for MS SQL allows selecting an individual table or other database objects that you want to restore without data loss.

Besides ensuring timely and adequate backups, we need a plan to perform crash recovery in emergency situations. For instance, restoring a database from the backup when the original database gets damaged. It is also important that you restore the backups in a way that meets data loss requirements and in an acceptable recovery time. The recovery time, however, can vary depending on,

Database size
You cannot expect to restore a 500 GB database and recover all its data in a few minutes. Restoring a full database backup can take hours or even days.
Where the backup files are saved
When the backup files are stored on site, you only need to invest time in performing the restore and recovery process. However, for backup files stored in tapes for offsite storage, you'll need to plan extra time to retrieve those files first and then perform the restore and recovery process.
Complexity of the restore operation
Restoring a full db backup is a straightforward process. However, performing a point-in-time restore and recovery of database using full, differential, and log files can be a complex process and may require more time to complete.

How to Perform SQL Database Restore?

Step 1

Start SSMS, connect to your SQL Server instance, and then expand Databases.

Figure 5 - Expand Databases

Step 2

To initiate the database restore process, right-click on the database (i.e., DatabaseBackup), and then click Tasks > Restore > Database.

Figure 6 – Start the SQL Database Restore Process

Step 3

A 'Restore Database' window opens with a few auto-filled options. For example, the database name appears similar to the source database we have backed up.

Figure 7 - Restore Database Window

Step 4

In the Source for restore section, follow these steps,

Select From device, and then press the ellipsis button (…). This will open up a Specify Backup window with media type auto-filled with File.
Click Add to open the window that helps you locate and select the backup file.
Press OK to go back to the Restore Database window. The backup file’s details will be added in the 'Select the backup sets to restore' section.
Check the Restore column box to select that backup file.

Figure 8 - Configuration for Native Backup Restore
Click on the Options tab on the left side of the window (refer to Figure 7). This opens a window with Restore options, as shown in the image below,

Figure 9 - Database Backup Restore Options
In the above window, choose any of these restore options,

Overwrite the existing database
Select this option if you would like to overwrite the original database information with backup of the database.
Preserve the replication settings
Use this option for a replication-enabled environment. It allows re-initializing replication after performing the restore process without the need to reconfigure the replication settings.
Prompt before restoring each backup
Select this option to receive a message prompt before the backup files are processed. Skip this option when restoring a single backup file.
Restrict access to the restored database
Choose this option to restrict access to the restore database to db_owner role members.

Next, specify the location of MDF and LDF files for the restored db.
Finally, specify the recovery state of the backup files to be restored.

Step 5

Click OK and wait for the restore process to complete.

What if the SQL Database Restore Process Fails?

The restore process may fail if the backup gets corrupted or becomes inaccessible. Using specialized SQL repair software can come in handy in such a situation.This can be used to repair severely corrupted SQL database (MDF/NDF) files following a few simple steps.

Wrapping Up!

Having the latest backup copy is crucial for restoring a SQL database in the event of a server crash, hardware failure, data corruption, etc. However, it's also important that the backup be restored to minimize database downtime and recover maximum data. For this, you need to develop SQL Server backup restore to act quickly in an emergency, such as data