Home > Storage > PowerFlex > White Papers > Microsoft SQL Server Data Protection using Dell PowerFlex Snapshots > Use Case 3: Incomplete database recovery
Incomplete database recovery means not all the committed transactions are recovered. Incomplete recoveries restore and recovers the database at certain point-in-time in the past that is, not all the transaction log backups are restored. Typically, incomplete recoveries are performed for one of the following scenarios:
This use case demonstrated the first scenario.
The following steps are followed to simulate the first use case:
1. Create a table in the OLTP database and insert one record into it.
Use OLTP
create table returns (returnID int, returnName varchar (255));
insert into returns values (1,'sampleValue');
select * from returns;
Table 4. OLTP table
returnID | returnName |
1 | sampleValue |
2. Quiesce the OLTP database with the following T-SQL query to freeze the database transactions.
ALTER DATABASE OLTP SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
SQL Server has now halted the database operation and ready for storage snapshot.
3. Create snapshot of database and log volume using PowerFlex GUI as shown below
Figure 19. Database and log volume snapshot
Run the following command to create a snapshot using PowerFlex CLI:
scli --snapshot_volume --volume_name CSV_data –snapshot_name CSV_data-snap-1
Sample output:
Snapshots on two volumes created successfully
Consistency group ID: b10f52c800000002.
4. Take T-SQL metadata backup of the database.
After the snapshot is created, use the following command to create the metadata file and resume database write operations:
BACKUP DATABASE OLTP TO DISK = 'D:\mssql_metadata_backup\OLTP_metadata.bkm' WITH METADATA_ONLY, MEDIANAME='PowerFlex-MSSQL_DATA-Log-Snapshot-backup';
In this step, the metadata backup file of the OLTP database is stored in the specified path. This command also releases the database lock and allows the database operations to resume.
5. Insert two more records in the table with the following query:
insert into returns values (2, 'Item 1');
insert into returns values (3, ' Item 2');
select * from returns;
returnID | returnName |
1 | sampleValue |
2 | Item 1 |
3 | Item 2 |
6. Take a transaction log backup.
The following command creates a log backup which includes the latest inserted records of the returns table. The database recovery uses this log backup.
BACKUP LOG OLTP TO DISK = 'C:\mssql_tail_log_backup\OLTP-tail-log-before-disaster.bkm’
7. Insert another record in the returns table.
insert into returns values (4, 'Item 3');
select * from returns;
returnID | returnName |
1 | sampleValue |
2 | Item 1 |
3 | Item 2 |
4 | Item 3 |
The return table now has four records as shown in the above table.
8. Simulate the database corruption by dropping the customer table from the OLTP database as shown in the following table:
USE OLTP
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
Table 7. Customer table details
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
OLTP | dbo | customer | BASE TABLE |
OLTP | dbo | district | BASE TABLE |
OLTP | dbo | history | BASE TABLE |
OLTP | dbo | item | BASE TABLE |
OLTP | dbo | new_order | BASE TABLE |
OLTP | dbo | orders | BASE TABLE |
OLTP | dbo | order_line | BASE TABLE |
OLTP | dbo | stock | BASE TABLE |
OLTP | dbo | warehouse | BASE TABLE |
OLTP | dbo | returns | BASE TABLE |
Use OLTP;
Drop table dbo.customer;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
As shown in the following table, the customer table has been dropped and is missing from the table list of the database OLTP.
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
OLTP | dbo | district | BASE TABLE |
OLTP | dbo | history | BASE TABLE |
OLTP | dbo | item | BASE TABLE |
OLTP | dbo | new_order | BASE TABLE |
OLTP | dbo | orders | BASE TABLE |
OLTP | dbo | order_line | BASE TABLE |
OLTP | dbo | stock | BASE TABLE |
OLTP | dbo | warehouse | BASE TABLE |
OLTP | dbo | returns | BASE TABLE |
The database corruption has happened post data insertion of the last row to the return table, but transaction log backup has an entry of the two records that got inserted after the storage snapshot.
The following steps restore the database volume snapshot and then recover the database with the last available transaction log backup.
Before restoring the database, if it is still up (depends on the type of corruption), set the database offline by running the following command:
alter database OLTP set offline;
Set-Disk -Number 1 -is Offline $True
Note: In this example only the data disk is set to offline, as the active transaction log remained intact and there is no reason to overwrite it with the log snapshot.
The following is an example of restoring the snapshot using PowerFlex CLI:
scli --overwrite_volume_content --source_vol_name CSV_data-snap-1 --destination_vol_name ‘CSV_data’
Sample output
Overwrite volume content can remove data and should not be called during I/O operations or on mounted volumes. Press 'y' and then Enter to confirm:
Overwrite volume content was completed successfully.
Bring the database volumes back online either using Disk management or PowerShell commands.
Set-Disk -Number 1 -is Offline $False
Bring the OLTP database back online by using following command:
alter database OLTP set online;
Use the metadata file captured during the snapshot backup to make the SQL Server aware of the restored snapshot.
Note: SQL Server requires to perform a backup of active transaction log content before the metadata restore.
BACKUP LOG OLTP TO DISK = 'C:\mssql_tail_log_backup\OLTP-tail_log_after_disaster.bkm' WITH NORECOVERY;
RESTORE DATABASE OLTP FROM DISK = 'C:\mssql_metadata_backup\OLTP_metadata.bkm' WITH METADATA_ONLY, NORECOVERY;
Note: Since the command specifies METADATA_ONLY, SQL Server knows that the database was restored from a storage snapshot. If NORECOVERY is used, the database goes to a restoring state, as it is waiting to apply transaction log backups to make it consistent.
Restore the appropriate transaction log backup or backups to recover the database till point in time. In the following example, you are restoring the last log backup taken. The last transaction log contains three records of the return table.
RESTORE LOG OLTP FROM DISK = 'C:\mssql_tail_log_backup\OLTP-tail-log-before-disaster.bkm’ WITH RECOVERY;
USE OLTP
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’;
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
OLTP | dbo | customer | BASE TABLE |
OLTP | dbo | district | BASE TABLE |
OLTP | dbo | history | BASE TABLE |
OLTP | dbo | item | BASE TABLE |
OLTP | dbo | new_order | BASE TABLE |
OLTP | dbo | orders | BASE TABLE |
OLTP | dbo | order_line | BASE TABLE |
OLTP | dbo | stock | BASE TABLE |
OLTP | dbo | warehouse | BASE TABLE |
OLTP | dbo | returns | BASE TABLE |
select * from returns;
returnID | returnName |
1 | sampleValue |
2 | Item 1 |
3 | Item 2 |
The above table output confirms that the database has been recovered with the customer table and the database was rolled forwarded to the last committed transaction recorded in the transaction log backup that is last three records of the return table.
The last entry with returnID 4 is missing because of no transaction log backup available post entry of the record. This is a scenario of incomplete recovery or database point-in-time recovery because database got recovered at a specific time in the past and not before the actual database corruption time.
This is an example of recovery point objective (RPO) where the RPO is defined at half an hour that is earliest possible data recovery post disaster is half an hour before and if the snapshots and transaction log backups are taken half an hour ago then it is within the defined RPO. The recovery point objective is the maximum amount of data measured by time that can be recovered from a disaster or failure. It also determines the maximum age of the data or files in the backup.
Database administrators and infrastructure teams should collaborate to achieve the Recovery point objective (RPO) of the organization by planning the correct backup schedule. Restore and recovery exercises should also be performed periodically to avoid any surprises while recovering the databases in a real time disaster or outage situation.