Category Archives: Maintenance
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!
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!
MAP Toolkit in Action
I have written in the past about the MAP Toolkit (Microsoft Assessment and Planning) and how it helps with licensing issues including core counts. With that being said, it is time for us to renew our Software Assurance maintenance agreement and this was the original reason that I installed the MAP Toolkit a couple of months ago.
This week I rescanned all of my instances to figure out how many cores we had licensed and for which version. I wanted to get all of my documentation ready to go to our contract manager when I noticed a new server that had 24 Enterprise cores that I had never heard of before. My supervisor had never heard of the box either.
When I logged into the box, sure enough it had SQL Server installed. The server was for a monitoring tool and when the administrators installed the software on the box instead of asking me where they could install the database they found a disk and installed SQL Server themselves. Unlicensed. On the same box as the software. Without telling anyone.
When the contract manager told them how much the 24 cores would cost their department they quickly called me and asked me to kindly move their 13 GB database to one of my other servers. It just happened recently because I scan the network every few months and had never seen this before. Not only did they put us in jeopardy with our licensing, they had no backups setup for the database. When confronted they said yeah that was taking up too much space on the disk.
The lesson here today boys and girls is to scan your instances and look for unknown installations regularly. Carry on and enjoy!
SP_SpaceUsed for Disk Space Monitoring?
Recently, I heard about a couple of developers that were using the stored procedure SP_SpaceUsed to monitor available disk space. They had requested additional disk space because the utility had told them that there was only 4mb of unallocated space. Try not to laugh because these could be YOUR developers.
Last month I blogged about using the procedure to show you the estimated backup size for the database. But these developers were confusing unallocated space, which is actually just space in the database that has not been allocated. For example, when you set a autogrowth interval and your database grows in accordance with the interval you will now have unallocated space until the database takes that free space. Enjoy!
| 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 |
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.
SQL Features Discovery Report
While going through my preparation for the MCSA exam I stumbled upon this gem that I always seem to forget about so I thought I would pass it along to you. For a quick an easy way to determine what is installed on an instance of SQL Server, run the SQL Server Installation Center and select tools. Then select Installed SQL Server features discovery report, as shown in Figure 1. This will produce a nice report as shown in Figure 2.
Enjoy!
Figure 1 – SQL Server Installation Center
Figure 2 – Setup Discovery Report
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
