August 8, 2019 at 3:58 pm
Hi,
Couple of question with below restore script. This is an existing script and I am trying to understand and improve it. Please share your suggestions. The databases are 3 - 5 TB in size so any performance tips are also welcome.
USE [master]
GO
DENY CONNECT SQL TO [ROLENAME]
----- Q) is it to deny any connection to role while restore is in progress?
GO
USE [master]
ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DBNAME] FROM DISK = N'\\LOCATION\DBNAME.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DBNAME] SET MULTI_USER
-- what are file =1 and stats = 5 options will do ?
GO
USE [master]
GO
GRANT CONNECT SQL TO [ROLENAME]
--- And I am not sure why we need to grant again. Is it like only allow the restore process to happen and nothing else?.
GO
USE [master]
ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DBNAME] FROM DISK = N'\\LOCATION\DBNAME.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DBNAME] SET MULTI_USER
?
Thanks,
August 8, 2019 at 5:15 pm
----- Q) is it to deny any connection to role while restore is in progress?
This is to prevent connections for members in the server role ROLENAME. As this is only a deny, if they have been granted CONNECT permissions anywhere else, it is possible for them to still log in. While the database is restoring, no one will be able to access it, regardless if they have CONNECT permissions. This may be a hedge to keep other databases on the same server from getting updated by processes that update tables in multiple databases.
-- what are file =1 and stats = 5 options will do ?
File = 1 means to use the backup in slot 1 of the file given by DISK = N'\\LOCATION\DBNAME.bak' A single backup file can hold multiple backups, depending on if the backup specified INIT, NOINIT, or FORMAT or NOFORMAT.
Stats = 5 means to give feedback in the messages window on each 5% of the backup having been restored.
--- And I am not sure why we need to grant again. Is it like only allow the restore process to happen and nothing else?.
The role ROLENAME appears to be a server role, so restoring the database will have no effect on it. If the CONNECT permissions were denied before, they would need to be re-granted now, in order to bring the system back to how it was at the beginning.
I am not sure why there is a second restore command. The first command did not specify norecovery, so this would be an attempted restore of a full backup all over again.
August 8, 2019 at 7:58 pm
I also think the DENY CONNECT and later GRANT CONNECT is an odd way to do this. What I've typically done is instead of
ALTER DATABASE [DBname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
I typically use
ALTER DATABASE [DBname] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
that way the "single user" can't be a regular ordinary user. Is this script going to be run interactively by a DBA or is it going to be part of a SQL Agent job? The STATS = 5 part only makes sense if this script is run directly by someone watching it interactively.
August 8, 2019 at 9:26 pm
I follow what Chris H does, though two things:
I've been burned by a bad restore before. Add a commented out RESTORE WITH RECOVERY to bring this online when you are ready.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply