SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Caution about Restoring via SSMS v17

By Warren Campbell,

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.

 
Total article views: 1495 | Views in the last 30 days: 1495
 
Related Articles
FORUM

Backup/restore

Backup/restore

FORUM

Restore from Backup with Replicated Database

Restore from Backup with Replicated Database

FORUM

database backup and restore in sql server 2005

database backup and restore using java

SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

FORUM

Restore multiple Differential backups

Restore multiple Differential backups

 
Contribute