The following information describes different methods that can be used
to make copies of the database files such as master, model, tempdb,
and pubs, which can then be used with Backup or Restore.
Copies of the MASTER.DAT file (or the corresponding .MDF and .LDF files for SQL Server versions 7.0 and later) can be made with any reliable utility as
long as the database is shut down when the copy is made. Restoring
from such a backup is easier than using BLDMASTR and LOAD DATABASE,
especially if the master database is damaged. (The procedure for
restoring a damaged master database is complex if database devices
were added or deleted since the dump was taken or if databases were
created, dropped, or altered since the dump.) On the other hand, a
copy of MASTER.DAT could simply be restored and no special action
would be required.
Listed below are the advantages of using DUMP/LOAD:
- It can be done while the database is up and even while it is being
- It allows backup and/or recovery of individual databases separately.
- Empty space is not dumped.
Just copying MASTER.DAT could never accomplish the first and second
items; however, a good data compression utility might do better than
DUMP/LOAD on the third item.
If MASTER.DAT is copied while SQL Server is still running, many
updated pages might not yet have been written to disk, resulting in an
inconsistent image of the database. This is why DUMP/LOAD goes through
SQL Server rather than accessing the disk files directly.