Restore improvements in SQL Server Denali CTP3 Management Studio

  • Article

SQL Server Denali CTP3 comes with a lot of improvements in all areas but some areas are more visible than others.  Some of the improvements in SQL Server Management Studio in the restore area are quite useful however.  Lets look at some of these improvements. 

An interesting addition is the Timeline button

image

Clicking the Timeline button will open the following window:

image

This will let you configure a point-in-time restore in a visual way which will greatly simplify this task.
You can find more information about the Timeline at https://msdn.microsoft.com/en-us/library/ff963619(v=SQL.110).aspx.

 

A small but yet useful change is the ability to easily relocate database files.  This was possible in previous version but the the interface is a lot more intuitive now.

image

Another way of limiting the time to do restores in case of corruption are Page Restores.  Imagine you run into a situation where a certain physical error occurs corrupting just a page in the database.  Since one of the most important aspects of a database is that you can guarantee consistency, having a corrupt page is a pretty significant issue to say the least.  A new option in the interface will also help you determine which pages have been corrupted in your database.  Detecting this can take some time if you have a large database of course (as mentioned below it will launch DBCC CHECKDB PHYSICAL_ONLY). 

image

Time to corrupt a database and see if the interface reacts as expected.  There is a nice tutorial on how you can corrupt your database at https://sqlserverpedia.com/blog/sql-server-bloggers/corrupting-databases-for-dummies-hex-editor-edition/.  Obviously intentionally corrupting your database is not for production purposes.

image

This will add the following to the T-SQL script:

RESTORE DATABASE [AdventureWorksDWDenali] PAGE='1:1688' FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDWDenali.bak' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5

These are just a couple of small changes in the UI that can make a world of difference when it comes to recovering your database.