· 8 years ago · Nov 22, 2017, 06:08 PM
1USE mydb
2
3SET NOCOUNT on
4BEGIN TRY
5 DROP table #S
6END TRY
7BEGIN CATCH
8END CATCH
9
10BEGIN TRY
11 DROP TABLE #Mytable
12END TRY
13BEGIN CATCH
14END CATCH
15
16BEGIN TRY
17 DROP TABLE #DBInfoResults
18END TRY
19BEGIN CATCH
20END CATCH
21
22go
23
24CREATE table #S ( server_name sysname, purpose VARCHAR(255), servertype VARCHAR(30) )
25insert into #s
26VALUES
27('myserver', 'Test', 'Smallserver'),
28('mybigserverdsa', 'Production', 'Data ware house'),
29('myservertoo', 'Test', 'Data ware house')
30
31declare @Server_name sysname, @Purpose varchar(255)
32declare @sql nvarchar(max) = N'', @s varchar(21)='', @loopCounter int=0, @debug TINYINT=1
33
34DECLARE Server_Cursor CURSOR
35FOR
36SELECT s.server_name, s.purpose
37FROM #S s
38order by 1
39
40CREATE TABLE #Mytable (server_name sysname, database_name sysname, LastFullBackup DATE, LastIncrementalBackup DATE, comment VARCHAR(255), SizeInGB BIGINT, LastRestoreDate DATE, LastKnownGoodDBCCCheck DATE)
41CREATE TABLE #DBInfoResults ([ParentObject] VARCHAR(512),[Object] VARCHAR(512),[Field] VARCHAR(512),[VALUE] VARCHAR(512))
42
43OPEN Server_Cursor
44FETCH NEXT FROM Server_Cursor INTO @Server_name, @Purpose
45WHILE @@FETCH_STATUS = 0 BEGIN
46 set @loopCounter +=1
47 RAISERROR ('%i server: "%s" ', 10 ,1, @Loopcounter, @Server_name) WITH NOWAIT
48
49 select @sql = N'
50insert into #Mytable ( server_name, database_name, LastFullBackup, LastIncrementalBackup)
51SELECT server_name, name, LastFullBackup, LastIncrementalBackup
52from OPENROWSET(''SQLNCLI10'', ''Server='+@Server_name+';Trusted_Connection=yes;'',
53 ''SELECT server.server_name, d.name, FullBackup.LastFullBackup, IncBackup.LastIncrementalBackup
54 FROM sys.databases d
55 OUTER APPLY (SELECT @@SERVERNAME AS server_name) AS server
56 OUTER APPLY (
57 SELECT TOP 1 B.backup_finish_date AS LastFullBackup
58 FROM msdb.dbo.backupset B
59 WHERE TYPE=''''d''''
60 AND server.server_name=B.server_name
61 AND d.name=b.database_name
62 ORDER BY B.backup_finish_date DESC
63 ) AS FullBackup
64 OUTER APPLY (
65 SELECT TOP 1 B.backup_finish_date AS LastIncrementalBackup
66 FROM msdb.dbo.backupset B
67 WHERE TYPE=''''I''''
68 AND server.server_name=B.server_name
69 AND d.name=b.database_name
70 ORDER BY B.backup_finish_date DESC
71 ) AS IncBackup
72 WHERE STATE_DESC = ''''ONLINE''''
73 AND name <> ''''tempdb'''' /* no backups, checkdbs needed */
74 ORDER BY 1,2
75 ''
76 ) as a
77 '
78 if @loopCounter <= 1 IF @debug <> 0 select @sql
79 begin try
80 exec sp_executesql @sql
81 end try
82 begin CATCH
83 print error_number()
84 print ERROR_MESSAGE()
85 end catch
86
87
88 /* loop the databases found on this server */
89 DECLARE @database_name sysname
90 DECLARE db_Cursor CURSOR
91 FOR
92 SELECT database_name
93 FROM #Mytable H
94 WHERE H.server_name=@Server_name
95 order by 1
96
97 OPEN db_Cursor
98 FETCH NEXT FROM db_Cursor INTO @database_name
99 WHILE @@FETCH_STATUS = 0 BEGIN
100 select @sql = N'
101 UPDATE #Mytable
102 SET SizeInGB=(
103 SELECT SizeInGB
104 from OPENROWSET(''SQLNCLI10'', ''Server='+@Server_name+';Trusted_Connection=yes;'',
105 ''
106 SELECT SUM(CAST(size AS BIGINT))*8/1024/1024 as SizeInGB FROM ' + @database_name + '.sys.database_files DF
107 ''
108 ) as a
109 )
110 from #Mytable h
111 where h.server_name=''' + @server_name + ''' and h.DataBase_name=''' + @database_name + '''
112
113 '
114 RAISERROR ('%i server: "%s" db: %s get Size', 10 ,1, @Loopcounter, @Server_name, @database_name) WITH NOWAIT
115 if @loopCounter <= 1 IF @debug <> 0 select @sql
116 begin try
117 exec sp_executesql @sql
118 end try
119 begin CATCH
120
121 print error_number()
122 print ERROR_MESSAGE()
123 end CATCH
124
125 /* last restore date */
126 select @sql = N'
127 UPDATE #Mytable
128 SET LastRestoreDate=(
129 SELECT restore_date
130 FROM OPENROWSET(''SQLNCLI10'', ''Server='+@Server_name+';Trusted_Connection=yes;'',
131 ''
132 SELECT max(restore_date) as restore_date FROM msdb.dbo.restorehistory where destination_database_name=''''' + @database_name + '''''
133 ''
134 ) as a
135 )
136 from #Mytable h
137 where h.server_name=''' + @server_name + ''' and h.DataBase_name=''' + @database_name + '''
138 '
139
140 RAISERROR ('%i server: "%s" db: %s get Restore date ', 10 ,1, @Loopcounter, @Server_name, @database_name) WITH NOWAIT
141 if @loopCounter <= 1 IF @debug <> 0 select @sql
142 begin try
143 exec sp_executesql @sql
144 end try
145 begin CATCH
146
147 print error_number()
148 print ERROR_MESSAGE()
149 end CATCH
150
151
152 /* last DBCC */
153 TRUNCATE TABLE #DBInfoResults
154 select @sql = N'
155 Begin Try
156 EXEC sys.sp_dropserver @server = ''myLinkedServer''
157 End try
158 begin catch
159 end catch
160
161 begin try
162 EXEC sp_addlinkedserver @server=''myLinkedServer'', @srvproduct='''', @provider=''sqlncli'', @datasrc='''+@Server_name+''', @location='''', @provstr='''', @catalog=''' + @database_name + '''
163 EXEC sp_addlinkedsrvlogin @rmtsrvname = ''myLinkedServer'', @useself = ''true''
164 EXEC sp_serveroption ''myLinkedServer'', ''rpc out'', true;
165
166 INSERT INTO #DBInfoResults
167 EXEC (''DBCC DBINFO() WITH TABLERESULTS, NO_INFOMSGS'') at myLinkedServer
168
169 UPDATE #Mytable
170 SET LastKnownGoodDBCCCheck=(SELECT value FROM #DBInfoResults where Field = ''dbi_dbccLastKnownGood'')
171 from #Mytable h
172 where h.server_name=''' + @server_name + ''' and h.DataBase_name=''' + @database_name + '''
173 end try
174 begin catch
175 print error_number()
176 print ERROR_MESSAGE()
177 end catch
178 EXEC sys.sp_dropserver @server = ''myLinkedServer''
179 '
180
181 RAISERROR ('%i server: "%s" dbcc: %s ', 10 ,1, @Loopcounter, @Server_name, @database_name) WITH NOWAIT
182 if @loopCounter <= 1 select @sql
183 begin try
184 exec sp_executesql @sql
185 end try
186 begin CATCH
187
188 print error_number()
189 print ERROR_MESSAGE()
190 end CATCH
191
192 SET @loopCounter+=1
193 FETCH NEXT FROM db_Cursor INTO @database_name
194 END
195 CLOSE db_Cursor ;
196 DEALLOCATE db_Cursor ;
197
198 FETCH NEXT FROM Server_Cursor INTO @Server_name, @Purpose
199END
200CLOSE Server_Cursor ;
201DEALLOCATE Server_Cursor ;
202
203UPDATE #Mytable SET Comment = 'Problem! ' FROM #Mytable H WHERE DATEDIFF(DAY, CASE WHEN h.LastIncrementalBackup>LastFullBackup THEN h.LastIncrementalBackup ELSE LastFullBackup END, GETDATE()) > 1
204UPDATE #Mytable SET Comment = 'No backup required; structure in TFS.' WHERE database_name IN ('vdcasdw', 'mydbTemp', 'VTMChart', 'VTMFileStream', 'VTRArchive')
205UPDATE #Mytable SET Comment = 'No backup required;' WHERE database_name LIKE '%ToBeDeleted'
206UPDATE #Mytable SET Comment = 'No backup required; data in DWH.' WHERE database_name IN ('OperationalData')
207UPDATE #Mytable SET Comment = 'No backup required; test server.' FROM #Mytable H INNER JOIN #S S ON S.server_name = H.server_name WHERE Purpose IN ('test', 'Development')
208
209/* list all checks */
210SELECT top 10000 h.*, s.purpose, s.servertype FROM #Mytable H
211INNER JOIN #S S ON S.server_name = H.server_name
212ORDER BY 1 DESC
213
214
215/* run report on production servers */
216SELECT H.server_name, H.database_name, COALESCE(CAST(H.LastFullBackup AS VARCHAR(30)), 'no backup exists!') AS LastFullBackup
217, COALESCE(CAST(H.LastIncrementalBackup AS VARCHAR(30)), '') AS LastIncrementalBackup
218, COALESCE(CAST(DATEDIFF(DAY, CASE WHEN h.LastIncrementalBackup>LastFullBackup THEN h.LastIncrementalBackup ELSE LastFullBackup END, GETDATE()) AS VARCHAR(30)), '') AS DaysSinceLastBackup
219, COALESCE(comment, '') AS Comment
220, COALESCE(CAST(sizeinGB AS VARCHAR(30)), '') AS SizeinGB
221, COALESCE(CAST(H2.LastRestoreDate AS VARCHAR(30)), 'Backup never tested!') AS LastRestoreDate
222, COALESCE(CAST(DATEDIFF(DAY, h2.LastRestoreDate, GETDATE()) AS VARCHAR(30)), '') AS DaysSinceRestore
223, CASE WHEN H2.LastKnownGoodDBCCCheck <> '1900-01-01' THEN (CAST(H2.LastKnownGoodDBCCCheck AS VARCHAR(30))) else 'A Database without DBCC CheckDB' END AS LastKnownGoodDBCCCheck
224, CASE WHEN H2.LastKnownGoodDBCCCheck <> '1900-01-01' THEN (CAST(DATEDIFF(DAY, h2.LastKnownGoodDBCCCheck, GETDATE()) AS VARCHAR(30))) else '' END AS DaysSinceLastKnownGoodDBCCCheck
225, h2.Purpose AS SystemThatExists
226FROM #Mytable H
227INNER JOIN #S S ON S.server_name = H.server_name
228OUTER APPLY (
229 SELECT MAX(LastKnownGoodDBCCCheck) AS LastKnownGoodDBCCCheck, MAX(LastRestoreDate ) AS LastRestoreDate, utl.CommaListConcatenate(s3.Purpose) AS Purpose FROM #Mytable H3
230 INNER JOIN #S S3 ON S3.server_name = H3.server_name
231 WHERE h.database_name=h3.database_name AND s3.servertype=s.servertype
232) AS h2
233WHERE s.purpose='production'
234ORDER BY 1,2
235
236SELECT LEFT(d.name,20) AS database_name,
237 CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'D' THEN b.backup_finish_date END), 120) AS LastFullBackup,
238 CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'I' THEN b.backup_finish_date END), 120) AS LastDiffBackup,
239 CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'L' THEN b.backup_finish_date END), 120) AS LastLogBackup,
240 CONVERT(VARCHAR(16), MAX(CASE WHEN b.[type] NOT IN ('D','I','L') THEN b.backup_finish_date END), 120) AS LastOtherBackup
241 FROM sys.databases d
242 LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
243 WHERE d.name <> 'tempdb'
244 GROUP BY d.database_id, d.name
245 ORDER BY CASE WHEN d.database_id <= 4 THEN 0 ELSE 1 END, d.name
246
247# Load SMO extension
248[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
249$Servers =
250## A list 'Servername1','Servername2' a text file Get-Content 'PATHTOSERVERFILE' or query a database Invoke-SQLCmd -Server SERVERNAME -Database ALLMyInstances -Query "Select Name FROM Instances"
251foreach($Server in $Servers)
252{
253$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
254$lastDBCC_CHECKDB = @{Name="Last DBCC Check";Expression={$_.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select Value -ExpandProperty Value}}
255foreach($db in $srv.databases)
256{
257$db|Select Parent,Name,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate,$lastDBCC_CHECKDB
258 }
259}
260
261-- I used the same method as written by Brent Ozar Unlimited in sp_Blitz to get the DBCC date.
262-- All credit for that goes to them fof that.
263-- I wrote the rest of it, so similarities to code, living or dead, is unintentional.
264
265declare @databasesize table (dbname nvarchar(128), dbsize decimal(20, 6))
266create table #dbcc (ParentObject varchar(255), [Object] varchar(255), Field varchar(255), Value varchar(255), DbName nvarchar(128) NULL)
267
268insert into @databasesize exec sp_MSforeachdb '
269 select
270 ''?''
271 ,((sum(size) * 1.0) / 128) as DatabaseSize
272 from
273 ?.sys.database_files df'
274
275exec sp_MSforeachdb 'use [?]; insert into #dbcc (ParentObject,
276 Object,
277 Field,
278 Value)
279 EXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');
280 UPDATE #dbcc SET DbName = N''?'' WHERE DbName IS NULL;'
281
282select
283 @@SERVERNAME as ServerName
284 ,sd.[name] as DatabaseName
285 ,ds.dbsize
286 ,max(bsd.backup_finish_date) as LastFullBackupDate
287 ,max(bsl.backup_finish_date) as LastLogBackupDate
288 ,rh.restore_date as BackupFileRestoreDate
289 ,nullif(dbc.Value, '1900-01-01 00:00:00.000') as LastDBCCDate
290from
291 sys.databases sd
292 inner join @databasesize ds on sd.[name] = ds.dbname
293 left outer join msdb..backupset bsd on sd.[name] = bsd.database_name
294 and bsd.[type] = 'D'
295 left outer join msdb..backupset bsl on sd.[name] = bsl.database_name
296 and bsl.[type] = 'L'
297 left outer join #dbcc dbc on sd.[name] = dbc.DbName
298 and dbc.Field = 'dbi_dbccLastKnownGood'
299 left outer join msdb..restorehistory rh on bsd.backup_set_id = rh.backup_set_id
300group by
301 sd.[name]
302 ,ds.dbsize
303 ,dbc.Value
304 ,rh.restore_date
305
306drop table #dbcc