Styjun

How exactly are corporate bonds priced at issue

Does fossil fuels use since 1990 account for half of all the fossil fuels used in history?

Telephone number in spoken words

What are those bumps on top of the Antonov-225?

Heating Margarine in Pan = loss of calories?

How make an image of my entire usb flash drive?

Took GRE two times, same scores with minor differences - worth sending both?

Is it possible to grow new organs through exposure to radioactivity?

Simplification of numbers

crippling fear of hellfire &, damnation, please help?

Why is statically linking glibc discouraged?

Can renaming a method preserve encapsulation?

Why aren’t there water shutoff valves for each room?

Why command hierarchy, if the chain of command is standing next to each other?

Modeling the uncertainty of the input parameters

Why is the result of ('b'+'a'+ + 'a' + 'a').toLowerCase() 'banana'?

How is являться different from есть and быть

Can lodestones be used to magnetize crude iron weapons?

(A room / an office) where an artist works

The cat ate your input again!

How do I call a 6 digit Austrailian phone number with a US based mobile phone?

Do I have to cite common CS algorithms?

Regex crossword (sudoku?)

Can I enter the USA with an E-2 visa and a one way flight ticket?

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

SQL Server: Database stuck in “Restoring” stateBackup and Restore from SQL Server 2005 to 2008 express edition?Android backup/restore: how to backup an internal database?How to maintain login capabilities to a SQL Server 2008 database after a backup/restore across servers?How to create copy of database using backup and restoreYii backup/restore MSSQL databaseIdentify when a database is in restore stateSQL Server 2012: Backup restore from a compressed backupRestoring backup from DB2 9.7 to DB2 10.1SQL Server database stuck in restoring state after BACKUP LOG WITH NORECOVERY

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

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! :)

sql-server database database-administration database-backups

share|improve this question

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

add a comment | 

0

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! :)

sql-server database database-administration database-backups

share|improve this question

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

add a comment | 

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! :)

sql-server database database-administration database-backups

share|improve this question

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

sql-server database database-administration database-backups

sql-server database database-administration database-backups

share|improve this question

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

share|improve this question

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

asked Mar 27 at 10:21

wenzzzelwenzzzel

37222 silver badges99 bronze badges

1 Answer
1

active

oldest

votes

2

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.

share|improve this answer

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to add with restricted_user but not with multi_user?
  
  – wenzzzel
  Mar 29 at 10:18
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @wenzzzel, I suspect RESTRICTED_USER is implemented because it's the more common RESTORE use case of restoring from a backup taken in MULTI_USER. This permits validation of the database before toggling back to MULTI_USER. Consider submitting a SQL Server product feedback suggestion to allow MULTI_USER or SINGLE_USER too.
  
  – Dan Guzman
  Mar 29 at 11:00
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That makes sense
  
  – wenzzzel
  Mar 29 at 11:57
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55374848%2fis-database-state-from-source-db-brought-over-the-the-new-db-when-restoring-from%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

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.

share|improve this answer

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to add with restricted_user but not with multi_user?
  
  – wenzzzel
  Mar 29 at 10:18
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @wenzzzel, I suspect RESTRICTED_USER is implemented because it's the more common RESTORE use case of restoring from a backup taken in MULTI_USER. This permits validation of the database before toggling back to MULTI_USER. Consider submitting a SQL Server product feedback suggestion to allow MULTI_USER or SINGLE_USER too.
  
  – Dan Guzman
  Mar 29 at 11:00
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That makes sense
  
  – wenzzzel
  Mar 29 at 11:57
  

  
  
  
  

add a comment | 

2

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.

share|improve this answer

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to add with restricted_user but not with multi_user?
  
  – wenzzzel
  Mar 29 at 10:18
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @wenzzzel, I suspect RESTRICTED_USER is implemented because it's the more common RESTORE use case of restoring from a backup taken in MULTI_USER. This permits validation of the database before toggling back to MULTI_USER. Consider submitting a SQL Server product feedback suggestion to allow MULTI_USER or SINGLE_USER too.
  
  – Dan Guzman
  Mar 29 at 11:00
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That makes sense
  
  – wenzzzel
  Mar 29 at 11:57
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges

share|improve this answer

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges

answered Mar 27 at 10:33

Dan GuzmanDan Guzman

26.7k33 gold badges2020 silver badges4343 bronze badges