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 PowerStore. This can be done using PowerStore Manager or PSTCLI. In this example, PSTCLI is used to take a snapshot named SQLBackupFull-1 of a PowerStore Volume Group SQLDemo that contains database data and log volumes: pstcli -d xxx.xxx.xxx.xxx -u username -p password volume_group -name SQLDemo snapshot -name SQLBackupFull-1
- Issue a BACKUP DATABASE command using the option WITH METADATA_ONLY. This creates 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='Dell PowerStore PS-13',MEDIADESCRIPTION='volume group: SQLDemo',NAME='SnapTest backup',DESCRIPTION='desc'
Note: Although the BACKUP DATABASE command is used here, it functions much differently. It only writes a small amount of metadata used to restore the database. This metadata file and the related PowerStore snapshot are required for a database recovery. Without the correct metadata file, the database files can be attached as a method of recovery but point-in-time recovery using differential or T-LOG backups will not be possible. Descriptive fields are therefore used in the examples to document how the backup metadata file and its PowerStore snapshot are related and where to find them.
Restore workflow when replacing existing database
- Drop the existing database.
- Use Windows Disk Management or PowerShell to take offline the disks that are used by the existing database. The following command will take a Windows disk offline with the drive letter “E”: Set-Disk (Get-Partition -DriveLetter E | Get-Disk | Select number -ExpandProperty number) -isOffline $true
- Restore the PowerStore snapshot, either by using PowerStore Manager to select the proper volume group and performing Restore from Snapshot or the PSTCLI.
- Use Windows Disk Management or PowerShell to bring the disks online. The following command will bring 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 following examples recover the database named SnapTest using a backup metadata file: s:\sql\SnapTest_PowerStore_PS13_SQLBackup.bkm.
- When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = 's:\sql\SnapTest_PowerStore_PS13_SQLBackup.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 because the backup metadata files are small.
- When performing a point-in-time recovery and applying differential and/or transaction log backups, use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = 's:\sql\SnapTest_PowerStore_PS13_SQLBackup.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 PowerStore snapshot. Use PowerStore Manager to select the proper PowerStore Volume Group and choose Create Thin Clone using Snapshot or the PSTCLI.
- Map the newly create volume or volumes to the host.
- Bring the disks online with Windows Disk Management or PowerShell and assign new driver letters or mount points.
- Issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The following examples recover the database named SnapTest using a backup metadata file s:\sql\SnapTest_PowerStore_PS13_SQLBackup.bkm and specify that 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 = 's:\sql\SnapTest_PowerStore_PS13_SQLBackup.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 and/or transaction log backups, use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = 's:\sql\SnapTest_PowerStore_PS13_SQLBackup.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 using the standard recovery process.