You are currently offline, waiting for your internet to reconnect

INF: Copying SQL Database Files for Backup/Restore Purposes

This article was previously published under Q46424
This article has been archived. It is offered "as is" and will no longer be updated.
The following information describes different methods that can be usedto 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 aslong as the database is shut down when the copy is made. Restoringfrom such a backup is easier than using BLDMASTR and LOAD DATABASE,especially if the master database is damaged. (The procedure forrestoring a damaged master database is complex if database deviceswere added or deleted since the dump was taken or if databases werecreated, dropped, or altered since the dump.) On the other hand, acopy of MASTER.DAT could simply be restored and no special actionwould 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 updated.
  • 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 seconditems; however, a good data compression utility might do better thanDUMP/LOAD on the third item.

If MASTER.DAT is copied while SQL Server is still running, manyupdated pages might not yet have been written to disk, resulting in aninconsistent image of the database. This is why DUMP/LOAD goes throughSQL Server rather than accessing the disk files directly.
Dumping loading

Article ID: 46424 - Last Review: 02/27/2014 18:29:35 - Revision: 3.2

Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbother KB46424