Summary
I have a SQL database running, and I need to restore an older version of this DB to the same server for some recovery work. I want to choose a database instance and use the MSSQL New Location" and "MSSQL New Location Logs" options in the Define Destination Options screen to restore the older DB to an alternate location where I can work on it later. When I kick off the restore, my original production DB is removed from the original disk. "
Symptoms
Original SQL database is deleted when other instance is restored to an alternate location.
Resolution
It is the expected behavior. With some planning from the SQL DBA, accidental removal of the original database can be avoided.
DPX interacts directly with SQL instances for backup and restore operations. With restore operations, MS-SQL is sent various API commands that inform it that a database is going to be restored.
DPX takes care of informing SQL of restore, providing data, and providing SQL information that restore is complete.
Microsoft SQL takes care of preparing the SQL instance to accept a restored database (including any cleanup that might be necessary), waiting for the backup program to provide the data, and then bringing the restored database online when the data transfer is complete.
In a standard restore operation, the original database files are overwritten after the MS-SQL instance signals that it is ready for the restore operation.
When attempting to restore using "New Location", the MS-SQL instance first attempts to move the existing database files to the new location, and then it accepts the new data to overwrite the old files.
Here are some suggestions for File level and Snapvault SQL restore if you need to retain your existing database:
Restore from File Level backup:
The safest way to retain your current data and restore an older instance is to detach the existing database and restore your old database to an alternate disk/directory location through the 'Define Destination Options' screen. Once the restore operation is completed and your old database is online, you can rename it and attach the other version.
Restore from Block backup:
If you backed up your SQL instance using OSSV, then you have three options:
- You can detach your existing instance, restore your old data to an alternate location through the Define Destination Options screen, and then attach your existing data again.
- Since DPX communicates with MS SQL server and does its SQL prepare prior to disk snapshot, you can bring up Snapvault restore, drill down into the drive where your SQL data and log files are located, restore these files to some other alternate location, and then attach them to your SQL instance as needed.
- You can IA map the drive(s) where your SQL data and log files exist, and attach them directly to your SQL instance. Using an iSCSI attached LUN will be somewhat slower than accessing a local copy on your hard drive, however you do not need to sacrifice disk space and time to transfer these files locally.
Raw, Image, and NDMP backup:
These backup types do not include a component that accounts for online SQL activity. Although you may be able to find the actual SQL data and log files, they may have been backed up in an inconsistent state; you may be able to recover data from these files, however this is not a supported use of DPX.
If you used one of these backup methods for cold backup (for example, SQL was taken down or the desired database files were detached from the running SQL instance), then you can restore the necessary data and log files to an alternate location and attach them to your running SQL instance at a later time.