Home » SQL Backup & Restore » A Brief Explanation of SQL Server Point In Time Restore

A Brief Explanation of SQL Server Point In Time Restore

Stacy Brown ~ Modified: February 19th, 2019 ~ SQL Backup & Restore ~ 4 Minutes Reading

Introduction

The SQL Server point in time restore option becomes useful at the time of disaster recovery. For that, you will have to push back the SQL Server database. It could be a requirement because of many reasons, which include accidentally deleting a certain table, improperly executing DML or DDL commands, etc. or because of:

  • The upgradation of the database failed because of the failure in the scripts.
  • The data stored in database may be destroyed by bugs or virus attacks.
  • Because of the transaction, logs as well as the backups were missing the recovery of the database after failure did not work well.

You can overcome these situations through the roll backing of the database to its previous state; a state where the damages did not affect the databases. However, for the restoring back to the point it depends on the recovery models.

What Is Recovery Models?

Recovery models are maintained for the control of the transaction logs. Mainly, you can see three models of recovery. They are Simple, Bulk-logged and Full.

Simple Recovery Model You will have to agree to miss the changes that are made during the last full backup. This mode does not support SQL Server point in time recovery, log shipping, database mirroring, etc.

Bulk-Logged Model It allows performing the operation in bulk without writing the transaction to the transaction logs and this does not support the relapsing fully since lack the logs.

Full Recovery Model This model enables you to restore the database to the previous point, but you will have to get the complete log chain. A log chain is created when a full backup is processed in the full recovery model. The restoration takes the way as long as the log chain is not broken. However, if chain is broken, you will have to take the backup once again.

The SQL Server Point In Time Restore occurs only in the bulk or fully recovery models. However, proper restoration of SQL Server database to the point in time can take place only in full recovery model. In bulk-logged model, you will come across some errors and it may fail.

How To Perform SQL Server Point In Time Restore ?

Restore Using Two Ways

Server Management Studio

Follow the steps to restore to the point in time:

  1. Connect to the Server Database engine and expand the folders.
  2. Open the database either user-defined or System Databases, choose accordingly.
  3. Right-click the required database, select Task then points to Restore and Databases.
    Databases
  4. In the General window, specify the Source and Location for the restore. You can select either from the database or from device.
    Restore Database
  5. On the Destination section it loads the name automatically when you select the file from database. If selection is done from device, you will have to specify the destination name.
    DB Test
  6. Specify the Timeline accordingly for restoring the database back to time.
    Point In Time Restore
  7. In the Option window, you will come across Restore option and Recovery state options.
  8. Restore option has many options. Select according to the requirement.
    • Overwrite the existing database (WITH REPLACE)
    • Preserve the replication settings (WITH KEEP_REPLICATION)
    • Prompt before restoring each backup
    • Restrict access to the restored database (WITH RESTRICTED_USER)

    Restore Option

  9. In Recovery state section, you can find the options for setting recovery state of the database after restore.
    Recovery States

    • RESTORE WITH RECOVERY: It is the default option and keeps database ready by rolling back uncommitted transactions.
    • RESTORE WITH NORECOVERY: Uncommitted transactions are not roll backed but extra transactions can be retrieved.
    • RESTORE WITH STANDBY: It eliminates the uncommitted transactions but also stores the eliminating actions in the Standby file for reverting the recovery effects later.
  10. Click on “OK” once completed.

T-SQL

For going with this method, you need full transaction logs. Here, the restoration takes place from log backups only. In this, the target database name is specified along with the time in STOPAT clauses.

Syntax:

With STOPAT

In this, the transactions those are committed after the specified time in WITH STOPAT clause is not restored and an error message will be displayed if the backup is of recent i.e. doesn’t suite with the target time. If the time specified is incorrect then, the success of restoration is uncertain in this case.

Conclusion

It’s recommended to verify backup integrity to check If the database is affected by bugs or by mistakes, the recovery to previous stage, i.e. before damage is possible through two ways as mentioned above; Server Management Studio and T-SQL. However, in case if there is any corruption occurs in .bak file while restoring .bak file to SQL Server point in time then one can go with SQL backup recovery tool to repair corrupt .bak file.