Online restore feature in SQL Server 2005

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 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.

    To restore a read-write filegroup, follow the process below:
    -- restore file
    RESTORE DATABASE adb FILE='a1' FROM backup WITH NORECOVERY

    -- 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.

  • Guide: SQL Server Backup and Recovery Learning Guide
  • Tip: Testing SQL Server restores
  • Tip: Restore basics: How to restore SQL Server using Enterprise Manager

  • This was first published in June 2006

    Dig deeper on Microsoft SQL Server 2005

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close