· 8 years ago · Mar 27, 2018, 12:10 AM
1Restore Database [LasHF]
2from disk = N'\BTSSqlTest1lien_refreshesBackUpsLASDB.1.31s100.bak'
3with File = 1,
4move N'lasdb' to N'E:lien_refreshesSQLDataLasHF.mdf',
5move N'lasdb_log' to N'E:lien_refreshesSQLDataLasHF.ldf',
6NoUnload, Replace, Stats = 25;
7
8Use [LasHF]
9
10Create User [roseLasTEST_Svc]
11
12Grant execute to [roseLasTEST_Svc]
13
14Alter Authorization On Schema::[db_backupoperator] to [roseLasTEST_Svc]
15
16Alter Authorization On Schema::[db_Owner] to [roseLasTEST_Svc]
17
18sp_AddRolemember 'db_backupoperator', 'roseLasTEST_Svc'
19
20sp_AddRolemember 'db_owner', 'roseLasTEST_Svc'
21
22sp_DropUser [roseLasPROD_Svc]
23
24declare @dbNm nvarchar(50) = 'LasHF'
25 declare @user varchar(40) = 'roseLasTEST_Svc'
26 declare @dbId int = DB_Id(@dbNm)
27 -- ----------------------------------------------------------------
28 Declare @tab Table
29 (logNm varchar(256), phyNm varchar(300),
30 Typ varchar, FilGrpNm varChar(128), Siz varchar(128),
31 MaxSize varChar(128), FileId varchar(128),
32 CreateLSN varChar(128),
33 DropLSN varchar(128), UniqueId varChar(128),
34 ROLSN varchar(128),
35 RWLSN varchar(128), BkSizBytes varChar(128),
36 SrceBlckSize varchar(128),
37 FileGrpId varchar(128), LogGrpId varChar(128),
38 DiffBaseLSN varchar(128),
39 DiffBaseGUID varchar(128), IsReadOnly varChar(128),
40 IsPresent varchar(128), ThumbPrint varchar(128))
41 -- ----------------------------------------------------------
42 Insert @tab(logNm, phyNm, Typ, FilGrpNm, Siz, MaxSize, FileId,
43 CreateLSN, DropLSN, UniqueId, ROLSN, RWLSN, BkSizBytes,
44 SrceBlckSize, FileGrpId, LogGrpId, DiffBaseLSN,
45 DiffBaseGUID, IsReadOnly, IsPresent, ThumbPrint)
46 Exec('Restore fileListOnly from disk=''' + @fileSpec + '''')
47 declare @oldDataFileSpec varChar(400),
48 @oldLogFileSpec varChar(400)
49 Set @oldDataFileSpec = (Select logNm from @tab where Typ = 'D')
50 Set @oldLogFileSpec = (Select logNm from @tab where Typ = 'L')
51 -- -------------------------------------
52 declare @dataFile varChar(400)
53 declare @logFile varChar(400)
54 Select @dataFile = physical_name
55 from sys.Master_Files
56 Where Database_Id = @dbId and type = 0
57 Select @logFile = physical_name
58 from sys.Master_Files
59 Where Database_Id = @dbId and type = 1
60
61 declare @killSql nVarChar(200) = 'msdb.dbo.sp_KillUserProc '
62
63 declare @restoreSql nVarChar(1000) =
64 N'Restore Database [' + @dbNm + ']' + @nl +
65 'from disk = N''' + @fileSpec + ''' with File = 1,' + @nl +
66 ' move N''' + @oldDataFileSpec + '''' + ' to N''' +
67 @dataFile + ''',' + @nl +
68 ' move N''' + @oldLogFileSpec + '''' + ' to N''' +
69 @logFile + ''',' + @nl +
70 ' NoUnload, Replace, Stats = 25;'
71
72
73 declare @spids table (spid integer primary key not null)
74 insert @spids(spid)
75 select session_id from sys.dm_exec_sessions
76 where database_id = @dbId
77 -- ----------------------
78 declare @spid int = 0
79 declare @spidstr varchar(4)
80 while exists (select * from @spids where spid > @spid) begin
81 Select @spid = min(spid) from @spids where spid > @spid
82 set @spidstr = format(@spid, '0')
83 set @sqlCode += @killSql + @spidstr + @nl
84 end
85 -- --------------------------------------------
86
87 if @exec = 1 Begin
88 Set @spid = 0
89 while exists (select * from @spids where spid > @spid) begin
90 Select @spid = min(spid) from @spids where spid > @spid
91 set @spidstr = format(@spid, '0')
92 exec(@killSql + @spidstr)
93 end
94 -- ------------------------------------------------
95 exec (@restoreSql)
96 print ' Database [' + @dbNm + '] restored.'
97 end
98 else Set @sqlCode += @restoreSql + @2nl
99
100 -- Switch to new restored database
101 declare @UseSql nVarChar(100) = 'Use [' + @dbNm + ']'
102 if @exec = 1 begin
103 exec (@UseSql)
104 print 'Switched to Database [' + @dbNm + '].'
105 end else Set @sqlCode += @UseSql + @2nl
106
107 -- Create user
108 declare @createSql nVarChar(1000) = N'Create User [{User}]'
109 Set @createSql = Replace(@createSql, '{User}', @user)
110 if @exec = 1 begin
111 exec (@createSql)
112 print 'Created user [' + @user + '].'
113 end else Set @sqlCode += @createSql + @2nl
114
115
116 -- Grant execute permisssions
117 declare @grantSql nVarChar(1000) = N'Grant execute to [{User}]'
118 Set @grantSql = Replace(@grantSql, '{User}', @user)
119 if @exec = 1 begin
120 exec (@grantSql)
121 print 'Execute granted to user [' + @user + '].'
122 end else Set @sqlCode += @grantSql + @2nl
123
124 -- Assign user to schemas -------------
125 declare @schmSql nVarChar(200) =
126 N'Alter Authorization On Schema::[db_backupoperator] to [{user}]'
127 Set @schmSql = Replace(@schmSql, '{User}', @user)
128 if @exec = 1 begin
129 exec (@schmSql)
130 print 'User [' + @user + '] authrzd in schema db_backupoperator.'
131 end else Set @sqlCode += @schmSql + @2nl
132 -- ----------------------------
133 Set @schmSql = Replace(@schmSql, 'db_backupoperator', 'db_Owner')
134 if @exec = 1 begin
135 exec (@schmSql)
136 print 'User [' + @user + '] authorized in schema db_Owner.'
137 end else Set @sqlCode += @schmSql + @2nl
138 -- --------------------------------------------------
139
140 -- Grant backup operator & dbOwner Roles
141 declare @roleSql nVarChar(1000) =
142 'sp_AddRolemember ''db_backupoperator'', ''{User}'''
143 Set @roleSql = Replace(@roleSql, '{User}', @user)
144 if @exec = 1 begin
145 exec (@roleSql)
146 print 'User [' + @user + '] added to role db_backupoperator.'
147 end else Set @sqlCode += @roleSql + @2nl
148 -- ---------------------------
149 set @roleSql = 'sp_AddRolemember ''db_owner'', ''{User}'''
150 Set @roleSql = Replace(@roleSql, '{User}', @user)
151 if @exec = 1 begin
152 exec (@roleSql)
153 print 'User [' + @user + '] added to role db_Owner.'
154 end else Set @sqlCode += @roleSql + @2nl
155
156 -- ----- Drop PROD User -----------
157 declare @dropUserSql nVarchar(50) =
158 'sp_DropUser [roseLasPROD_Svc]'
159 if @exec = 1 begin
160 exec (@dropUserSql)
161 print 'Dropped User [roseLasPROD_Svc].'
162 end else Set @sqlCode += @dropUserSql + @2nl
163 -- ---------------------------
164 if @exec = 0 print @sqlCode
165 Return 0