Skip to content

Easier Point-in-Time Database Restore with SQL Server 2012

Easier Point-in-Time Database Restore with SQL Server 2012

With SQL Server 2012, database backup and restore operations have been improved to make the process easier.  The Backup / Restore task dialog box allows you to restore to a point-in-time in a very intuitive fashion.

For example, terminating connections prior to restoring requires a couple of lines of SQL code to the effect of:

ALTER DATABASE
SET SINGLE USER
Restore Command
SET MULTI USER

Granted, you will still be executing the same commands, but the system will script out the commands for you, whereas in the past, you would either have had to remember the commands, write them down somewhere yourself, and then retrieve them; or go for a Google search.

Here’s how to restore a SQL Server 2012 database by pointing-and-clicking your way through the graphical user interface:

SQL Server 2012 Database Restore



The initial screen for restoring a database looks like previous versions. Look for the “Timeline” button in the Destination: Restore to: section.

Timeline Selection for SQL Server 2012 Database Restore



The default database restore will have you restore to latest backup taken.

Backup Timeline for SQL Server 2012 Database Restore



If you want to restore to an earlier “point-in-time,” select the radio button for a specific data and time.

SQL Server 2012 Database Restore Options



One of the key options to check when you’re restoring, is to close existing connections. This is something that always had to be written out in the SQL Script — now the script can be generated automatically based on these selected options.

SQL Server 2012 - Generate Database Restore Script



To generate the script, click on the menu item at the top of the dialog to Script to New Query Editor Window.

USE [master]
ALTER DATABASE [MyDBName_MSCRM] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDBName_MSCRM] FROM  DISK = N'U:\CRM-DB-Backups\MyDBName_MSCRM\MyDBName_MSCRM_backup_2013_09_03_020001_4058041.bak' WITH  FILE = 1,  
NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [MyDBName_MSCRM] SET MULTI_USER
 
GO

 

 

 

 

No comment yet, add your voice below!


Add a Comment

Your email address will not be published. Required fields are marked *