Piecemeal (aka Partial) filegroup restores from litespeed (sorta)

Okay, I wanted to write this down while I'm thinking about it.

I use filegroups and do filegroup backups. Extensively. Gives you more control and power, since you can backup each one individually, put it in its own location, give it its own schedule, etc, etc. Litespeed helps with this, obviously, since it'll shrink each file.

The downside is that restoring with Litespeed is an all-or-nothing ordeal. You can't restore just the primary, or just one of the filegroups, or just a table (no, object-level restore doesn't work on filegroup backups). Fortunately, SQL 2005 has a new "PARTIAL" option that allows you to restore just the primary filegroup, and then restore others in addition, so I'm using that.

I've already requested this feature for Litespeed, as well as the PARTIAL option, but here's what I had to do to restore some tables from my filegroup backup

0. Take filegroup backups. Take a TLOG backup if needed.

1. Decompress the files I need. Litespeed _does_ offer an "extractor" utility. This will turn your Litespeed BKP into an uncompressed SQL backup. One note: it creates 7 files, and the total will be the same as the uncompressed backup. So make sure you have room. I barely did - thank god for filegroups (or maybe not, since the whole purpose of this post is working around it). So, extract the primary and whichever filegroup you need.
Here's the code to restore a file:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

On my system it converts to native at about .75gb per minute (uncompressed size). It's extremely disk-bound. You need to extract the log file too, if you're compressing it (you might not be! Check and see....).


2. Restore the primary filegroup WITH PARTIAL. Make sure to keep your Primary filegroup small. Mine isn't - I need to fix that. Here's the command I used:
RESTORE DATABASE main
FILE = 'main_system_01'
FROM DISK = 'K:\primary.bak0',
DISK = 'K:\primary.bak1',
DISK = 'K:\primary.bak2',
DISK = 'K:\primary.bak3',
DISK = 'K:\primary.bak4',
DISK = 'K:\primary.bak5',
DISK = 'K:\primary.bak6'
WITH partial, move 'main_system_01' TO 'E:\MSSQL_Data\main.mdf' ,
move 'main_log_01' to 'F:\MSSQL_Log\main_log.ldf', --without this, it will fail
FILE = 1, norecovery, nounload, stats = 10


3. Wait a couple hours for the primary to restore. (See? This is why you have a small primary filegroup.)


4. Restore the additional filegroup. You do _not_ use the PARTIAL keyword here. Why? Don't know - using PARTIAL caused it to fail, though my lab notes say to use it. My bet is that you don't need PARTIAL if you're restoring the only filegroup within a backup.
RESTORE DATABASE Main
FILE = 'Main_Secondary_01'
FROM DISK = 'K:\FG_Main_Secondary.bak0',
DISK = 'K:\FG_Main_Secondary.bak1',
DISK = 'K:\FG_Main_Secondary.bak2',
DISK = 'K:\FG_Main_Secondary.bak3',
DISK = 'K:\FG_Main_Secondary.bak4',
DISK = 'K:\FG_Main_Secondary.bak5',
DISK = 'K:\FG_Main_Secondary.bak6'
WITH --partial,
move 'Main_Secondary_01' TO 'E:\MSSQL_Data\Secondary.ndf' ,
FILE = 1, norecovery, nounload, stats = 10


5. Restore the log. I'd say to use use STOPAT so you don't have to keep restoring TLOGs, but I can't find anywhere how to get that to work with Litespeed's restore - I tried adding it to the @with, but then it just failed. Odd. Also, if I extract and restore a log, I get error messages about having to roll the LSN forward to a certain point, whereas I don't get that when running the below command.

EXEC master.dbo.xp_restore_log
@database = 'Main'
, @filename = '\\myserver\SQL_Backups\Main\Main_tlog_200802041030.TRN'
, @filenumber = 1
,@WITH ='RECOVERY'


6. Keep running restores until you get current. The way I did it, I kept running TLOG restores until I got an error message telling me the database is offline. It does this once you hit the last log file prior to the last database backup finishing.

7. Bring database ONLINE. I do it via the GUI.

8. Bring database from SINGLE-USER to MULTI-USER.

[Tuning] Getting file disk usage

Short and simple. Uses the DMV to tell you how much IO each file is using. What you think may be the case is not necessarily the case. And remember that backups count. I'd recommend doing 2+ samples during the day, and comparing. That'll help remove the backups from the end totals. Sample_MS is the number of milliseconds the computer has been up.

SELECT 
  master_files.NAME, 
  master_files.type_desc,
  master_files.physical_name, 
  vfs.sample_ms,
  vfs.num_of_bytes_written, 
  num_of_bytes_read, 
  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB
FROM sys.dm_io_virtual_file_stats(null, null) vfs
INNER JOIN MASTER.sys.master_files 
 ON vfs.[file_id] = master_files.[file_id] 
 AND vfs.database_id = master_files.database_id
ORDER BY (num_of_bytes_read + num_of_bytes_written) desc