Restoring Microsoft SQL Server user databases

To restore a lost or damaged database

1. If the transaction log of the damaged or inaccessible user database is on an undamaged device, make a backup of the transactions before proceeding. (This lets you preserve up to the minute transactions that are not included on the backup tape.)

   You may use either a DUMP TRANSACTION statement on the SQL server or use a Yosemite Server Backup Incremental backup job to back up only the transactions logs.

2. If you are restoring the database because the data in the database is no longer needed or is incorrect, skip to step 3. The following instructions are for recreating database devices and the database which had existed previously.

   During the restore processes, Yosemite Server Backup will recreate the database and all segments exactly as they existed when the backup was performed.

   To do this, Yosemite Server Backup first determines if the database exists. If the database does exist, Yosemite Server Backup will use the database as is without any further processing or changes.

3. If the database does not exist, Yosemite Server Backup next identifies the database devices on which the database was originally located. If the appropriate database device already exists, Yosemite Server Backup will use that device as is without further processing.

   If the database device does not exist, Yosemite Server Backup recreates the database device at its original location and with its original size. After all the database devices are created, Yosemite Server Backup then creates the database with all the original options at the original locations.

   	TIP: This method makes disaster recovery simple. The user simply create a restore job and allows Yosemite Server Backup to recreate whatever is needed in order to successfully restore the database.

   	NOTE: If a disk drive fails and is not replaced, Yosemite Server Backup will be unable to restore your database because it will be unable to recreate a database device. For example, if a segment of your database resides on a database named ‘DATA’ at D:\MSSQL\DATA\DATA.DAT, if D: is lost and not replaced, when Yosemite Server Backup attempts to recreate the database device, it will fail, since D: no longer exists.

   To avoid this problem, manually recreate the database device at some other valid location. It must be at least as large as the original database device since Yosemite Server Backup will attempt to create a database segment on it the same size as the original database.

   An alternative method is to manually create the entire database itself. Thus, when Yosemite Server Backup attempts to restore the database, since the database already exists, it will use that preexisting database. This allows you to restore a database in a new location, since Yosemite Server Backup does not check to see if it is the original device before restoring the database, because the database already exists.

   	NOTE: An alternative method is to manually create the entire database itself. Thus, when Yosemite Server Backup attempts to restore the database, since the database already exists, it will use that preexisting database. This allows you to restore a database in a new location, since Yosemite Server Backup does not check to see if it is the original device before restoring the database, because the database already exists.

   Using Yosemite Server Backup, create a restore job and run the job to restore the database. You must start with a full backup version of the database to restore which was created using a Full backup job.

4. Create additional restore jobs to restore each transaction log backed up after the full database you restored. You must create and run a separate restore job for each transaction log.

   For example, if you ran a full backup on Friday and incremental jobs (that is, jobs that backed up only the transaction logs) on the following Monday and Tuesday, you must first restore the database using Friday’s version of the database. Next, create a run and restore job that restores Monday’s version (Monday’s transaction log). Finally, create and run a job that restores Tuesday’s version (Tuesday’s transaction log).

   In the last incremental restore job, click the Advanced Options button and select the Finalize recovery of MS SQL databases check box. If you do not select this check box, the database will be offline.