Blog Archives
Online Restores
I was doing some mentoring today with online restores, so I thought I would share it with my blog readers. The most important thing about online restores is that it is only supported in SQL Server 2005 and later versions. In addition, for the bulk-logged recovery model the following conditions must be met in order to complete an online restore:
- All of the log backups must have been taken and completed before you start the online restore.
- If you have bulk changes in the database, the files must be online.
- All of your bulk changes must be backed up before starting the online restore process.
In addition, I have seen it recommended that you switch your bulk-logged recovery model to the full model before starting the online process and then switching it back afterward for smoother recovery. Enjoy!
Looking for Humorous Backup/Recovery Stories
While preparing my newest presentation, I thought the best way to teach backup and recovery is through humorous stories revolving around horrific backup and/or recovery incidents. I have a couple, but I thought I would open this up to the SQL community and see if there were some really outrageous ones out there. I can cite you (or not if you choose to remain anonymous) in my presentation and tell your story to help teach the world about SQL Server Backup and Recovery. You can simply e-mail me at Ed at SQLGator.com or comment on this post.
Thanks for your help.
Backup and Recovery Documentation
Today I began documenting my backup and recovery strategy for my servers. If you have not documented your strategy, take my advice and do it. This should include the following items:
- Retention schedule
- Recovery models
- Disaster recovery plans
- RPO and RTO or SLAs
- Identify your backup schedule
- Identify your recovery schedule
- Identify offsite storage options and retention
- Identify your compression strategy
- Identify any third party vendor tools
I hope this helps. Enjoy!
HyperBac No More
In case you missed it last week, Red Gate announced the death of the HyperBac tool for backup and recovery. I am not surprised by this move as the features were pretty much the same as the Backup Pro tool also offered by Red Gate. When I setup a test case for the tools in comparison with Backup Pro and native compression, it consistently had the worst compression ratios.
However, I was a little sad about the death of the Virtual Restore piece of HyperBac. This tool had great potential, especially since we just figured out how to script it to autoMAGICally restore all of our backups on a nightly basis. Maybe they will be able to incorporate this piece into the Backup Pro tool. Only time will tell, I guess. Enjoy!
Restoration Recovery State Options
(Editor’s note: I just discovered that this blog did not post on 2013-Feb-06 as it was originally intended)
Yesterday, I was asked what the difference was in some of the restoration options. Thus, I thought this would make a good blog post as it is somewhat confusing in the beginning.
On the options page of the restore database interface in SQL Server Management Studio, there are several options than can be quite frightening to new DBAs. I have tried to explain them here in the order that they appear on the screen.
In the Recovery State options:
- Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY): Choose this option only if you are restoring the log files now (in full or bulk-logged recovery models). This is the default option and allows you to only go back to the last backup file.
- Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY): Pick this option when you want to go to the very latest possible data! Leaves the database in a restoring mode as you restore the backup and then all of the transaction logs generated since the backup up to the point of failure (if possible).
- Leave the database in read-only mode. Undo committed transactions, but save the undo actions in a standby file so that recovery effects can be reversed. (RESTORE WITH STANDBY): The database will be left in read-only mode. At this point you can still apply transactions logs and is generally used when it takes too long to restore the system to a full database and you wish to use the server as a stand by server.
I hope this helps, enjoy!
Need to Predict Backup Sizes?
Someone showed me a neat trick a little while ago that I thought I would pass on. The problem is I wrote down the trick but not who told it to me, so if you are reading this and you think it was you who told me please let me know and I will cite you properly. I apologize for this transgression but I thought the tip was appropriate given my recent posts concerning backup and recovery and I was searching through some old notes looking for something to blog about tonight.
Have you ever added a new database to your server and thought to yourself, hey do I have enough space on my backup drive to cover however many days you are backing up? Here is an easy way to get a general idea for an uncompressed backup size. If you are running compression, then it will obviously be less but this is an estimate.
USE <Database Name>
GO
EXEC sp_spaceused @updateusage = ‘TRUE’
Which for a particular database on one of my servers it returned:
| database_name | database_size | unallocated_space | |
| <Database Name> | 2449.81 MB | 40.45 MB | |
| reserved | data | index_size | unused |
| 2396472 KB | 2386856 KB | 5144 KB | 4472 KB |
The actual uncompressed backup for this database was 2,317,543 KB and the reserved data from the stored procedure was 2,396,472 which is pretty close to the actual backup. Not bad, huh? I hope this helps.
Restoration Options
Today I was asked what the difference was in some of the restoration options. Thus, I thought this would make a good blog post as it is somewhat confusing in the beginning.
On the options page of the restore database interface in SQL Server Management Studio, there are several options than can be quite frightening to new DBAs. I have tried to explain them here in the order that they appear on the screen:
- Overwrite the existing database (WITH REPLACE): This is a scary option as you should always make sure that you REALLY want to overwrite an existing database. Proceed with caution. This option overwrites a safety check that prevents you from overwriting database X with database Y.
- Preserve the replication settings (WITH KEEP_REPLICATION): This option is really only relevant if the database was replicated when the backup was created. I do not have a strong background in replication so I don’t have much to add to this item.
- Prompt before restoring each backup: This essentially will let you pause a restore with a dialog box prompt when moving between media sets. Unless your backup is stored across multiple tapes, this is probably of little use to you. This might be helpful if you would like to break up the restore into smaller pieces, but I am not sure that is practical.
- Restrict access to the restored database (WITH RESTRICTED_USER): This option will make your restored database only available to the members of sysadmin, dbcreator, or db_owner roles. This might be helpful if you need to perform additional steps before making the database available to the users to keep them from accidentally jeopardizing some scripts you need to run post restore.
I hope this helps your understanding. Stay tuned for tomorrow where we discuss the recovery state options. Enjoy!
Point In Time Restores
One of the many skills needed by DBAs today is the ability to restore a database from backup using point in time recovery. This option is only available if you are using the full or bulk-logged recovery model (see my post on recovery models). The default time shown is the ‘most recent possible’ which works in most scenarios.
Why would I need to restore to a point in time? There are many different reasons, one being that you noticed through your monitoring or logs that things went south at a certain point in time. Therefore, we want to restore a minute or two prior to that time. Another scenario that I have seen is that a change is made in an application in production at a certain time. They now want those changes demoted down into a testing environment. In this scenario we would want to make sure our restore includes that point.
How do I do it? In the restore database dialog, you select the ellipsis (…) button to the right of the point in time field below the database name and you will see a date and time option, as shown in Figure 1. Select the remaining restore options as you normally would. Not sure about the rest of the options? See the MSDN directions detailing point in time recovery. Enjoy!
Figure 1 – Point In Time Restore
Red Gate Virtual Restore Scripting (Part 10)
If you have been following along since December, then you know that I have been doing some investigation with the Red Gate Backup and Restore bundle to implement in our system with our use of Ola Hallengren’s backup maintenance scripts. One of our last stumbling blocks has been automating and scripting of the Red Gate Virtual Restore product. We have moved methodically through the list and with the help of Red Gate’s excellent support department resolved each item including this one.
The problem we had been that when we tried to verify the backup through restoration and DBCC CHECKDB on a certain database, that was considerably larger than all of the other databases on this server, we would not have enough space on the drive using our restore script we created last week. That scripting solution is great as long as there is enough space on the drives to attach the database files from backup and then drop them.
The beauty of the Virtual Restore product, in case you did not already know, is that it can restore these files with a much smaller footprint than the actual database files take up in the exiting format. However, the problem is that it is strictly a wizard at this point that will grab a specific backup and run the restore manually and then drop it once it checks the integrity and consistency. This is a great product but this is feature should be included or else it does not help much especially when you have hundreds of databases.
We ran the wizard and then viewed the script:
RESTORE DATABASE [TestDB_Virtual] FROM
DISK=N’I:\TestDB\FULL\SQLPROD40_TestDB_FULL_20130101_213217.sqb’
WITH MOVE N’TestDB_Data’ TO N’F:\VirtualRestore\TestDB_Data_TestDB_Virtual.vmdf’,
MOVE N’TestDB_Index’ TO N’F:\VirtualRestore\TestDB_idx_TestDB_Virtual.vmdf’,
MOVE N’TestDB_Log’ TO N’F:\VirtualRestore\TestDB_Log_TestDB_Virtual.vldf’, NORECOVERY, STATS=1,REPLACE
GORESTORE DATABASE [TestDB_Virtual] WITH RECOVERY, RESTRICTED_USER
GODBCC CHECKDB ([TestDB_Virtual])
GODROP DATABASE [TestDB_Virtual]
GO
This script did not work when we ran it via T-SQL because of a lack of disk space which is the same problem we encountered using the Backup Pro restore script, however it did work with the Red Gate Virtual Restore Wizard. We contacted support to find out why there was a difference on the same box. Basically SQL Server does not know that the HyperBac service is running in the background. The wizard is smart enough to check your disk space and if you do not have enough it temporarily adds an entry into the HyperBac configuration that tricks SQL Server into thinking that you have the right amount of disk space in order to complete the task.
The parameter is “VirtualDiskSize=” where the value is an integer to represent the disk size in megabytes. You add the parameter to the bottom of the to the hyper.conf file found in the C:\Program Files (x86)\Red Gate\HyperBac\Bin folder. At this point then you restart the HyperBac service and the problem is solved! Next I will try to automate this script….stay tuned!
Creating a Red Gate Backup Pro “Virtual Restore” Job Automatically (Part 9)
If you have been playing along at home this month then you know about some of the struggles that I have faced with automating the Red Gate Backup Pro automatically. Well, with the help of Red Gate and some rewriting of their provided script, I now have a script that will work automagically! When I say automagically, I mean that I want the script to run forever without any more interference from me in the same manner that our Ola Hallengren backup scripts work.
If you are not familiar, this tool is excellent for many reasons but one of the reasons that we like it is for the simple fact that if a new database is created, it will be maintained automagically. I do not have to create any maintenance plans or agent jobs each time I add a database to a server. We have several servers that get new small databases all the time and this is a time saver as well as a life saver. Now this scripts will do the same, it will iterate through the database names and restore the latest backup set as a new database with the name ‘_Restored’ tacked on the end of the existing name and place the data and log file in a separate directory to avoid overwriting your existing databases.
Do not worry about failure reporting with the job as Red Gate will send you an error for each database, pass or fail. If you wish to see only the failures then change ‘MAILTO’ to ‘MAILTO_ONERRORONLY’ in the script.
–This script will restore multiple databases where each database has it’s own Full Backup to be restored.
USE masterDECLARE @dbname NVARCHAR(260)
— Add any databases in that you want to exclude
DECLARE cdatabases CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name != ‘tempdb’
AND name != ‘master’
AND name != ‘msdb’
–Set @restorepath to be the path of where your backups are located, in my example this is ‘D:\Backup\’
DECLARE @restorepath VARCHAR(500)
–@filename will need to be modified depending how the backup files are named
DECLARE @filename VARCHAR(500)
DECLARE @restorestring VARCHAR(1000)
–Not needed if running in a SQL job
DECLARE @exitcode INT
DECLARE @sqlerrorcode INTOPEN cdatabases
FETCH next FROM cdatabases INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @restorepath = ‘D:\SQLVMDEV10\’ + @dbname + ‘\Full\’
— @filename will need to be modified depending how the backup files are named
SET @filename = @restorepath + ‘SQLVMDEV10_’ + @dbname + ‘_FULL_*.sqb’
SET @restorestring = ‘-SQL “RESTORE DATABASE [‘ + @dbname + ‘_Restored] FROM DISK = ”’ + @filename
+ ”’ SOURCE = ”’ + @dbname + ”’ LATEST_FULL WITH MAILTO = ”ed@sqlgator.com”, RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO ”G:\VirtualRestore”, MOVE LOGFILES TO ”G:\VirtualRestore”, REPLACE, ORPHAN_CHECK, CHECKDB = ”ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS”, DROPDB” -E’
–If you wish to apply additional backup, remember to change the WITH RECOVERY to WITH NORECOVERY,
EXEC master..Sqlbackup
@restorestring,
@exitcode output,
@sqlerrorcode output–IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
— BEGIN
— RAISERROR (‘SQL Backup job failed with exitcode: %d SQL error code: %d’, 16, 1, @exitcode, @sqlerrorcode)
— END
FETCH next FROM cdatabases INTO @dbname
ENDCLOSE cdatabases
DEALLOCATE cdatabases
This will generate multiple entries in Red Gate Monitor, as mentioned in Part 4 of our journey. In order to prevent these setup a maintenance window on your monitoring tool and run this script only during the maintenance window ensuring that no other jobs run during that time so that you do not miss any important alerts from those maintenance jobs. Here is a link from Red Gate detailing how to set the maintenance windows in Monitor. Enjoy!
SQL Swampland 