A. 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 :-
1. 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.
2. 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)
3. 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.
Advertisement
