Databases (47)

Recovering SQL databases. Thursday, 21 January 2016 00:00

Recovering SQL databases.

If you operate an SQL server, you should be aware of the dangers and how to recover if the worst happens.

SQL server recovery information...

The thought of an SQL database crash is something which can cause nightmares. Lost information, corrupt data, inaccessible systems and inoperable services can all occur and whilst pre-planning can help to eliminate some problems, unforeseen emergencies can really mean a bad day in the office for all.

If your company operates an SQL server, here’s some information as to what you could be dealing with should the worst happen and the solutions available. The important elements of an SQL server.

Mainstream support for Microsoft SQL Server 2005 ends in half a year – April 2016. This provides plenty of time for CTOs and DBAs to prepare a Microsoft SQL Server upgrade or migration plan, but what are the options open to them?

The platform

At this point in time, the smartest move would be to skip Microsoft SQL Server 2008 and 2012, and move straight on to SQL Server 2014. Choosing to use the latest version of the database engine will help to maximise the lifespan of the platform and lengthen the time between upgrades.

Your business will also be able to take advantage of new features, like full text search, that are not available in intermediate editions of Microsoft SQL Server.

Upgrade option #1 – In-place upgrade

Simultaneously the easiest, and potentially most risky option, an SQL Server in-place upgrade involves installing the new software, preferably SQL Server 2014, over the top of the existing system. The database engine is then upgraded, as are the tables and any other “moving parts”.

However, anything but the most basic of SQL Server environments (think single instance) is unlikely to be quite so straightforward. The in-place upgrade route is probably not the correct upgrade path for enterprise databases.

The other potential problem with in-place upgrades is the lack of simple rollback in the event of a problem. The database server(s) will need to be taken offline, whilst a full SQL Server recovery procedure is performed using the last full back up.

If you’re an SQL server fan, you’ll fall in love with SQL Server 2016 instantly. The database’s preview release by Microsoft is quite promising and stands apart from the other SQL server releases. The 2016 version won’t revolve around Azure features as much as the previous versions and the reason behind that approach is Microsoft’s plan to blend in a little bit of both worlds – the on-premise flavor of SQL server as well as the Azure SQL database. With the code base for both being common, product changes are expected to be adapted in a more Agile way.

The latest version of the database is bound to include a little for everyone. With an impressive feature set, SQL server 2016 is surely going to be worth the wait. Let’s check out some brilliant features of the forthcoming database version.

For very large Microsoft SQL Server databases, a complete restore operation can take many hours. During this time the database cannot be used to prevent data being entered and lost as information is copied back from tape or disk. Obviously in a high-availability environment any downtime is costly, so keeping it to a minimum is essential.

Fortunately page level restore techniques can be used to keep recovery times to a minimum by reducing the amount of data that needs to copied back from the backup media. Since the release of Microsoft SQL Server 2005, DBAs have had the option of carrying out a ‘page level restore’ which allows them to recover a ‘handful’ of pages, rather than having to restore entire datasets and copy information back into the original database.

The page level restore operation is perfect for situations where data becomes corrupted during writes through a faulty disk controller, misconfigured antivirus software or an IO subsystem. Better still, restore level operations can be performed online for Enterprise editions of Microsoft SQL Server.


As with any database recovery operation, page level restores are reliant on having a complete backup from which to work. If such a backup is not available, you will need to investigate an alternative method of recovering data from the server disks direct.

And although you can carry out the page level restore with the database online, you may decide to keep things safe by switching to single user mode whilst you transfer data using:


This command ensures that everyone is out of the system and cannot enter until you change the mode back again. You will also want to ensure that you have the end of the log file backed up so that you have all transactions fully accounted for and to prevent any further data loss:

TO DISK = N'X:\SQLBackups\DBName_TailEnd.trn'

General Backup and Recovery questions

Why and when should I backup my database?

Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!

Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule. Backup frequency normally depends on the following factors:

  • Rate of data change/ transaction rate
  • Database availability/ Can you shutdown for cold backups?
  • Criticality of the data/ Value of the data to the company
  • Read-only tablespace needs backing up just once right after you make it read-only
  • If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
  • If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
  • Etc.

Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.

Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunication.

However, there are scenarios when you wouldn't have enough space to complete SQL Server backups to a particular drive or the database backup is taking considerable time to complete. One possible solution might be to split database backup between multiple files in SQL Server to reduce the backup time and to use available space on multiple drives.

Why split database backups?

  • Achieve faster database backup by splitting database backups to multiple files. By writing database backups to multiple files located on multiple drives, you can easily achieve a higher I/O, thereby reducing the time necessary to perform database backups.
  • Using the Split File Backup option, you can easily split a very large backup file into multiple files of the same size.
  • Since the database backup is split into multiple files, each file will be smaller, allowing the DBA to copy the files easily across the network or to tape.
  • Open SQL Server Management Studio, expand Databases node and then right-click the database for which you would like to perform a Split File Full backup and click Tasks à Back Up… as shown in the screenshot below. For the purposes of this article, I am using the sample database named AdventureWorks2008R2.

In a previous tip we discussed the different recovery models SQL Server offers and decision points on which to base your recovery model selection. After you've selected the appropriate recovery model, you need to put in place the proper backup strategy to minimize data loss and downtime in case of a database failure.

I have seen many database installations wherein the correct recovery model is in place to minimize data loss, but the correct backup options and plans are not in place. Often the Full Recovery model is selected, but the only backups occurring are full backups. Occasionally, the Simple Recovery model is selected, even though the customer base could not afford to lose all transactions since the last full backup.

