CODEWITHSUNDEEP
sql-serverdatabasedatabase-administrationdatabase-backups
wenzzzel
wenzzzel

Reputation: 682

Is database state from source db brought over the the new db when restoring from backup?

Brief background info

We´re moving a database from Windows Server 2012 to 2016 in a critical production environment and are experiencing problems where the database on the new server gets set to Restricted user after the restore is done.

This is the order things are done:

  1. 2012 db is manually set to restricted user
  2. Backup is made from 2012 db
  3. 2016 db is restored using backup in step 2
  4. 2016 db is unwillingly set to restricted user

The reason that we set the 2012 db in restricted user in the first place is to force logged in users to a stand-by environment while the 2016 db is being set-up.

The team that is doing this backup and restore claims that the database state is included in the backup as well and that that's the reason the new database get's set into restricted user as soon as the restore is done.

The new database being set to restricted user is not a huge problem since it's possible to manually change it back to multi user, but since this environment is of such critical nature, we don't want to risk having our users on the backup system any longer then necessary

So here's the actuall question:

When restoring a database from a backup, is the database state included in the backup and brought to the newly restored database? And if so, is it possible to exclude this?

Thanks in advance! :)

Upvotes: 0

Views: 65

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46231

The RESTORE documentation states:

During a restore, most of the database options that are settable using ALTER DATABASE are reset to the values in force at the time of the end of backup.

So if the source database is in RESTRICTED_USER, the database will be set to that mode during the restore. You can, however, specify the WITH RESTRICTED_USER option during the restore to set the restored database to RESTRICTED_USER if the source database was backed up with a different option.

But there is no RESTORE option to specify WITH MULTI_USER so you'll need to execute ALTER DATABASE after the RESTORE to change the option.

Upvotes: 2

Related Questions