mssqlserver.com: FAQ: If I lose my data file (MDF) in SQL 7 I can't backup the log to recover to a point in time

mssqlserver.com: FAQ: If I lose my data file (MDF) in SQL 7 I can't backup the log to recover to a point in time - why not?

If I lose my data file (MDF) in SQL 7 I can't backup the log to recover to a point in time - why not?

A small oversight on MS's part - which will be fixed in the next major (not service-pack) release of SQL.

Under 6.5 you would do BACKUP LOG <dbname> TO <device> WITH NO_TRUNCATE.

Under SQL 7 this gets you an error
Server: Msg 3446, Level 16, State 1
Primary file not available for database <xx>

This is because the metadata that tells SQL where the files are for the database are in the primary file - in SQL 6.5 this info was held in system tables in master.

A workaround for this is :-

Use only ONE file in the primary file group (the primary file), and place this file on the same drive (mirrored) as your log files. This gives it the same protection as the log.
Add another file group, with one or more files for data - obviously this goes on different disks to the log. Mark this second file group as the default (Alter database)
Then, when your data file is lost , backup no_truncate will work because the primary file with the metadata in (but no user data) is still available.

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.