Creating a full backup once a day probably makes a lot of sense to most people, but that should only be the beginning of your backup strategy. We will take a look at some of the components to help define a backup strategy as well as the different backup options that are available.

First, let's look at the backup options:

Database (Full)

  • This option creates a full backup copy of the database at the time the backup occurs.
  • Available for all recovery models.


  • This option creates a copy of the active transaction log, which lists any transactions that have occurred since the last backup.
  • Available only for Full Recovery or Bulk-Logged Recovery models.


  • This option copies only the database pages that have been modified after the last database backup.
  • Available for all recovery models.
  • When to use Differential backups.
    • If you issue several transaction log backups throughout the day, it is helpful to also use differential backups in conjunction with database and transaction backups. The use of differential backups minimizes the number of restores that need to occur in case of a failure.
    • Here is an example: If you issue one database backup at midnight and transaction backups every 15 minutes, by 9 a.m. you will have 37 backup files. If you need to restore your database to 9 a.m., you will have to restore all 37 files. If you also issue differentials every three hours, and you need to restore your database to 9 a.m., you will only need to restore two files (the full and the differential that occurred at 9 a.m.). You can see how this could greatly decrease the time it takes to do a restore of a database and, therefore, minimize downtime.

SQL Server backup and recovery procedures must be in place before a database crash occurs -- or precious data and company time will be lost. This comprehensive checklist will help you set up, safeguard and test your backups.

The key to a successful recovery in the event of a database crash -- or worse yet -- a system crash, is to have the key elements for SQL Server backups in place. SQL Server offers many options for performing backups and the following checklist highlights some of the more important options to think about. This list is a comprehensive starting point for getting your backups in place, safeguarding your backups and testing. Take the time to understand all of the components. It will go a long way toward safeguarding your company's most prized asset and removing the possibility of data loss from the equation.

Select recovery model

The first thing you need to do is figure out which recovery model you will use for your databases. This option is set for each database on your server and can be set using Enterprise Manager or T-SQL. This starting point will determine the type of backups you will run and the type of restores you can run. SQL Server has three different recovery models: Simple, Full and Bulk-Logged. To learn more about recovery models, refer to this tip: Selecting a SQL Server recovery model.

Select backup options

Next, determine what types of backup you will run. SQL Server offers four backup variations: Full, Differential, Log and File and Filegroup. Each backup option offers different levels of recoverability; the options are also based on the recovery model you select. To maximize backup and restore times, the best option to choose is a mix of Full, Differential and Transaction Log backups. This will ensure you have the ability to do point-in-time recoveries, and it minimizes the number of files to restore in case of a failure. To learn more about backup options, refer to this tip: Selecting a SQL Server backup model.

Determine backup schedule

Once you have decided on backup options to use for your databases, you can set up a schedule of when your backups will run. A good rule of thumb is to run your Full backups during off hours or very low peak times. The scheduled time of the Full backup then determine when you will run your Differential and Transaction Log Backups. If you are not sure when to run each type of backup, a good practice would be to run Full backups daily, Differentials every three hours and Transaction Log backups every 15 minutes. Each environment is different, so you should set these options based on the acceptable amount of data loss in case of a failure.

SQL Server 2000 offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.

Most people either select full or simple for all of their databases and just stick with the same option across the board. In most cases, selecting the full recovery model is the smartest option, because it gives you the greatest flexibility and minimizes data loss in the event a restore has to take place.

Although using the full recovery model makes logical sense, there are reasons why the other two options are available. We will further define why there are three options and when you might want to use the different options to protect your databases. First, let's take a closer look at each model.


The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.


The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.

Disaster recovery in SQL Server relies on three components: people, processes and technology. Your failover and failback solutions must minimize SQL Server data loss. Edgewood Solutions' Greg Robidoux guides you through decisions you'll face, including that of the appropriate DR budget to match your business goals.

There are several components to your disaster recovery plan. Enabled people, processes and technology are areas that rely upon one another. I'll discuss the level of importance all three have on a successful DR plan.

The reason for creating a DR plan is to ensure that your systems will continue to run as needed in case of any failure, whether large or small. In most cases, disaster recovery scenarios never get exercised. They act more like an insurance policy minimizing data loss and/or downtime to the smallest amount possible in the case of a failure.

Before you begin putting together a realistic DR plan, you need to think about two things: budget and need. Just about anything is possible if you have enough money, but your budget is one area that often causes you to implement a less than ideal disaster recovery solution. For the second element, need, ask yourself if your disaster recovery plan is the proper plan for meeting your business goals. In many cases, disaster recovery plans are over-engineered because of a budget surplus or the strategists do not understand the true business need. Or, the plan is under-engineered because of a lack of dollars or, again, the assessment of the real business need wasn't thorough enough.

Before you begin selecting and implementing a plan, answer these two questions:

  • If a failure occurs, how much data loss can I afford?
  • If a failure occurs, how much downtime can I afford?

Once you answer these questions, you can begin to formulate the type of solution you need as well as the costs. Oftentimes when speaking with clients the first answer is we cannot afford any data loss and we do not want any downtime. When you discuss the dollars needed in order to meet their criteria, the client's tune usually changes and so does the plan. Next, the real analysis begins.

So let's take a look at the three areas -- people, process and technology -- to see what needs to be put in place in order to have an effective DR plan within your budget.

Page 1 of 4