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.
A few things need to occur before you can take advantage of this new feature in SQL Server 2005.
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.
