· 8 years ago · Mar 27, 2018, 05:26 AM
1Restore Database [newDBName]
2from disk = N'\BTSSqlTest1lien_refreshesBackUpsLASDB.1.31s100.bak'
3with File = 1,
4move N'lasdb' to N'E:lien_refreshesSQLDatanewDBName.mdf',
5move N'lasdb_log' to N'E:lien_refreshesSQLDatanewDBName.ldf',
6NoUnload, Replace, Stats = 25;
7
8
9Use [newDBName]
10
11Create User [domainNewUserName]
12
13Grant execute to [domainNewUserName]
14
15Alter Authorization On Schema::[db_backupoperator] to [domainNewUserName]
16
17Alter Authorization On Schema::[db_Owner] to [domainNewUserName]
18
19sp_AddRolemember 'db_backupoperator', domainNewUserName'
20
21sp_AddRolemember 'db_owner', domainNewUserName'
22
23sp_DropUser [domainOldUserName]
24
2525 percent processed.
2650 percent processed.
2775 percent processed.
28100 percent processed.
29Processed 779144 pages for database 'newDBName', file 'lasdb' on file 1.
30Processed 6 pages for database 'newDBName', file 'lasdb_log' on file 1.
31RESTORE DATABASE successfully processed 779150 pages in 11.532 seconds (527.845 MB/sec).
32Database [newDBName] restored.
33Switched to Database [newDBName].
34Execute granted to user [domainNewUserName].
35User [domainNewUserName] authorized in schema db_backupoperator.
36User [domainNewUserName] authorized in schema db_Owner.
37User [domainNewUserName] added to role db_backupoperator.
38User [domainNewUserName] added to role db_Owner.
39Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
40User domainOldUserName' does not exist in the current database.
41Dropped User [domainOldUserName].
42
43******************************************************
44 ****** Stored proc ***********************************
45 Create PROCEDURE RestoreProdTest
46 @fileSpec nvarchar(400),
47 @exec bit = 0
48 As
49 Set NoCount On
50 declare @nl Char(2) = char(13) + char(10)
51 declare @2nl char(4) = @nl + @nl
52 -- --------------------------------
53 declare @debugMsg varchar(max) = 'Variable Values:' + @nl
54 declare @sqlCode varchar(max) = 'Executable SQL Code:' + @nl
55
56 declare @dbNm nvarchar(50) = 'newDBName'
57 declare @user varchar(40) = 'domainNewUserName'
58 declare @dbId int = DB_Id(@dbNm)
59 -- ----------------------------------------------------------------
60 Declare @tab Table
61 (logNm varchar(256), phyNm varchar(300),
62 Typ varchar, FilGrpNm varChar(128), Siz varchar(128),
63 MaxSize varChar(128), FileId varchar(128),
64 CreateLSN varChar(128),
65 DropLSN varchar(128), UniqueId varChar(128),
66 ROLSN varchar(128),
67 RWLSN varchar(128), BkSizBytes varChar(128),
68 SrceBlckSize varchar(128),
69 FileGrpId varchar(128), LogGrpId varChar(128),
70 DiffBaseLSN varchar(128),
71 DiffBaseGUID varchar(128), IsReadOnly varChar(128),
72 IsPresent varchar(128), ThumbPrint varchar(128))
73 -- ----------------------------------------------------------
74 Insert @tab(logNm, phyNm, Typ, FilGrpNm, Siz, MaxSize, FileId,
75 CreateLSN, DropLSN, UniqueId, ROLSN, RWLSN, BkSizBytes,
76 SrceBlckSize, FileGrpId, LogGrpId, DiffBaseLSN,
77 DiffBaseGUID, IsReadOnly, IsPresent, ThumbPrint)
78 Exec('Restore fileListOnly from disk=''' + @fileSpec + '''')
79 declare @oldDataFileSpec varChar(400),
80 @oldLogFileSpec varChar(400)
81 Set @oldDataFileSpec = (Select logNm from @tab where Typ = 'D')
82 Set @oldLogFileSpec = (Select logNm from @tab where Typ = 'L')
83 -- -------------------------------------
84 declare @dataFile varChar(400)
85 declare @logFile varChar(400)
86 Select @dataFile = physical_name
87 from sys.Master_Files
88 Where Database_Id = @dbId and type = 0
89 Select @logFile = physical_name
90 from sys.Master_Files
91 Where Database_Id = @dbId and type = 1
92
93 declare @killSql nVarChar(200) = 'msdb.dbo.sp_KillUserProc '
94
95 declare @restoreSql nVarChar(1000) =
96 N'Restore Database [' + @dbNm + ']' + @nl +
97 'from disk = N''' + @fileSpec + ''' with File = 1,' + @nl +
98 ' move N''' + @oldDataFileSpec + '''' + ' to N''' +
99 @dataFile + ''',' + @nl +
100 ' move N''' + @oldLogFileSpec + '''' + ' to N''' +
101 @logFile + ''',' + @nl +
102 ' NoUnload, Replace, Stats = 25;'
103
104
105 declare @spids table (spid integer primary key not null)
106 insert @spids(spid)
107 select session_id from sys.dm_exec_sessions
108 where database_id = @dbId
109 -- ----------------------
110 declare @spid int = 0
111 declare @spidstr varchar(4)
112 while exists (select * from @spids where spid > @spid) begin
113 Select @spid = min(spid) from @spids where spid > @spid
114 set @spidstr = format(@spid, '0')
115 set @sqlCode += @killSql + @spidstr + @nl
116 end
117 -- --------------------------------------------
118
119 if @exec = 1 Begin
120 Set @spid = 0
121 while exists (select * from @spids where spid > @spid) begin
122 Select @spid = min(spid) from @spids where spid > @spid
123 set @spidstr = format(@spid, '0')
124 exec(@killSql + @spidstr)
125 end
126 -- ------------------------------------------------
127 exec (@restoreSql)
128 print ' Database [' + @dbNm + '] restored.'
129 end
130 else Set @sqlCode += @restoreSql + @2nl
131
132 -- Switch to new restored database
133 declare @UseSql nVarChar(100) = 'Use [' + @dbNm + ']'
134 if @exec = 1 begin
135 exec (@UseSql)
136 print 'Switched to Database [' + @dbNm + '].'
137 end else Set @sqlCode += @UseSql + @2nl
138
139 -- Grant execute permissions (also creates the user)
140 declare @grantSql nVarChar(1000) = N'Grant execute to [{User}]'
141 Set @grantSql = Replace(@grantSql, '{User}', @user)
142 if @exec = 1 begin
143 exec (@grantSql)
144 print 'Execute granted to user [' + @user + '].'
145 end else Set @sqlCode += @grantSql + @2nl
146
147 -- Assign user to schemas -------------
148 declare @schmSql nVarChar(200) =
149 N'Alter Authorization On Schema::[db_backupoperator] to [{user}]'
150 Set @schmSql = Replace(@schmSql, '{User}', @user)
151 if @exec = 1 begin
152 exec (@schmSql)
153 print 'User [' + @user + '] authrzd in schema db_backupoperator.'
154 end else Set @sqlCode += @schmSql + @2nl
155 -- ----------------------------
156 Set @schmSql = Replace(@schmSql, 'db_backupoperator', 'db_Owner')
157 if @exec = 1 begin
158 exec (@schmSql)
159 print 'User [' + @user + '] authorized in schema db_Owner.'
160 end else Set @sqlCode += @schmSql + @2nl
161 -- --------------------------------------------------
162
163 -- Grant backup operator & dbOwner Roles
164 declare @roleSql nVarChar(1000) =
165 'sp_AddRolemember ''db_backupoperator'', ''{User}'''
166 Set @roleSql = Replace(@roleSql, '{User}', @user)
167 if @exec = 1 begin
168 exec (@roleSql)
169 print 'User [' + @user + '] added to role db_backupoperator.'
170 end else Set @sqlCode += @roleSql + @2nl
171 -- ---------------------------
172 set @roleSql = 'sp_AddRolemember ''db_owner'', ''{User}'''
173 Set @roleSql = Replace(@roleSql, '{User}', @user)
174 if @exec = 1 begin
175 exec (@roleSql)
176 print 'User [' + @user + '] added to role db_Owner.'
177 end else Set @sqlCode += @roleSql + @2nl
178
179 -- ----- Drop PROD User -----------
180 declare @dropUserSql nVarchar(50) =
181 'sp_DropUser [roseLasPROD_Svc]'
182 if @exec = 1 begin
183 exec (@dropUserSql)
184 print 'Dropped User [roseLasPROD_Svc].'
185 end else Set @sqlCode += @dropUserSql + @2nl
186 -- ---------------------------
187 if @exec = 0 print @sqlCode
188 Return 0