The graphical utilities and tools within SSMS are a boon for all the application developers and "accidental" DBAs. Often developers need to perform basic DBA activities like a database backup or a restore on local copies of their database. Similarly, the accidental DBAs are acting as a substitute for a DBA who is on leave or unavailable and would like to be as safe as possible. Instead of writing T-SQL code, it is much easier, faster and reliable for them to use the SSMS.

The problem

These usage patterns often expose interesting issues with the design of a product. In June 2011, I had written about an issue that some of the developers in our team were facing – SSMS – Database Restore – Physical File Names should default to Logical File Names – MS Connect Case #668566. For those who work with database backup/restore operations frequently, it is common place to know that:

1. If the paths where the source database files existed are available on the destination file system, the SSMS would set these paths and file names by default
2. If these paths are unavailable, then the default paths are used
3. If the default paths are used, then the file names default to the database name

The problem with this approach is that all data files – primary and secondary – receive the same file name and are configured, by default, to be restored on the same path. The restore would fail if one accepts the defaults set by the Restore wizard.

In relation to this issue, I had logged a Microsoft Connect bug report#668566, which has been fixed in SQL Server 2012 (“Denali”). Today, I will attempt to quickly walk you through the changes made to the restore database UI.

Database Restore Wizard Changes in SQL 2012

Let’s assume that I have a test database, NAVTestDB, on one of my servers at the location C:TestDB with file names – NAVTestDB1.mdf, NAVTestDB2.mdf and NAVTestDB.ldf for the primary and secondary data files and log file respectively.

~~~USE NAVTestDB
GO
SELECT * FROM sys.database_files sdbf ORDER BY sdbf.type asc~~~

Now, let’s take a backup of this database, and move to a different instance of SQL Server 2012 hosted on a machine where the path: C:TestDB is not available. Once the backup file has been moved to the new/destination server, let’s attempt to restore this database using the restore database wizard.

The restore database wizard now has a status bar on top and a more logical interface: 1. User would first select the source to be used for the operation 2. Then supply the destination database 3. Finally select the backup sets to restore

The backup set selection dialog has changed! We now have a familiar dual-pane interface.

The system attempts to read the following from the backup file: 1. Header information 2. Label information 3. Filelist information The user is now aware that an internal operation is going on by the availability of a progress bar within the status bar on top.

Once the backup set information is retrieved, the user can attempt to verify the backup set by clicking on the “Verify Backup Media” button. Confirmation about the verification is available on the status bar. You can read about RESTORE VERIFYONLY from my post here.

FILES Tab: Notice that the user can now choose to relocate the files by checking the “Relocate all files to folder” checkbox and choosing the required path. Also, note that the wizard now shows us the original path & file name and the proposed path & file names. NOTE: Observe that we now have unique file names preventing the original issue that we started off with.

OPTIONS Tab: Moving to the Options tab, we can see that the wizard is more powerful than ever before. The user now has the ability to choose: 1. Recovery state (WITH RECOVERY/NORECOVERY/STANDBY) 2. Specify a standby file 3. Perform a tail log backup before attempting a restore Associated quick help is available in the label boxes below.

Clicking on “OK” begins the restore, however, the user now has the chance to stop the restore in progress.

Conclusion

SQL Server 2012 (“Denali”) comes with a lot of new features and enhancements targeted towards making the lives of developers and administrators more simpler and easier. I hope that you, my respected readers will be able to make maximum use of these new features.

Until we meet next time,

Be courteous. Drive responsibly.