Online restore feature in SQL Server 2005

by By Greg Robidoux, Edgewood Solutions

SQL Server 2005's online restore feature eliminates the need to have exclusive access to the database when restoring a backup. Expert Greg Robidoux explains the restore procedure, which requires the Enterprise or Developer Edition for implementation to take place.

Microsoft has added its new online restore option to SQL Server 2005, which allows you to restore a backup while the database is still online. In the past you needed exclusive access to the database in order to restore, but that's not the case with SQL Server 2005. Before you get too excited about this new feature, there are a couple of things to note: (1) This option only exists in the Enterprise and Developer Editions and (2) You...

By submitting your personal information, you agree to receive emails regarding relevant products and special offers from TechTarget and its partners. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

can only restore at the filegroup level.

So what does that mean? Initially, you will have to invest in the more expensive version of SQL Server to be able to use this feature in a production environment. But, more importantly, to even take advantage of this product, your database must be configured in a certain way.

Database setup

A few things need to occur before you can take advantage of this new feature in SQL Server 2005.

Your database must be using multiple filegroups. That's because you are restoring a file or an entire filegroup, and when the restore occurs, this filegroup is offline and not accessible.

The primary filegroup, which holds your system tables, needs to remain online.

The filegroups can be read-only or read-write filegroups.

For read-write filegroups, you must use the full or bulk-logged recovery model so transactions can be restored.

For read-only filegroups, you can use the simple recovery model.

Since some of the filegroups may be online and others offline, carefully plan how your data is laid out and how your applications access your tables.

How to restore

From a restore perspective, it is pretty easy to issue an online restore procedure. Basically, you perform the same process as you normally go through when doing a restore, but you also specify a file or filegroup you want to restore.

-- back up tail portion of the log. This is also using a new SQL Server 2005 feature "COPY_ONLY," which does not disrupt the sequence of backups BACKUP LOG adb TO copy_only_log_backup WITH COPY_ONLY

-- restore transaction logs using the RECOVERY option for the last restore RESTORE LOG adb FROM log_backup WITH NORECOVERY RESTORE LOG adb FROM copy_only_log_backup WITH RECOVERY

For a read-only filegroup, the process is much simpler. That's because you only need to restore the file and do not need to worry about the transaction logs since there are no transactions updating your read-only filegroup. -- restore file RESTORE DATABASE adb FILE='a1' FROM backup WITH RECOVERY

Advantages

The biggest advantage of doing online restores is that you have the ability to keep your database up while you restore a portion of the database that may be corrupt. Doing a partial restore is also much faster than restoring the entire database. For very large database environments, it's critical to cut down on the overhead of maintaining your databases. Also, if you are using read-only filegroups, the partial restore feature is very helpful because the restore process is quite simple and straightforward, and there is no impact to the rest of the database.

Disadvantages

The biggest disadvantage to this process is having to set up your database to take advantage of filegroup restores. Usually if you have a very large database, you've already implemented multiple filegroups, so you've taken care of this process. In smaller database installations, the database is set up with only the primary filegroup, so planning and reconfiguration would need to take place.

In addition to the initial setup, see what portions of your database you can take offline to do an online restore and still have the application function. This part is probably the hardest thing to figure out. With read-only filegroups, which may contain archived data, this may be a much simpler task. However, with read-write filegroups, it may not be very easy to do -- or even possible.

Summary

Based on the information above, you can probably tell whether online restores make sense for you. The biggest deciding factor is whether you are using the Enterprise Edition of SQL Server or not. After that, establish whether you will be able to take advantage of online restores if you break down your database into multiple filegroups. For additional information about online restores, take a look at SQL Server 2005 Books Online or download a copy of the Developer Edition and try this new feature to see if it makes sense for your environment.

Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

E-Handbook

0 comments

E-Mail

Username / Password

Password

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy