November 8, 2017 at 9:11 am
river1 - Wednesday, November 8, 2017 7:32 AMHello Guys,Than you very much for your answers.
Now before restore is done, I need to guaranty that no connections are in place.
I was thinking about: ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This WITH ROLLBACK IMMEDIATE will kill all the connections, correct?
Thank you
Yes but you don't want to leave it that way just in case something goes wrong where you might not be able to get a connection back because some web service beat you to it if you lose it.
Write the script to do the single user thing, immediately change back to multi-user, and immediate start the restore before anything else realizes what just happened.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2017 at 9:17 am
Thank you.
This brings me to the next question.
I will do like:
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) Restore Command
3) ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
Questions are now related with number 2 and 3. On number 2 can I use the force to restore the backup even if the existing DB is newer that the one being restored? For number 3, should I use Rollback immediate as well?
Thank you
November 8, 2017 at 9:27 am
river1 - Wednesday, November 8, 2017 9:17 AM1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) Restore Command
3) ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATEQuestions are now related with number 2 and 3. On number 2 can I use the force to restore the backup even if the existing DB is newer that the one being restored?
What do you mean by "newer" - a new version of SQL Server? You can restore a database on to a server of a newer version (but not an older one) than the database. Don't forget to change the compatibility level, unless you want to leave it at that of the source server.
For number 3, should I use Rollback immediate as well?
Number 3 is unnecessary, since the database will be in the same state as the one you restored, not as it was before it was restored. And even if the step were necessary, setting to multi-user doesn't require any connections to be killed, so you wouldn't need WITH ROLLBACK IMMEDIATE.
John
November 8, 2017 at 9:28 am
river1 - Wednesday, November 8, 2017 9:17 AMThank you.This brings me to the next question.
I will do like:
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) Restore Command
3) ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATEQuestions are now related with number 2 and 3. On number 2 can I use the force to restore the backup even if the existing DB is newer that the one being restored? For number 3, should I use Rollback immediate as well?
Thank you
You can always try out to ensure your scenarios work and your code is good.
RESTORE will restore it as per the file you give, so it will do it however you tell it to, if you want to do a replacement do REPLACE, if you need to move files use MOVE etc, just as you would restore it manually.
For multi user, no need to do that unless you take your production database into single user to do the BACKUP. As the RESTORE will put it into whatever state the database was in when the BACKUP happened.
November 8, 2017 at 9:36 am
Actually I have found some more interesting things.
The command that I am trying is this:
Declare @DBName VARCHAR(200)
Declare @Path VARCHAR(1000)
set @DBName = 'MyDBName'
set @Path = (select dbo.ReturnFullBackupPath (replace (@DBName, '_Prod', '')))
--Sets DB in Single user Mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
--Restore lastest full backup from PROD
exec ('restore database ' + @DBName + ' from disk = ''' + @Path + '''')
--Database back to Multi User mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE')
This is a function that is returning the path as UNC and it is providing me the right output : '\\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'
But when I run I get this error:
Msg 3201, Level 16, State 2, Line 23
Cannot open backup device '\\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 23
RESTORE DATABASE is terminating abnormally.
Can this be a permissions issue?
Another thing that I noticed is that if the second command: exec (
'restore database ' + @DBName + ' from disk = ''' + @Path + '''') produces an error , then the first one: exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE') is also not executed.
I am running this in query pane of MS.
If I execute in a job, result is the same? Only first is executed if second already executed?
Thanks
November 8, 2017 at 9:45 am
river1 - Wednesday, November 8, 2017 9:36 AMActually I have found some more interesting things.The command that I am trying is this:
Declare @DBName VARCHAR(200)
Declare @Path VARCHAR(1000)
set @DBName = 'MyDBName'
set @Path = (select dbo.ReturnFullBackupPath (replace (@DBName, '_Prod', '')))
--Sets DB in Single user Mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
--Restore lastest full backup from PROD
exec ('restore database ' + @DBName + ' from disk = ''' + @Path + '''')
--Database back to Multi User mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE')
This is a function that is returning the path as UNC and it is providing me the right output : '\\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'
But when I run I get this error:
Msg 3201, Level 16, State 2, Line 23
Cannot open backup device '\\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 23
RESTORE DATABASE is terminating abnormally.
Can this be a permissions issue?
I suspect the issue here is that you're using an administrative share (G$) in your UNC. You'll need to create a normal user share, grant permissions on it to the service account, then add logic to your function to convert the administrative share to the normal one in the file path.
Another thing that I noticed is that if the second command: exec ('restore database ' + @DBName + ' from disk = ''' + @Path + '''') produces an error , then the first one: exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE') is also not executed.
Yes, if it's a parse-time error (which I think you would get if you attempt to build your SQL statement on the fly) then none of the batch will be executed.
John
November 8, 2017 at 9:56 am
"Can this be a permissions issue?"
I don't know, is SQLServer Account that needs to have^the right permissions to open the backup , correct? not my account.
As far as I can understand, what I need to have in my account is previlleges to do restores, this case in dev environemnt (where I am restoring) but its the SQL Server Service account of my engine that needs to have the permissions to connect to PROD server and start the restore, correct?
Meaning, have permissions to go here: \\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'
IF so, should I give serveradmin or process admin rights to the service account?
Thank you
November 8, 2017 at 10:01 am
It's very likely a permissions issue. When you run backups through SQL Agent, for example, that backup process is running under SQL Agent's login, whatever that might be. When you're connected to SQL Server through SSMS and running queries, it's your login. It's entirely possible you don't permission to that path. Or vice versa, you do and SQL Server does not.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 8, 2017 at 11:03 am
river1 - Wednesday, November 8, 2017 9:56 AM"Can this be a permissions issue?"I don't know, is SQLServer Account that needs to have^the right permissions to open the backup , correct? not my account.
As far as I can understand, what I need to have in my account is previlleges to do restores, this case in dev environemnt (where I am restoring) but its the SQL Server Service account of my engine that needs to have the permissions to connect to PROD server and start the restore, correct?
Meaning, have permissions to go here: \\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'
IF so, should I give serveradmin or process admin rights to the service account?
Thank you
That looks like an admin share. Try creating a regular share to the backup folder with the necessary permissions instead of using an admin share.
Sue
November 8, 2017 at 11:17 am
Hello. Cannot create another share. It's configurated like that and they don't want to change it.
if I understood well, if I am in arms then it is my account that needs permissions to connect to that share and to make the restore. If I am adding this code to a sql job then it's the sql agent service account that needs this permissions?
I thought that in case of ssms it would the engine service account that would need to be a domain account and able to see the share. Are you sure that it's my account?
November 8, 2017 at 12:18 pm
river1 - Wednesday, November 8, 2017 9:17 AMThank you.This brings me to the next question.
I will do like:
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) Restore Command
3) ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATEQuestions are now related with number 2 and 3. On number 2 can I use the force to restore the backup even if the existing DB is newer that the one being restored? For number 3, should I use Rollback immediate as well?
Thank you
That's not correct. It should be...
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) ALTER DATABASE [YourDatabaseName] SET MULTI_USER
3) Restore Command
Like I said in my previous post, if anything goes wrong with the Restore doing it your way and it causes you to lose the connection, something else may pick up that single connection even though the database isn't ready rock and then you're dead meat.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2017 at 12:34 pm
Jeff Moden - Wednesday, November 8, 2017 12:18 PMriver1 - Wednesday, November 8, 2017 9:17 AMThank you.This brings me to the next question.
I will do like:
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) Restore Command
3) ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATEQuestions are now related with number 2 and 3. On number 2 can I use the force to restore the backup even if the existing DB is newer that the one being restored? For number 3, should I use Rollback immediate as well?
Thank you
That's not correct. It should be...
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) ALTER DATABASE [YourDatabaseName] SET MULTI_USER
3) Restore CommandLike I said in my previous post, if anything goes wrong with the Restore doing it your way and it causes you to lose the connection, something else may pick up that single connection even though the database isn't ready rock and then you're dead meat.
It's why I always preferred RESTRICTED_USER, but then I had good security and a high degree of confidence that I wasn't going to be stepping on another DBAs toes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 8, 2017 at 12:44 pm
John Mitchell-245523 - Wednesday, November 8, 2017 9:45 AMriver1 - Wednesday, November 8, 2017 9:36 AMActually I have found some more interesting things.The command that I am trying is this:
Declare @DBName VARCHAR(200)
Declare @Path VARCHAR(1000)
set @DBName = 'MyDBName'
set @Path = (select dbo.ReturnFullBackupPath (replace (@DBName, '_Prod', '')))
--Sets DB in Single user Mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
--Restore lastest full backup from PROD
exec ('restore database ' + @DBName + ' from disk = ''' + @Path + '''')
--Database back to Multi User mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE')
This is a function that is returning the path as UNC and it is providing me the right output : '\\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'
But when I run I get this error:
Msg 3201, Level 16, State 2, Line 23
Cannot open backup device '\\ServerName\G$\Backups\FolderName\DB_FULL_201711050200.bak'. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 23
RESTORE DATABASE is terminating abnormally.
Can this be a permissions issue?
I suspect the issue here is that you're using an administrative share (G$) in your UNC. You'll need to create a normal user share, grant permissions on it to the service account, then add logic to your function to convert the administrative share to the normal one in the file path.
Another thing that I noticed is that if the second command: exec ('restore database ' + @DBName + ' from disk = ''' + @Path + '''') produces an error , then the first one: exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE') is also not executed.
Yes, if it's a parse-time error (which I think you would get if you attempt to build your SQL statement on the fly) then none of the batch will be executed.
John
Why do you say I need to create a normal share? I think that even with administrative share, if with right permissions, then this is not an issue. And this admin share was created just to hold backups
November 8, 2017 at 1:02 pm
Grant Fritchey - Wednesday, November 8, 2017 12:34 PMJeff Moden - Wednesday, November 8, 2017 12:18 PMriver1 - Wednesday, November 8, 2017 9:17 AMThank you.This brings me to the next question.
I will do like:
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) Restore Command
3) ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATEQuestions are now related with number 2 and 3. On number 2 can I use the force to restore the backup even if the existing DB is newer that the one being restored? For number 3, should I use Rollback immediate as well?
Thank you
That's not correct. It should be...
1) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2) ALTER DATABASE [YourDatabaseName] SET MULTI_USER
3) Restore CommandLike I said in my previous post, if anything goes wrong with the Restore doing it your way and it causes you to lose the connection, something else may pick up that single connection even though the database isn't ready rock and then you're dead meat.
It's why I always preferred RESTRICTED_USER, but then I had good security and a high degree of confidence that I wasn't going to be stepping on another DBAs toes.
Understood and agreed on the high security thing except I don't trust the other people that could get in as RESTRICTED_USER, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2017 at 1:10 pm
Jeff Moden - Wednesday, November 8, 2017 1:02 PMUnderstood and agreed on the high security thing except I don't trust the other people that could get in as RESTRICTED_USER, either.
Or SQL Server Agent. I think that's one of the more problematic ones.
Sue
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply