storePOINT Disaster Prevention

home // solutions // storePoint // knowledge base

Downloads Technology Whitepaper Feature Matrix

How do I backup SQL Server databases?

Overview

If you are using the Simple recovery model, recovery is as simple as downloading the *.mdf and *.ldf files and attaching them to the SQL Server. Otherwise you will download the database dump files and use the tool you used to generate the dump file (such as SQL Enterprise Manager) to restore your data.

Instructions

The procedure depends on whether you were backing up your database using the simple recovery model or another recovery model.

Simple recovery model:

Recovery of an entire database:

1. Start the File Manager (Go to the Control Panel page in the Backup Manager. Click Manager or Restore Files.)
2. Use the wizard to select the database files (*.mdf and *.ldf) you want to restore.
3. On the Restore Data: Options screen, choose to restore the files to a folder that is different from the folder containing your existing database files. Please choose a location that is on a local hard disk (SQL Server will not attach databases on network storage)
4. Be sure not to overwrite any existing files when performing the restore.
5. Follow the steps in the wizard to finish the restore process.
6. Login as the database administrator and open a SQL command window.
7. For each database your need to restore, perform the following SQL commands:
   
      use master
      go
      sp_detach_db 'databasename'
      go
      sp_attach_db 'databasename' 'path to mdf file' 'path to ldf file'
      go
   
8. After you have verified all data was restored successfully, you may delete the old mdf and ldf files.

Partial recovery:

1. Restore the mdf and ldf files following steps 1 - 4 from above.
2. Login as the database administrator and open a SQL command window.
3. For each database you need to extract information from, perform the following SQL commands:
   
   use master
   go
   sp_attach_db 'temp_databasename' 'path to mdf file' 'path to ldf file'
   go
   (use SQL commands or other tools to copy data you want to restore from temp_databasename to the live database)
   sp_detach_db 'temp_databasename'
   go
   
4. After you have verified all data was restored successfully, you may delete the downloaded (restored) mdf and ldf files.

Other recovery model:

1. Start the File Manager (Go to the Control Panel page in the Backup Manager. Click Manager or Restore Files.)
2. Follow the steps in the wizard to restore your database dump files.
3. Use the SQL Enterprise Manager (or your other backup software) to restore your data using the database dump files.

Additional instructions on using the SQL Enterprise Manager to restore data can be found here.