Home Print  
Restore MS SQL Server

backs up SQL server by creating a dump file using the sql-server internal commands. supports full backup, differential backup and transaction log backups. Restoring sql-server is a two step process,
1. Restore the full/differential/transaction log dump files and
2. Use the SQL Server Enterprise Manager and restore the sql-server database from the backup dump files.

Step 1: Restore the SQL-Server backup dumps.

From the "Restore Data" UI page in , you have to restore the sql-server backup dumps from the backup server to the local machine first. You can learn more about that in "Restore Data" section of the help documentation.

The following describes how the backup dump is stored in the backup server and how you have to choose the appropriate dump files for restore.

The sql-server backup dump are stored in the backup server with the following directory and file naming convention.

Full Backup Directory Name: A full backup will be stored under a directory ID_FULL_DDMMYYYY_H_M_S, where

  1. ID is the unique id generated by sql-server for the backup. The ID for a current backup will always be greater than the ID for a previous backup. So the ID can be used to identify the sequence in which the backups were done. This is especially useful while restoring database transaction logs as they have to be restored in sequence on a database

  2. FULL is meant to identify that this is a full backup directory.

  3. DDMMYY_H_M_S is the Day, Month, Year, Hour, Minute and Seconds when the backup was done.

Full Backup File Name: The full backup file dump will be under the full backup directory. The file name for a full backup dump file will be ID_DBNAME_DDMMYYYY_H_M_S_FULL.dump, where

  1. DBNAME is the name of the database that is being backed up.

Differential Backup Directory Name: All Differential backups are stored under a directory ID_DIFFERENTIAL_DDMMYYYY_H_M_S. Note that for every full backup there can be multiple differential backup directories.

Differential Backup File Name: The differential backup file dump will be under the corresponding differential backup directory. The file name for a differential backup dump file will be ID_DBNAME_DDMMYYYY_H_M_S_DIFFERENTIAL.dump.

Transaction Log File Name: All transaction logs will be either stored under a FULL backup directory or a DIFFERENTIAL backup directory depending upon whether the transaction log is being done after a full backup or a differential backup. The file name for a transaction log dump file will be ID_DBNAME_DDMMYYYY_H_M_S_LOG.dump.

While restoring these sql-server backup dumps, you need to be aware of the following:

  1. You should restore the full backup dump file as differential backups and transaction logs have to be always applied on top a full backup.

  2. You can choose any one differential backup (or none at all) for restore depending upon your requirement.

  3. For transaction logs you should restore all the transaction logs under the chosen differential backup in (b) above. OR, you need to restore all transaction logs under the full backup if no differential backup was selected in (b) above.

Step 2: Restore the SQL Server Database using SQL Server Enterprise Manager

  1. Open the SQL Server Enterprise manager from the Windows "System->Programs->SQL Server->Enterprise Manager".

  2. Create a database with the same name as the original database. If the original database already exists then you have to delete the database or move the tables to another database. Otherwise you will have to create the database with a different name than the original database name.

  3. After creating the database, right click that node. And in the popup menu, choose "All Tasks->Restore Database" menu item.

  4. In the "Restore Database" dialog, choose "From Device" and click on the "Select Device" button.

  5. In the "Choose Restore Devices" dialog, add the database dump file. Note that for full backup and differential backup, you have to just add the full backup or the differential backup dump file alone and for transaction logs you can add all the transaction logs except the last transaction log together (but in sequence based on the unique ID in the file name). Also you will have to first restore the full backup, then the differential backup on top of it and then restore the transaction logs under the differential backup.

  6. In the General Tab, choose the appropriate option from the available options :

    1. Database Complete: If you are restoring a full backup, then choose this option.
      Database Differential: If you are restoring a differential backup, then choose this option.
      Transaction Log: If you are restoring transaction logs, then choose this option.

    In the Options Tab, choose the following options:

      Select "Force Restore over existing database".
      Select "Leave database read-only and able to restore additional transaction logs".

  7. While restoring the last transaction log, you need to follow step 3 to step 5 again and in the options tab in Step 5, you have to select the "Leave Database operational. No additional transaction logs can be restored".

Technical support-