SQLServerCentral Article

Caution about Restoring via SSMS v17

,

A lot of DBAs use backup and restore as a way to make copies of databases. The intent often is to have no impact on the source database. In the past, doing this through the SQL Server Managment Studio restore wizard could be acheived by simply selecting a backup of your source database, and changing the destination database name. With the new version of Managment Studio for SQL Server 2017, the default option is now to "take a tail-log backup" of the source database" and "leave the database in the restoring state (with norecovery)":

You can see this default in the image below of the restore dialog. This is on the Options tab that is selected on the right.

Administrators familiar with the old behavior, may not expect their source database to go into the NORECOVERY state. It's important to be aware of this change in behavior.

To correct this, be sure that you always carefully look at your restore dialog. In this case, the top of the dialog above shows a warning in yellow. The default restore dialog has a gray bar, so pay attention to this part of the window.

You should also always script out the command to a new window. This will let you see that there are two commands being run. A BACKUP LOG line and a RESTORE DATABASE line. These two commands listed together might help you avoid an unpleasant situation that takes a production database offline.

Rate

4.82 (22)

You rated this post out of 5. Change rating

Share

Share

Rate

4.82 (22)

You rated this post out of 5. Change rating