The Ins and Outs of Offline Files
Published Mar 23 2019 05:02 AM 631 Views
First published on MSDN on Jun 02, 2007

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;


-- create our test database

IF DATABASEPROPERTY (N'offlinefiletest', 'Version') > 0

DROP DATABASE offlinefiletest;


CREATE DATABASE offlinefiletest ON (

NAME = fgt_mdf,

FILENAME = 'c:\offlinefiletest\offlinefiletest.mdf',



NAME = fgt_log,

FILENAME = 'c:\offlinefiletest\offlinefiletest.ldf',

SIZE = 1MB);


-- Add a filegroup with a single file

ALTER DATABASE offlinefiletest ADD FILEGROUP offlinefg;


ALTER DATABASE offlinefiletest ADD FILE (

NAME = damagedfile,

FILENAME = 'c:\offlinefiletest\damagedfile.ndf',

SIZE = 512KB) TO FILEGROUP offlinefg;


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'



BACKUP LOG offlinefiletest TO

DISK = 'c:\offlinefiletest\offlinefiletest_log.bak'



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);


-- 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';


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);


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';


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'



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'



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.


Version history
Last update:
‎Mar 23 2019 05:02 AM
Updated by: