Preface
In the first article in this series published last month I mentioned that I’ll be presenting a session at the 2016 IT/Dev Connections Conference titled (some) of the Top 10 Things Every Accidental DBA Needs to Know. Why “some”? Because the list is actually endless – it’s difficult to say what is considered important for a DBA just starting their professional journey and one who is an established database administrator moving towards Senior DBA status.
In advance of this year’s IT/Dev Connections conference I’ll elaborate on these ten points I intend to cover in my session and will extend this into a regular series of articles aimed at those IT professionals who have found themselves assigned stewardship of their company’s data.
This first two articles cover what are arguably the most important aspects of the Database Administration profession: the ability to recover lost or damaged data through the backup and restore process. I’ve always stated that a successful backup is irrelevant – only a successful restore with no loss (or at the worst acceptable loss) of data is the benchmark for a Database Administrator’s success.
We covered the basics of backups in the first article. Of course I stress the importance of a successful restore but of course you can’t have that with at least one viable backup. This article covers what comes next and what can sometimes strike fear into the heart of a new DBA – particularly one who took the reins not by choice but rather by circumstance.
Types of Restores
Just as there are multiple varieties of backups in Microsoft SQL Server there are also multiple types of restore processes that can be utilized when facing the need to recover a database – or part or a database – that has gone foul.
There are three basic types of backups in Microsoft SQL Server – we will be covering :
-
Database
-
Transaction Log Restores
-
File and Filegroup Restores
-
Page Restores
We will cover the first two restore processes here as they’re the ones a DBA is most likely to encounter. Restoring pages and files or filegroups are advanced topics that will not be covered in this series since we’re trying to keep it aimed at the new Database Administrator.
Author’s Note: I move forth with the expectation that you either have a working understanding of backing up SQL Server databases or have at least read the first article in this series.
Database Restores
A database restore involves at least one backup file – and it has to be a full backup file. Using a single backup file, if successful, will produce a restored database to a point of time when the backup was taken. It will be a copy of that database at that time less any transactions that were uncommitted. Database restores are a common method of migrating copies of databases amongst server for such things as testing or troubleshooting; so long as the database is reasonably sized (and by reasonably sized that is a loose term for manageable with the constraints of time and disk space you need to move it around.
By no means though are you constrained to the single file – restore-just-to-the-point-of-the-last-backup situation. You can string together multiple backups (of multiple types) in order to successfully bring a database up to a point in time right before failure or corruption should you choose and if your backup and restore strategy are proper. If your database is in a logged recovery model (Full or Bulk-Logged – see the backup article in this series for explanations of recovery models) then you can perform a chain of backups to do just that – recover right up to the point of failure. In this case you would do so by first applying your base database backup followed (possibly) by a differential backup and then finally by one or more transaction log backups up to a point in time of your choosing. That point in time could coincide with the end of the last transaction log backup to be applied or somewhere in the middle of that last transaction log backup.
The base backup will build your platform for the restore process. You can stop here if you don’t need to apply any further backups to meet your recovery needs. If you need to move towards a point in time recovery you’re then left to a mixture of differential and transaction log backups. As explained in the last article, differential backups capture all changed pages in the database since the last full backup. This means that if you schedule your full backups at noon each Monday and your differential backups at 11:00am every day then each sequential daily differential backup will get larger until the next full backup is cut that following Monday at noon. (Assuming there is activity in the database on a regular basis and that activity involves either changes to data via INSERT, UPDATE, or DELETE statements or modifying the structure of objects in the database.) Under this schedule if you have a failure on Wednesday at 1:00pm you would first restore the previous Monday’s full backup followed by the differential backup from Wednesday. Then you’d employ transaction log backups from that point to roll forward the database to a point in time right before 1:00pm.
Hopefully you’re backing up the transaction logs if this sort of recovery is important for you!
Conversely, let’s say the failure is instead at 9:00am on Wednesday. Now you’d have a bit more work because you could not use that Wednesday differential backup as a stepping off point before rolling forward transaction log backups since it was taken past the point of failure you’re trying to recover. Instead you’ll need to apply the Monday full backup followed by the Tuesday differential backup. Then you’ll need to apply as many transaction log backups as it takes to get to 9:00am on Wednesday from the point the differential backup completed on Tuesday.
I outlined this because you need to know two main things about differential backups: you should not have to apply multiple differential backups and differential backups have no concept of understanding when a transaction took place that modified data or the structure of the database – you can’t stop half-way through a differential backup when trying to recover to a point in time.
Recovery, No Recovery, Standby
When discussing restores it’s important to understand the concept of the state you intend to leave your database in once you perform the restore of a single backup be it a database, transaction log, or a differential backup. There are three states to cover:
Recovery
A database in a recovered state means it’s ready for use and you’ll not be able to restore any additional backups to bring the database further along in it’s lifeline. In order to perform a chain of restores you can’t break the log sequence of transactions a database is comprised of. You can think of the log sequence as the story of a database. Each transaction is logged with a log sequence number or LSN. In a logged recovery model, when restoring full, differential, and transaction log backups the backups must be ordered and applied in such a manner that the order of LSNs is not broken. If you were to allow a database to come into recovery and users start creating new transactional activity against the database then you could not apply further backups because that chain is now broken by new transactional activity logging new LSNs. To place a database in a recovered state at the end of the restore process you use the keyword RECOVERY in the WITH clause of the restore command. We will look at syntax at the end of the article. Don’t fret.
No Recovery
If a database is in a recovering state you’ll see it displayed as restoring in SQL Server Management Studio’s Object Explorer. This means that the database is in a state where at least one backup file has been restored and the database is ready to accept the next backup in the chain – be it a differential or a transaction log backup. It could also mean that whomever restored the last backup failed to place it into recovery with the RECOVERY keyword mentioned above. A database in recovery, while able to accept subsequent backup files in the restore process, is not capable of allowing activity from end users – this even includes read activity that would not modify data in any manner. Once a database has been in a recovered state you cannot place it into a recovery state to accept new restore process activity for the reasons already stated above. To ensure a database in the midst of a restore process can accept additional differential or transaction log backups you need to ensure you have the NO_RECOVERY keyword included in the restore command’s WITH clause.
Standby
A database in standby mode is in limbo: it does allow read-only activity by users and with a gentle nudge it can accept additional restore activity. In order to allow this and provide a stable and consistent state of data for querying it must rollback any uncommitted transactions. If it discarded those transactions you’d not be able to restore any further backups because the LSN chain would be broken. So what SQL Server does is stores those uncommitted transactions in what is called an UNDO file. Before any further transaction logs can be restored SQL will need to place the database into a recovering state, apply the transactions stored in the UNDO file, then finally restore the next transaction log in the backup chain. To place a database into a standby state you’ll need to specify the keyword STANDBY in the restore statements WITH clause and also specify a path to the UNDO file.
Transaction Log Backups
At this point I’ve already danced around the concept of transaction log backups. You can use these backup files to roll a recovering database forward to any point in time that is covered in the timespan of the transaction log backup. Any uncommitted transactions at the end of the backup are left uncommitted with the expectation that you’ll be able to apply additional backups to the restoring database. Should you choose to bring a database into RECOVERY it will roll back those uncommitted transactions. Should you choose NO_RECOVERY it will leave them intact and uncommitted. Select STANDBY and you’ll end up with those uncommitted transactions rolled back and preserved in the UNDO file. Transaction log backups store all information necessary to apply the transactions in time order and without collision as though you’re just playing back a recording of the activity in the database – essentially that is exactly what you’re doing when you apply a transaction log backup.
Our Sample Database Backup History
This section and the following section using Transact-SQL uses the following backups of my SQL_Cruise database which is in Full recovery – meaning all transactions are being logged and that if I so choose I could recover to a point in time if my backup files exist for that point in time.
Furthermore we’re going to say that someone (not me of course) dropped a very important table at 20:26 on 8/30/2016 with the following command:
DROP TABLE Very_Important_Table;
The Restore Process: GUI – to T-SQL Process
I will be using the latest download of SQL Server Management Studio to demonstrate. Earlier supported versions will look similar but may differ slightly.
The restore forms are located through right-clicking on the database you wish to recover. Then navigate through subsequent pop-up menus until you get to the different recovery types you can employ.
For this example we need to use the database restore process. When the Restore Database form is displayed it will fill in necessary values to get you to the latest recovery point your backups can recover to based upon stored backup history. In this case we start with the last full backup, the last differential backup prior to what SQL Server thinks is your restore point – the last point in time that’s recoverable, then all transaction logs to get you to that point.
If we wanted to recover to the last point in time that we could we would navigate on to the Files and Options pages in this form. If that is what you need to do now then by all means skip to the next paragraph where we discuss those pages. Those of you hanging on for a point in time recovery join me as I click on the Timeline… button:
This brings up a form that looks something like this in SQL Server Management Studio 2016. The only difference is that Last backup taken would be selected. I’ve already taken the step to fill in the time I want to restore to instead.
If I now click OK we can join the rest of the group on the Files page. The Files page is utilized to allow you to restore to a different location. This is valuable should you want to restore a copy of the database on a different server with perhaps a different file structure. When it comes to the syntax this will add a MOVE sub-statement to the underlying code as we will see later. Since we’re restoring over the existing database we will make no modifications here.
Finally we’re left with the Options page. I’ve filled in the relevant values for this example but will explain each section below:
Restore options:
This is what determines will happen with our existing database when the restore process completes:
Overwrite the existing database is what we want here since we need to replace what (someone) has broken. If you select this option you will lose any transactions that took place since the point in time you recovered to should you bring the database into a recovered state.
If you were dealing with a database under replication you’d want to consider the Preserve the replication settings option to ensure you’ll be able to get this database back into your replication scheme.
Restrict access to the restricted database will not allow general access to the database. You could use this to bring the database up and troubleshoot without letting users back in.
Recovery state:
We’ve covered this at length above. We’re selecting RESTORE WITH RECOVERY to bring the database back online. As you can see you can specify an UNDO file if you were to restore this database into STANDBY at the end of the restore process.
Tail-Log backup:
A tail of the log backup is a special process that is two-phased: a final transaction log backup is taken and then the database is placed into a state that prevents anyone to connect to it other than the connection performing the tail of the log backup and the subsequent recovery process. You can’t restore a database if there are connections to it. What this does is two things: it ensures you have a backup through to the point the database was last “up” should someone change their mind about the point in time restore you’re about to perform and it makes sure you can run the restore by kicking out any existing connections. There are seldom any reasons not to check this as a precaution if running in a logged recovery model.
The final options allow you to force the database into single user mode (in case you’re not performing a tail of the log backup or running in Simple recovery) and an option to have the process prompt you before each file restore step.
At this point we’re ready to bring the database up to the recovery point we’ve been instructed. What I want to show you before we do that is what the code that will execute this process looks like. In most dialogs in SQL Server Management Studio you have the option to script out the work behind the scenes to a new query window, clipboard, or a SQL Server Agent job to execute at a later time. If I script this to a new query window and clean it up to make it presentable you can see all the steps taken in the code window that follows: (the notations are my own. SQL is nice, but not that nice).
USE [master]
--Place the database into Single_User mode:
ALTER DATABASE [SQL_Cruise] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--Perform a tail of the log backup just in case:
BACKUP LOG [SQL_Cruise]
TO DISK = N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\Backup\SQL_Cruise_LogBackup_2016-08-30_20-37-27.bak'
WITH NOFORMAT, NOINIT, NAME = N'SQL_Cruise_LogBackup_2016-08-30_20-37-27', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
--Restore the first, full backup:
RESTORE DATABASE [SQL_Cruise]
FROM DISK = N'C:\temp\SQL_Cruise_FULL.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
--Restore the latest differential backup possible to meet our goal:
RESTORE DATABASE [SQL_Cruise]
FROM DISK = N'C:\temp\SQL_Cruise_DIFF_3.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
--Restore the complete transaction log backups in order:
RESTORE LOG [SQL_Cruise]
FROM DISK = N'C:\temp\SQL_Cruise_log_03.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [SQL_Cruise]
FROM DISK = N'C:\temp\SQL_Cruise_log_04.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
--Restore the last transaction log backup that includes the point in time and recover:
RESTORE LOG [SQL_Cruise]
FROM DISK = N'C:\temp\SQL_Cruise_log_05.trn'
WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 5,
STOPAT = N'2016-08-30T20:26:00'
--Bring the database out of single_user mode:
ALTER DATABASE [SQL_Cruise] SET MULTI_USER
GO
Note that there are two tape-dependent terms used within the generated code: NOUNLOAD and NOREWIND. They are ignored in non-tape operations but still are generated by default. They can be ignored. NOSKIP, as you’ll see in the tail of the log backup, controls whether a backup operation checks the expiration of a backup set on media before overwriting. Here it is also added by default and can be ignored.
As one can see from the code the database is going to be placed into single user mode – severing all sessions connecting to the database and rolling back their uncommitted transactions in the process. This then allow a final transaction log backup to take place preserving the state of the database prior to overwriting it as part of the restore process. After the tail of the log backup is done we step through the full, differential, and log restores each leaving the database in a norecovery state so the next restore operation can take place. Finally we get to the transaction log backup that hosts the point in time we want to recover to. Utilizing the STOPAT command we can set the process to go no further in it’s transaction replay than that point in time. You’ll note we also restore WITH RECOVERY at this point so no more restores can occur. Finally we bring the database out of single user mode to let all users get back into the database.
I mentioned that should to want to move the database to a new location you can employ the WITH MOVE command by changing the restore path in the Files page of the restore dialog. Doing so only affects the initial full backup restoration because all subsequent restores reference the database without need to set or create the file structure. I've isolated the code below for your review:
RESTORE DATABASE [SQL_Cruise] FROM DISK = N'C:\temp\SQL_Cruise_FULL.bak'
WITH FILE = 1,
MOVE N'SQL_Cruise'
TO N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL_Cruise_NEW.mdf',
MOVE N'SQL_Cruise_log'
TO N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL_Cruise_NEW_log.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
STATS
Let me address the STATS = 5 syntax This is the value that denotes what percent of the restore is messaged back to the console in SQL Server Management Studio when you execute the restore process. The default is 5 which means you'll be messaged every 5% the restore completes. I find this verbose and depending on the size of the database tend to set this value anywhere between 10 and 50.
FILE Setting
You may notice the FILE = 1 setting for each restore. This is the most common value you encounter. Think of the file number as the position of multiple backups sent to the backup file. It’s possible to back up more than once to the same file without overwriting it. Each backup sent to the same file is positioned subsequent to the last and its file number is incremented by 1. Since there is only one backup per file the file number in this example is 1.
Simple Transact-SQL Backup Commands
Just as I provided in the backup article this is where I provide you with some simple, templated, commands that cover the theory we just discussed. If you’re not familiar with templates in SQL Server Management Studio check out these two articles:
Introduction to SQL Server Management Studio Templates
Deeper int the SQL Server Management Studio Template Explorer
Tail of the Log Backup
--Place the database into Single_User mode:
ALTER DATABASE [] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--Perform a tail of the log backup just in case:
BACKUP LOG []
TO DISK = N''
WITH NOFORMAT, NOINIT, NAME = N'', STATS =
Restore Full or Differential Backup To Same Location (same syntax)
RESTORE DATABASE []
FROM DISK = N''
WITH FILE = , , , STATS =
Restore Full Backup to New Location
RESTORE DATABASE []
FROM DISK = N''
WITH FILE = ,
MOVE N''
TO N'',
MOVE N''
TO N'',
, , STATS =
Note that you may have multiple data files to restore. If so you can easily modify this code to add more data files.
Restore Transaction Log
RESTORE LOG []
FROM DISK = N''
WITH FILE = , , , STATS =
Restore Transaction Log To Point in Time
RESTORE LOG []
FROM DISK = N''
WITH FILE = , , , STATS =
STOPAT = N''
Conclusion
Backups are important to the recovery process but without the ability to recover the backups they're worthless. You'll hear it said over and over: test your backups. By using the code snippets above you can easily do so. The last thing you want to be caught explaining to your leadership team is that you were able to take backups, but they would not restore due to some undiagnosed reason. It's time well spent.
