First blog post of the year from TechEd! Well, Kimberly and I arrived a day early hoping to chill out in the sun by the pool but contrary to all expectations the weather sucks today – it’s actually much better in Seattle. Instead we're hibernating in our room blogging and having a Blokus re-match – last time we were in Orlando in March she beat me soundly so I need to get my revenge :-)
Over the next few posts I want to touch on some of the issues that have been causing confusion on the MSDN Disaster Recovery forum . First up is offline files.
One of the cool things you can do in SQL Server 2005 is set a particular file to be offline and then restore just that file from backups. The rest of the database stays online as long as the file isn’t part of the primary filegroup or a transaction log file. This allows you to restore a damaged database file without having to take the whole database offline.
You have to be careful though, depending on what recovery mode the database is in:
- If you’re using simple recovery mode, the file in question has to be part of a read-only filegroup and you have to have a backup of the file that was taken after the filegroup was made read-only.
- If you’re using full or bulk-logged recovery mode, you have to have a backup of the file and a complete transaction log backup chain.
The main point to emphasize here is that you have to have a backup! Once a file has been set offline, the ONLY way to bring it back online is to restore it from backups. I’ve worked up a simple script that illustrates how to do this.
Firstly I create a database offlinefiletest and add a filegroup with a single file called damagedfile .
USE master;
GO
-- create our test database
IF DATABASEPROPERTY (N'offlinefiletest', 'Version') > 0
DROP DATABASE offlinefiletest;
GO
CREATE DATABASE offlinefiletest ON (
NAME = fgt_mdf,
FILENAME = 'c:\offlinefiletest\offlinefiletest.mdf',
SIZE = 2MB)
LOG ON (
NAME = fgt_log,
FILENAME = 'c:\offlinefiletest\offlinefiletest.ldf',
SIZE = 1MB);
GO
-- Add a filegroup with a single file
ALTER DATABASE offlinefiletest ADD FILEGROUP offlinefg;
GO
ALTER DATABASE offlinefiletest ADD FILE (
NAME = damagedfile,
FILENAME = 'c:\offlinefiletest\damagedfile.ndf',
SIZE = 512KB) TO FILEGROUP offlinefg;
GO
Next I take a full database backup and a transaction log backup. Note that I’m using the CHECKSUM options – see my previous blog post here for more info on these.
BACKUP DATABASE offlinefiletest TO
DISK = 'c:\offlinefiletest\offlinefiletest.bak'
WITH INIT, CHECKSUM;
GO
BACKUP LOG offlinefiletest TO
DISK = 'c:\offlinefiletest\offlinefiletest_log.bak'
WITH INIT, CHECKSUM;
GO
Now I set the damagedfile offline and check the sys.database_files system catalog view to ensure the file has been taken offline.
ALTER DATABASE offlinefiletest MODIFY FILE (NAME = damagedfile, OFFLINE);
GO
-- Check the file state in the system catalog
SELECT file_id,
CONVERT (CHAR (15), RTRIM (name)) AS name,
CONVERT (CHAR (15), RTRIM (state_desc)) AS state
FROM offlinefiletest.sys.database_files WHERE name = 'damagedfile';
GO
And we get back:
file_id name state
----------- --------------- ---------------
3 damagedfile OFFLINE
There are a bunch of states that a file can be in – the full list is in the Books Online entry for sys.database_files . Ok – so our file is offline. Let’s try setting it online again.
ALTER DATABASE offlinefiletest MODIFY FILE (NAME = offlinefile, ONLINE);
GO
And we get back:
Msg 155, Level 15, State 1, Line 4
'ONLINE' is not a recognized CREATE/ALTER DATABASE option.
That’s because there’s no T-SQL syntax to explicitly set a file online – you HAVE to restore it from a backup. Let’s see what our backup contains using:
RESTORE FILELISTONLY FROM DISK = 'c:\offlinefiletest\offlinefiletest.bak';
GO
It has all three files. We’ll start the restore process by specifying which file we want to restore and also giving the NORECOVERY option – this allows us to restore subsequent log backups too.
RESTORE DATABASE offlinefiletest FILE = 'damagedfile'
FROM DISK = 'c:\offlinefiletest\offlinefiletest.bak'
WITH NORECOVERY;
GO
Now we’ve started the restore process, let’s check the state of the file again using the same sys.database_files query as above. We get the following results:
file_id name state
----------- --------------- ---------------
3 damagedfile RESTORING
The file is now listed as RESTORING. We complete the restore process by restoring all the log backups in the backup chain (in our case only one):
RESTORE LOG offlinefiletest FROM DISK = 'c:\offlinefiletest\offlinefiletest_log.bak'
WITH RECOVERY;
GO
And now if we check the sys.database_files system catalog view again we should see that the file is online again:
file_id name state
----------- --------------- ---------------
3 damagedfile ONLINE
It is - success!!
Bottom line here – this is a powerful feature that allows for much better availability in the event of a disaster BUT you must be setup to use it. As with all features, especially around disaster recovery, make sure you know the requirements and limitations for successful use. I've attached the complete script for you to play with.
Ok – now there’s a little bit of blue sky – chill time! Hope to see a bunch of you during the week – see here for a list of what we’re up to session-wise.
Microsoft
