Tip

Online restore feature in SQL Server 2005

By Greg Robidoux, Edgewood Solutions
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
    • Requires Free Membership to View

    • 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

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot 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
    Sort by: OldestNewest

    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:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.