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
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;
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:
- 2012 db is manually set to restricted user
- Backup is made from 2012 db
- 2016 db is restored using backup in step 2
- 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
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:
- 2012 db is manually set to restricted user
- Backup is made from 2012 db
- 2016 db is restored using backup in step 2
- 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
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:
- 2012 db is manually set to restricted user
- Backup is made from 2012 db
- 2016 db is restored using backup in step 2
- 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
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:
- 2012 db is manually set to restricted user
- Backup is made from 2012 db
- 2016 db is restored using backup in step 2
- 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
sql-server database database-administration database-backups
asked Mar 27 at 10:21
wenzzzelwenzzzel
3722 silver badges9 bronze badges
3722 silver badges9 bronze badges
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to addwith restricted_user
but notwith multi_user
?
– wenzzzel
Mar 29 at 10:18
1
@wenzzzel, I suspectRESTRICTED_USER
is implemented because it's the more commonRESTORE
use case of restoring from a backup taken inMULTI_USER
. This permits validation of the database before toggling back toMULTI_USER
. Consider submitting a SQL Server product feedback suggestion to allowMULTI_USER
orSINGLE_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
);
);
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to addwith restricted_user
but notwith multi_user
?
– wenzzzel
Mar 29 at 10:18
1
@wenzzzel, I suspectRESTRICTED_USER
is implemented because it's the more commonRESTORE
use case of restoring from a backup taken inMULTI_USER
. This permits validation of the database before toggling back toMULTI_USER
. Consider submitting a SQL Server product feedback suggestion to allowMULTI_USER
orSINGLE_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.
Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to addwith restricted_user
but notwith multi_user
?
– wenzzzel
Mar 29 at 10:18
1
@wenzzzel, I suspectRESTRICTED_USER
is implemented because it's the more commonRESTORE
use case of restoring from a backup taken inMULTI_USER
. This permits validation of the database before toggling back toMULTI_USER
. Consider submitting a SQL Server product feedback suggestion to allowMULTI_USER
orSINGLE_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.
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.
answered Mar 27 at 10:33
Dan GuzmanDan Guzman
26.7k3 gold badges20 silver badges43 bronze badges
26.7k3 gold badges20 silver badges43 bronze badges
Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to addwith restricted_user
but notwith multi_user
?
– wenzzzel
Mar 29 at 10:18
1
@wenzzzel, I suspectRESTRICTED_USER
is implemented because it's the more commonRESTORE
use case of restoring from a backup taken inMULTI_USER
. This permits validation of the database before toggling back toMULTI_USER
. Consider submitting a SQL Server product feedback suggestion to allowMULTI_USER
orSINGLE_USER
too.
– Dan Guzman
Mar 29 at 11:00
That makes sense
– wenzzzel
Mar 29 at 11:57
add a comment |
Thank you for your answer! Just a question out of curiousity. Do you know why it is possible to addwith restricted_user
but notwith multi_user
?
– wenzzzel
Mar 29 at 10:18
1
@wenzzzel, I suspectRESTRICTED_USER
is implemented because it's the more commonRESTORE
use case of restoring from a backup taken inMULTI_USER
. This permits validation of the database before toggling back toMULTI_USER
. Consider submitting a SQL Server product feedback suggestion to allowMULTI_USER
orSINGLE_USER
too.
– Dan Guzman
Mar 29 at 11:00
That makes sense
– wenzzzel
Mar 29 at 11:57
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
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
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
@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
That makes sense
– wenzzzel
Mar 29 at 11:57
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown