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:
SET SINGLE USER
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:
The initial screen for restoring a database looks like previous versions. Look for the “Timeline” button in the Destination: Restore to: section.
The default database restore will have you restore to latest backup taken.
If you want to restore to an earlier “point-in-time,” select the radio button for a specific data and time.
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.
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