Backup workflow
- Issue the following ALTER DATABASE command to prepare the database for snapshot where SnapTest is your database name: ALTER DATABASE SnapTest SET SUSPEND FOR SNAPSHOT_BACKUP=ON
- Create the snapshot on Unity XT using the Unisphere UI or Unisphere CLI.
- Issue a BACKUP DATABASE command using the option WITH METADATA_ONLY. This will create an entry in the backup metadata file for this backup. The MEDIANAME and MEDIADESCRIPTION fields are used to store information about the location of the snapshot: BACKUP DATABASE SnapTest TO DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH METADATA_ONLY,NOFORMAT,MEDIANAME='Unity XT U8',MEDIADESCRIPTION='consistency group: SQLDemo',NAME='SnapTest backup',DESCRIPTION='desc'
Note: Although the BACKUP DATABASE command is used here, it functions differently. It only writes a small amount of metadata used to restore the database. This metadata file combined with the related Unity XT snapshot is required for a database recovery. Without the correct metadata file, the database files can be attached for recovery but point in time recovery using differential or T-LOG backups will not be possible. Therefore, descriptive fields are used in the examples to document how the backup metadata file and its Unity snapshot are related and where to find them.
Restore workflow when replacing existing database
- Drop the existing database.
- Offline the disks used by the existing database with Windows Disk Management or PowerShell. The following command will offline a Windows disk with the drive letter “E”: Set-Disk (Get-Partition -DriveLetter E | Get-Disk | Select number -ExpandProperty number) -isOffline $true
- Restore the Unity XT snapshot either by using the Unisphere UI Manager to select the proper volume group and performing “Restore” or the UEMCLI.
- Online the disks with Windows Disk Management or PowerShell. The following command will online all offline disks: Get-Disk | Where-Object IsOffline -Eq $True | Select Number | Set-Disk -isOffline $False
- Issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The examples below recover the database named SnapTest using a backup metadata file “c:\temp\SnapTest_SQLBackupFull.bkm”.
- When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1,METADATA_ONLY,RECOVERY
NOTE: WITH FILE=1 is used to reference a file in a backup set. T-SQL snapshot backup is a good use for backup sets since the backup metadata files are small.
- When performing a point-in-time recovery and applying differential, transaction log backups, or both use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1,METADATA_ONLY,NORECOVERY
- For point in time recoveries, apply differential or transaction log backups per the standard recovery process.
Restore workflow when restoring as a copy
- Create a copy of the database volumes from a Unity XT snapshot using the Unisphere UI or UEMCLI.
- If using the Unisphere UI:
- Select the proper Unity XT Consistency Group and under “More Actions” choose “Clone”. This will launch a wizard.
- Select the snapshot.
- Provide a name for the thin clone.
- Verify the storage.
- Configure Access (map volumes to the host).
- Configure additional snapshots or replication if needed.
- If using the UEMCLI, run the proper commands to perform the following:
- Create a clone of the Consistency Group.
- Mount the newly created volumes to the host.
- Create additional snapshots schedule or replication if wanted.
- Online the disks with Windows Disk Management or PowerShell and assign new drive letters or mount points.
- Issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The examples below recover the database named SnapTest using a backup metadata file “'c:\temp\SnapTest_SQLBackupFull.bkm” and specify the database files should reside on drive letters “P”, “Q” and “R”.
- When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to 'p:\sql\SnapTest1.mdf',MOVE 'SnapTest2' to 'q:\sql\SnapTest2.mdf',MOVE 'SnapTest_log' to 'r:\sql\SnapTest_log.ldf', METADATA_ONLY,RECOVERY
- When performing a point-in-time recovery and applying differential, transaction log backups, or both use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = ''c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to 'p:\sql\SnapTest1.mdf',MOVE 'SnapTest2' to 'q:\sql\SnapTest2.mdf',MOVE 'SnapTest_log' to 'r:\sql\SnapTest_log.ldf',METADATA_ONLY,NORECOVERY
- For point in time recoveries, apply differential or transaction log backups per the standard recovery process.