Dumping a database

  1. Execute dbcc checkdb and dbcc checkalloc to verify the database runs cleanly.

  2. To prevent concurrent updates from open transactions by other processes during dump database, use sp_dboption to place the database in a single- user mode.

  3. Flush statistics to systabstats using sp_flushstats.

  4. Wait for 10 to 30 seconds, depending on the database size and activity.

  5. Run checkpoint against the database to flush updated pages.

  6. Run dump database.