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