· 6 years ago · Apr 11, 2019, 11:08 AM
1USE master
2GO
3
4-- set required options
5exec sp_configure 'show advanced options',1
6reconfigure
7go
8exec sp_configure 'xp_cmdshell',1
9reconfigure
10go
11exec sp_configure 'Ole Automation Procedures',1
12reconfigure
13go
14
15
16
17IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[expressmaint]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
18DROP procedure [dbo].[expressmaint]
19GO
20
21CREATE PROCEDURE [dbo].[expressmaint]
22(
23 @DATABASE sysname, -- database name | ALL_USER | ALL_SYSTEM
24 @optype varchar(7), -- LOG | DB | DIFF | REINDEX | REORG | CHECKDB
25 @backupwith varchar(500) = NULL, -- additional backup options
26 @backupfldr varchar(200) = NULL, -- folder to write backup to
27 @reportfldr varchar(200) = NULL, -- folder to write text report
28 @verify bit = 1, -- verify backup
29 @verifywith varchar(500) = NULL, -- additional verify options
30 @dbretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
31 @dbretainval int = 1, -- specifies how many retainunits to keep backup
32 @report bit = 1, -- flag to indicate whether to generate report
33 @rptretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
34 @rptretainval int = 1, -- specifies how many retainunits to keep reports
35 @checkattrib bit = 0, -- check if archive bit is cleared before deleting
36 @delfirst bit = 0, -- delete before backup (handy if space issues)
37 @debug bit = 0, -- print commands to be executed
38 @compression bit = 0 -- backup compression (SQL 2008 Enterprise Edition only)
39)
40AS
41/*
42 ExpressMaintTSQL
43
44 see http://www.sqldbatips.com/showarticle.asp?ID=27 for documentation
45
46 Date Author Notes
47 24/07/2004 Jasper Smith Initial release
48 28/07/2008 Jasper Smith Fixed datepart issue with MONTHS retention unit
49 06/12/2008 Jasper Smith Added SQL 2008 support plus some minor fixes
50
51*/
52SET NOCOUNT ON
53SET ARITHABORT ON
54SET DATEFORMAT YMD
55
56/************************
57 VARIABLE DECLARATION
58************************/
59
60 DECLARE @fso int
61 DECLARE @file int
62 DECLARE @reportfilename varchar(500)
63 DECLARE @backupfilename varchar(500)
64 DECLARE @delfilename varchar(500)
65 DECLARE @cmd varchar(650)
66 DECLARE @backupfldrorig varchar(200)
67 DECLARE @databaseorig sysname
68 DECLARE @TABLE nvarchar(600)
69 DECLARE @EXISTS varchar(5)
70 DECLARE @err int
71 DECLARE @start datetime
72 DECLARE @finish datetime
73 DECLARE @runtime datetime
74 DECLARE @output varchar(200)
75 DECLARE @errormsg varchar(210)
76 DECLARE @datepart nchar(2)
77 DECLARE @execmd nvarchar(1000)
78 DECLARE @delcmd nvarchar(1000)
79 DECLARE @exemsg varchar(8000)
80 DECLARE @filecount int ; SET @filecount = 0
81 DECLARE @delcount int ; SET @delcount = 0
82 DECLARE @hr int ; SET @hr = 0
83 DECLARE @ret int ; SET @ret = 0
84 DECLARE @cmdret int ; SET @cmdret = 0
85 DECLARE @delbkflag int ; SET @delbkflag = 0
86 DECLARE @delrptflag int ; SET @delrptflag = 0
87 DECLARE @filecrt int ; SET @filecrt = 0
88 DECLARE @user sysname ; SET @user = SUSER_SNAME()
89 DECLARE @jobdt datetime ; SET @jobdt = GETDATE()
90 DECLARE @jobstart char(12) ;
91 DECLARE @stage int ; SET @stage = 1
92 DECLARE @compressok bit ; SET @compressok = 0
93 DECLARE @versionmajor int
94 DECLARE @engineedition int
95
96 SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),':',''),4)
97 IF RIGHT(@reportfldr,1)<>'\' SET @reportfldr = @reportfldr + '\'
98 IF RIGHT(@backupfldr,1)<>'\' SET @backupfldr = @backupfldr + '\'
99 SET @backupfldrorig = @backupfldr
100 SET @databaseorig = @database
101
102 SELECT @versionmajor = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') as varchar(128)))-1) as int),
103 @engineedition = CAST(SERVERPROPERTY('EngineEdition') as int)
104 IF (@versionmajor>=10 AND @engineedition = 3) SET @compressok = 1
105
106 CREATE TABLE #files(filename varchar(255))
107 CREATE TABLE #exists(exist int,isdir int,parent int)
108 CREATE TABLE #databases(dbname sysname)
109
110/**********************************
111 INITIALIZE FSO IF @report = 1
112***********************************/
113
114 IF @report = 1
115 BEGIN
116 EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
117 IF @hr <> 0
118 BEGIN
119 EXEC sp_OAGetErrorInfo @fso
120 RAISERROR('Error creating File System Object',16,1)
121 SET @ret = 1
122 GOTO CLEANUP
123 END
124 END
125
126/************************
127 CHECK INPUT
128************************/
129
130 -- check SQL2005 or higher
131 IF @versionmajor<9
132 BEGIN
133 RAISERROR('SQL2005 OR higher IS required FOR sp_expressmaint',16,1)
134 SET @ret = 1
135 GOTO CLEANUP
136 END
137
138 -- check sysadmin
139 IF IS_SRVROLEMEMBER('sysadmin') = 0
140 BEGIN
141 RAISERROR('The current user %s IS NOT a member of the sysadmin role',16,1,@user)
142 SET @ret = 1
143 GOTO CLEANUP
144 END
145
146 -- check database exists and is online
147 IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
148 BEGIN
149 IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
150 BEGIN
151 RAISERROR('DATABASE %s IS invalid OR DATABASE STATUS IS NOT ONLINE',16,1,@database)
152 SET @ret = 1
153 GOTO CLEANUP
154 END
155 END
156
157 -- check @optype is valid
158 IF UPPER(@optype) NOT IN ('LOG','DB','DIFF','REINDEX','REORG','CHECKDB')
159 BEGIN
160 RAISERROR('%s IS NOT a valid OPTION FOR @optype',16,1,@optype)
161 SET @ret = 1
162 GOTO CLEANUP
163 END
164
165 -- check recovery mode is correct if trying log backup
166 IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
167 BEGIN
168 IF (@optype = 'LOG' and ((select recovery_model from sys.databases where name = @database) = 3))
169 BEGIN
170 RAISERROR('%s IS NOT a valid OPTION FOR DATABASE %s because it IS IN SIMPLE recovery mode',16,1,@optype,@database)
171 SET @ret = 1
172 GOTO CLEANUP
173 END
174 END
175
176 -- no log backups for system databases
177 IF @database = 'ALL_SYSTEM'
178 BEGIN
179 IF @optype = 'LOG'
180 BEGIN
181 RAISERROR('%s IS NOT a valid OPTION FOR the OPTION ALL_SYSTEM',16,1,@optype)
182 SET @ret = 1
183 GOTO CLEANUP
184 END
185 END
186
187 -- check that @backupfldr exists on the server
188 IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
189 BEGIN
190 IF @report = 1
191 BEGIN
192 EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@backupfldr
193 IF @exists <> 'True'
194 BEGIN
195 RAISERROR('The folder %s does NOT exist ON this server',16,1,@backupfldr)
196 SET @ret = 1
197 GOTO CLEANUP
198 END
199 END
200 ELSE
201 BEGIN
202 INSERT #exists
203 EXEC master.dbo.xp_fileexist @backupfldr
204 IF (SELECT MAX(isdir) FROM #exists)<>1
205 BEGIN
206 RAISERROR('The folder %s does NOT exist ON this server',16,1,@backupfldr)
207 SET @ret = 1
208 GOTO CLEANUP
209 END
210 END
211 END
212
213 -- check that @reportfldr exists on the server
214 IF @reportfldr IS NOT NULL or @report = 1
215 BEGIN
216 IF @report = 1
217 BEGIN
218 EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@reportfldr
219 IF @exists <> 'True'
220 BEGIN
221 RAISERROR('The folder %s does NOT exist ON this server',16,1,@reportfldr)
222 SET @ret = 1
223 GOTO CLEANUP
224 END
225 END
226 ELSE
227 BEGIN
228 DELETE #exists
229 INSERT #exists
230 EXEC master.dbo.xp_fileexist @reportfldr
231 IF (SELECT MAX(isdir) FROM #exists)<>1
232 BEGIN
233 RAISERROR('The folder %s does NOT exist ON this server',16,1,@reportfldr)
234 SET @ret = 1
235 GOTO CLEANUP
236 END
237 END
238 END
239
240 -- check @dbretainunit is a vaild value
241 IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
242 BEGIN
243 IF UPPER(@dbretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES')
244 BEGIN
245 RAISERROR('%s IS NOT a valid value FOR @dbretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@dbretainunit)
246 SET @ret = 1
247 GOTO CLEANUP
248 END
249 END
250
251 --check @dbretainval is a vaild value
252 IF @dbretainval<1
253 BEGIN
254 RAISERROR('%i IS NOT a valid value FOR @dbretainval (must be >0)',16,1,@dbretainval)
255 SET @ret = 1
256 GOTO CLEANUP
257 END
258
259 -- check @rptretainunit is a vaild value if present
260 IF UPPER(@rptretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES') and @rptretainunit IS NOT NULL
261 BEGIN
262 RAISERROR('%s IS NOT a valid value FOR @rptretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@rptretainunit)
263 SET @ret = 1
264 GOTO CLEANUP
265 END
266
267 --check @rptretainval is a vaild value
268 IF @rptretainval<1
269 BEGIN
270 RAISERROR('%i IS NOT a valid value FOR @rptretainval (must be >0)',16,1,@rptretainval)
271 SET @ret = 1
272 GOTO CLEANUP
273 END
274
275
276/***********************************
277 list of databases to process
278************************************/
279
280 IF @database IN ('ALL_USER','ALL_SYSTEM')
281 BEGIN
282 IF @database = 'ALL_USER'
283 INSERT #databases(dbname)
284 SELECT [name] from sys.databases where database_id > 4
285 AND (@optype <> 'LOG' OR recovery_model <> '3')
286 ELSE
287 INSERT #databases(dbname)
288 SELECT [name] from sys.databases where database_id in (1,3,4)
289 END
290 ELSE
291 INSERT #databases(dbname) SELECT @database
292
293
294/***********************************
295 INITIALIZE REPORT IF @report = 1
296************************************/
297
298 -- generate report filename
299 SELECT @reportfilename = @reportfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
300 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
301 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
302 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_'
303 WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
304 WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
305 WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
306 END + @jobstart + '.txt'
307
308 -- if no report just set @reportfilename to NULL
309 IF @report = 0 SET @reportfilename = NULL
310
311 IF @debug = 1
312 BEGIN
313 PRINT '@reportfilename = ' + ISNULL(@reportfilename,'NULL')
314 END
315
316 IF @report = 1
317 BEGIN
318 -- create report file
319 EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @reportfilename
320 IF (@hr <> 0)
321 BEGIN
322 EXEC sp_OAGetErrorInfo @fso
323 RAISERROR('Error creating log file',16,1)
324 SET @ret = 1
325 GOTO CLEANUP
326 END
327 ELSE
328 -- set global flag to indicate we have created a report file
329 SET @filecrt = 1
330
331 -- write header
332 EXEC sp_OAMethod @file,'WriteLine',NULL,''
333 SET @output = 'Expressmaint utility, Logged ON TO SQL Server [' + @@SERVERNAME + '] AS ' + '[' + @user + ']'
334 IF @debug = 1 PRINT @output
335 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
336
337
338 IF UPPER(@optype) NOT IN ('REINDEX','CHECKDB','REORG')
339 BEGIN
340 SET @output = 'Starting backup ON ' + convert(varchar(25),getdate(),100)
341 END
342 IF UPPER(@optype) = 'CHECKDB'
343 BEGIN
344 SET @output = 'Starting CheckDB ON ' + convert(varchar(25),getdate(),100)
345 END
346 IF UPPER(@optype) IN ('REINDEX','REORG')
347 BEGIN
348 SET @output = 'Starting Reindex ON ' + convert(varchar(25),getdate(),100)
349 END
350
351 IF @debug = 1 PRINT @output
352 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
353 EXEC sp_OAMethod @file,'WriteLine',NULL,''
354 END
355
356/************************
357 BACKUP ACTIONS
358************************/
359
360 IF UPPER(@optype) = 'CHECKDB' GOTO CHECK_DB
361 IF UPPER(@optype) IN ('REINDEX','REORG') GOTO REINDEX
362
363 -- if @delfirst = 1 we need to delete prior backups that qualify
364 IF @delfirst = 1 GOTO DELFIRST
365
366 -- this label is so that we can return here after deleting files if @delfirst = 1
367 DOBACKUP:
368
369 DECLARE dcur CURSOR LOCAL FAST_FORWARD
370 FOR SELECT dbname FROM #databases ORDER BY dbname
371 OPEN dcur
372 FETCH NEXT FROM dcur into @database
373 WHILE @@FETCH_STATUS=0
374 BEGIN
375
376 -- set backup start time
377 SET @start = GETDATE()
378
379 -- write to text report
380 IF @report = 1
381 BEGIN
382 SET @output = '[' + CAST(@stage as varchar(10)) + '] DATABASE ' + @database + ': ' +
383 CASE WHEN UPPER(@optype) = 'DB' THEN 'FULL Backup '
384 WHEN UPPER(@optype) = 'DIFF' THEN 'Differential Backup '
385 WHEN UPPER(@optype) = 'LOG' THEN 'Log Backup '
386 END + 'starting at ' + CONVERT(varchar(25),@start,100)
387 IF @debug = 1 PRINT @output
388 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
389 END
390
391 -- backup subfolder
392 SET @execmd = 'IF NOT EXIST "' + @backupfldrorig + REPLACE(@database,'''','') + '\" MKDIR "' + @backupfldrorig + REPLACE(@database,'''','') + '\"'
393 EXEC master.dbo.xp_cmdshell @execmd,no_output
394 SET @backupfldr = @backupfldrorig + REPLACE(@database,'''','') + '\'
395
396 SELECT @backupfilename = @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
397 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
398 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
399 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
400 END + @jobstart +
401 CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
402
403 /************************
404 FULL BACKUP
405 ************************/
406
407 IF UPPER(@optype) = 'DB'
408 BEGIN
409
410 IF @compression=1 AND @compressok=1
411 SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
412 ' WITH COMPRESSION' + CASE WHEN @backupwith IS NULL THEN '' ELSE (',' + @backupwith) END
413 ELSE
414 SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
415 CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
416
417 IF @debug = 1 PRINT 'FULL BACKUP : ' + @execmd
418
419 BEGIN TRY
420
421 EXEC(@execmd)
422
423 END TRY
424 BEGIN CATCH -- backup failure
425 SELECT @err = @@ERROR,@ret = @err
426 SELECT @errormsg = 'Full backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
427 SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
428 IF @debug = 1 PRINT @output
429 IF @report = 1
430 BEGIN
431 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
432 SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
433 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
434 EXEC sp_OAMethod @file,'WriteLine',NULL,''
435 END
436 CLOSE dcur
437 DEALLOCATE dcur
438 GOTO CLEANUP
439
440 END CATCH
441
442 -- backup success
443 SET @finish = GETDATE()
444 SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
445 IF @debug = 1 PRINT @output
446 IF @report = 1
447 BEGIN
448 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
449 END
450
451 --calculate backup runtime
452 SET @runtime = (@finish - @start)
453 SET @output = SPACE(4) + 'Full database backup completed in '
454 + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
455 + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
456 + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
457 IF @debug = 1 PRINT @output
458 IF @report = 1
459 BEGIN
460 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
461 EXEC sp_OAMethod @file,'WriteLine',NULL,''
462 END
463
464 END
465
466
467 /************************
468 DIFFERENTIAL BACKUP
469 ************************/
470
471 IF UPPER(@optype) = 'DIFF'
472 BEGIN
473
474 IF @compression=1 AND @compressok=1
475 SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
476 N' WITH DIFFERENTIAL,COMPRESSION' + CASE WHEN @backupwith IS NULL THEN N'' ELSE (N',' + @backupwith) END
477 ELSE
478 SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + N''' WITH DIFFERENTIAL' +
479 CASE WHEN @backupwith IS NULL THEN N'' ELSE (N',' + @backupwith) END
480
481 IF @debug = 1 PRINT 'DIFFERENTIAL BACKUP : ' + @execmd
482
483 BEGIN TRY
484
485 EXEC(@execmd)
486
487 END TRY
488 BEGIN CATCH -- backup failure
489
490 SELECT @err = @@ERROR,@ret = @err
491 SELECT @errormsg = 'Differential backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
492 SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
493 IF @debug = 1 PRINT @output
494 IF @report = 1
495 BEGIN
496 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
497 SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
498 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
499 END
500 CLOSE dcur
501 DEALLOCATE dcur
502 GOTO CLEANUP
503
504 END CATCH
505
506 -- backup success
507 SET @finish = GETDATE()
508 SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
509 IF @debug = 1 PRINT @output
510 IF @report = 1
511 BEGIN
512 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
513 END
514
515 --calculate backup runtime
516 SET @runtime = (@finish - @start)
517 SET @output = SPACE(4) + 'Differential database backup completed in '
518 + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
519 + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
520 + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
521 IF @debug = 1 PRINT @output
522 IF @report = 1
523 BEGIN
524 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
525 EXEC sp_OAMethod @file,'WriteLine',NULL,''
526 END
527
528 END
529
530 /************************
531 LOG BACKUP
532 ************************/
533
534 IF UPPER(@optype) = 'LOG'
535 BEGIN
536
537 IF @compression=1 AND @compressok=1
538 SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
539 ' WITH COMPRESSION' + CASE WHEN @backupwith IS NULL THEN '' ELSE (',' + @backupwith) END
540 ELSE
541 SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
542 CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
543
544 IF @debug = 1 PRINT 'LOG BACKUP : ' + @execmd
545
546 BEGIN TRY
547
548 EXEC(@execmd)
549
550 END TRY
551 BEGIN CATCH -- backup failure
552
553 SELECT @err = @@ERROR,@ret = @err
554 SELECT @errormsg = 'Log backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
555 SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
556 IF @debug = 1 PRINT @output
557 IF @report = 1
558 BEGIN
559 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
560 SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
561 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
562 END
563 CLOSE dcur
564 DEALLOCATE dcur
565 GOTO CLEANUP
566
567 END CATCH
568
569 -- backup success
570 SET @finish = GETDATE()
571 SET @output = SPACE(4) + 'Log backed up to ' + @backupfilename
572 IF @debug = 1 PRINT @output
573 IF @report = 1
574 BEGIN
575 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
576 END
577
578 --calculate backup runtime
579 SET @runtime = (@finish - @start)
580 SET @output = SPACE(4) + 'Log backup completed in '
581 + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
582 + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
583 + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
584 IF @debug = 1 PRINT @output
585 IF @report = 1
586 BEGIN
587 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
588 EXEC sp_OAMethod @file,'WriteLine',NULL,''
589 END
590
591 END
592
593 SET @stage = (@stage + 1)
594
595 FETCH NEXT FROM dcur into @database
596 END
597
598 CLOSE dcur
599 DEALLOCATE dcur
600
601 /************************
602 VERIFY BACKUP
603 ************************/
604
605 IF @verify = 1
606 BEGIN
607
608 DECLARE dcur CURSOR LOCAL FAST_FORWARD
609 FOR SELECT dbname FROM #databases ORDER BY dbname
610 OPEN dcur
611 FETCH NEXT FROM dcur into @database
612 WHILE @@FETCH_STATUS=0
613 BEGIN
614
615 SELECT @backupfilename = @backupfldrorig + REPLACE(@database,'''','') + '\' + REPLACE(REPLACE(@database,' ','_'),'''','_') +
616 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
617 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
618 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
619 END + @jobstart +
620 CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
621
622 SET @start = GETDATE()
623
624 -- write to text report
625 IF @report = 1
626 BEGIN
627 EXEC sp_OAMethod @file,'WriteLine',NULL,''
628 SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Verify Backup File...'
629 IF @debug = 1 PRINT @output
630 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
631 END
632
633 SET @execmd = N'RESTORE VERIFYONLY FROM DISK = ''' + @backupfilename + '''' +
634 CASE WHEN @verifywith IS NULL THEN '' ELSE (' WITH ' + @verifywith) END
635
636 BEGIN TRY
637
638 EXEC(@execmd)
639
640 END TRY
641 BEGIN CATCH
642
643 SELECT @err = @@ERROR,@ret = @err
644 SET @errormsg = 'Verify of ' + @backupfilename + ' failed with Native Error : ' + CAST(@err as varchar(10))
645 SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
646 IF @debug = 1 PRINT @output
647 IF @report = 1
648 BEGIN
649 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
650 END
651 CLOSE dcur
652 DEALLOCATE dcur
653 GOTO CLEANUP
654
655 END CATCH
656
657 -- verify success
658 SET @finish = GETDATE()
659 SET @output = SPACE(4) + 'Backup file ' + @backupfilename + ' verified'
660 IF @debug = 1 PRINT @output
661
662 IF @report = 1
663 BEGIN
664 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
665 END
666
667 --calculate verify runtime
668 SET @runtime = (@finish - @start)
669 SET @output = SPACE(4) + 'Verify backup completed in '
670 + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
671 + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
672 + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
673 IF @debug = 1 PRINT @output
674 IF @report = 1
675 BEGIN
676 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
677 END
678
679 SET @stage = (@stage + 1)
680 FETCH NEXT FROM dcur into @database
681 END
682
683 CLOSE dcur
684 DEALLOCATE dcur
685 END
686
687/************************
688 DELETE OLD FILES
689************************/
690
691
692 -- we have already deleted files so skip to the end
693 IF @delfirst = 1 GOTO CLEANUP
694
695 -- this label is so that we can delete files prior to backup if @delfirst = 1
696 DELFIRST:
697
698 /************************
699 DELETE OLD BACKUPS
700 ************************/
701
702 SET @datepart = CASE
703 WHEN UPPER(@dbretainunit) = 'MINUTES' THEN N'mi'
704 WHEN UPPER(@dbretainunit) = 'HOURS' THEN N'hh'
705 WHEN UPPER(@dbretainunit) = 'DAYS' THEN N'dd'
706 WHEN UPPER(@dbretainunit) = 'WEEKS' THEN N'ww'
707 WHEN UPPER(@dbretainunit) = 'MONTHS' THEN N'mm'
708 END
709
710 IF @debug = 1 PRINT '@datepart for backups = ' + @datepart
711
712 -- write to text report
713 IF @report = 1
714 BEGIN
715 EXEC sp_OAMethod @file,'WriteLine',NULL,''
716 END
717 SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files...'
718 IF @debug = 1 PRINT @output
719 IF @report = 1
720 BEGIN
721 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
722 END
723
724 DECLARE dcur CURSOR LOCAL FAST_FORWARD
725 FOR SELECT dbname FROM #databases ORDER BY dbname
726 OPEN dcur
727 FETCH NEXT FROM dcur into @database
728 WHILE @@FETCH_STATUS=0
729 BEGIN
730
731 SET @backupfldr = + @backupfldrorig + REPLACE(@database,'''','') + '\'
732 SELECT @backupfilename = @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
733 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
734 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
735 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
736 END + @jobstart +
737 CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
738
739 -- load files in @backupfldr
740 IF @checkattrib = 1
741 SET @cmd = 'dir /B /A-D-A /OD "' + @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
742 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
743 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
744 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
745 CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
746 ELSE
747 SET @cmd = 'dir /B /A-D /OD "' + @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
748 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
749 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
750 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
751 CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
752
753 IF @debug = 1 PRINT '@cmd = ' + @cmd
754
755 DELETE #files
756 INSERT #files EXEC master.dbo.xp_cmdshell @cmd
757 DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,''),'nothing')
758
759 IF @debug = 1 SELECT * FROM #files
760
761 -- get count of files that match pattern
762 SELECT @filecount = COUNT(*) from #files
763 WHERE PATINDEX('%File Not Found%',filename) = 0
764 AND PATINDEX('%The system cannot find%',filename) = 0
765
766 -- remove files that don't meet retention criteria if there are any files that match pattern
767 IF UPPER(@dbretainunit) <> 'COPIES'
768 BEGIN
769 IF @filecount>0
770 BEGIN
771 SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +
772 'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
773 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
774 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
775 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
776 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''
777
778 IF @debug = 1 PRINT '@delcmd=' + @delcmd
779 EXEC master.dbo.sp_executesql @delcmd
780
781 SELECT @delcount = COUNT(*) from #files
782 END
783 ELSE
784 BEGIN
785 SELECT @delcount = 0
786 END
787 END
788 ELSE -- number of copies not date based (include current backup that's not in #files)
789 BEGIN
790 IF @filecount>0
791 BEGIN
792 IF @dbretainval>1
793 BEGIN
794 SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST((@dbretainval-1) as nvarchar(10)) +
795 N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'
796
797 IF @debug = 1 PRINT '@delcmd=' + @delcmd
798 EXEC master.dbo.sp_executesql @delcmd
799 END
800
801 SELECT @delcount = COUNT(*) from #files
802
803 END
804 ELSE
805 BEGIN
806 SELECT @delcount = 0
807 END
808 END
809
810 IF @debug = 1 PRINT '@delcount = ' + STR(@delcount)
811
812 -- if there are any matching files
813 IF @filecount>0
814 BEGIN
815 -- are there any files that need deleting
816 IF @delcount>0
817 BEGIN
818 DECLARE FCUR CURSOR FORWARD_ONLY FOR
819 SELECT * FROM #files
820 OPEN FCUR
821 FETCH NEXT FROM FCUR INTO @delfilename
822 WHILE @@FETCH_STATUS=0
823 BEGIN
824 SET @cmd = 'DEL /Q "' + @backupfldr + @delfilename + '"'
825 EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
826
827 -- log failure to delete but don't abort procedure
828 IF @cmdret<>0
829 BEGIN
830 SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @backupfldr + @delfilename + ' ***'
831 IF @debug = 1 PRINT @output
832 IF @report = 1
833 BEGIN
834 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
835 END
836 SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
837 END
838 ELSE
839 BEGIN
840 SET @output = SPACE(4) + 'Deleted file ' + @backupfldr + @delfilename
841 IF @debug = 1 PRINT @output
842 IF @report = 1
843 BEGIN
844 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
845 END
846 END
847
848 FETCH NEXT FROM FCUR INTO @delfilename
849 END
850 CLOSE FCUR
851 DEALLOCATE FCUR
852 END
853 END
854
855 -- write to text report
856 SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
857 IF @debug = 1 PRINT @output
858 IF @report = 1
859 BEGIN
860 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
861 EXEC sp_OAMethod @file,'WriteLine',NULL,''
862 END
863
864 FETCH NEXT FROM dcur into @database
865 END
866
867 CLOSE dcur
868 DEALLOCATE dcur
869
870 -- clear temporary table and variables
871 DELETE #files
872 SET @cmd = ''
873 SET @delcmd = ''
874 SET @delfilename = ''
875 SET @datepart = ''
876 SET @filecount = 0
877 SET @delcount = 0
878 SET @cmdret = 0
879 SET @stage = @stage + 1
880
881
882 /************************
883 DELETE OLD REPORTS
884 ************************/
885
886 DELREPORTS:
887
888 IF @rptretainunit IS NOT NULL
889 BEGIN
890 SET @datepart = CASE
891 WHEN UPPER(@rptretainunit) = 'MINUTES' THEN N'mi'
892 WHEN UPPER(@rptretainunit) = 'HOURS' THEN N'hh'
893 WHEN UPPER(@rptretainunit) = 'DAYS' THEN N'dd'
894 WHEN UPPER(@rptretainunit) = 'WEEKS' THEN N'ww'
895 WHEN UPPER(@rptretainunit) = 'MONTHS' THEN N'mm'
896 END
897
898 IF @debug = 1 PRINT '@datepart for reports = ' + @datepart
899
900 -- write to text report
901 SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files...'
902 IF @debug = 1 PRINT @output
903 IF @report = 1
904 BEGIN
905 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
906 END
907
908 -- load files in @reportfldr
909 SET @cmd = 'dir /B /A-D /OD "' + @reportfldr + REPLACE(REPLACE(@databaseorig,' ','_'),'''','') +
910 CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
911 WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
912 WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
913 WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
914 WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
915 WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_' END + '*.txt"'
916
917 IF @debug = 1 PRINT '@cmd = ' + @cmd
918
919 INSERT #files EXEC master.dbo.xp_cmdshell @cmd
920 DELETE #files WHERE filename IS NULL
921
922 IF @debug = 1 SELECT * FROM #files
923
924 -- get count of files that match pattern
925 SELECT @filecount = COUNT(*) from #files
926 WHERE PATINDEX('%File Not Found%',filename) = 0
927 AND PATINDEX('%The system cannot find%',filename) = 0
928
929 -- remove files that don't meet retention criteria if there are any files that match pattern
930 IF UPPER(@rptretainunit) <> 'COPIES'
931 BEGIN
932 IF @filecount>0
933 BEGIN
934 SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@rptretainval as nvarchar(10)) + N',' +
935 'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
936 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
937 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
938 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
939 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''
940
941 IF @debug = 1 PRINT '@delcmd=' + @delcmd
942 EXEC master.dbo.sp_executesql @delcmd
943
944 SELECT @delcount = COUNT(*) from #files
945 END
946 ELSE
947 BEGIN
948 SELECT @delcount = 0
949 END
950 END
951 ELSE -- number of copies not date based
952 BEGIN
953 IF @filecount>0
954 BEGIN
955 SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST(@rptretainval as nvarchar(10)) +
956 N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'
957
958 IF @debug = 1 PRINT '@delcmd=' + @delcmd
959 EXEC master.dbo.sp_executesql @delcmd
960
961 SELECT @delcount = COUNT(*) from #files
962 END
963 ELSE
964 BEGIN
965 SELECT @delcount = 0
966 END
967 END
968
969 IF @debug = 1 PRINT STR(@delcount)
970
971 -- if there are any matching files
972 IF @filecount>0
973 BEGIN
974 -- are there any files that need deleting
975 IF @delcount>0
976 BEGIN
977 DECLARE FCUR CURSOR FORWARD_ONLY FOR
978 SELECT * FROM #files
979 OPEN FCUR
980 FETCH NEXT FROM FCUR INTO @delfilename
981 WHILE @@FETCH_STATUS=0
982 BEGIN
983 SET @cmd = 'DEL /Q "' + @reportfldr + @delfilename + '"'
984 EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
985
986 -- log failure to delete but don't abort procedure
987 IF @cmdret<>0
988 BEGIN
989
990 SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @reportfldr + @delfilename + ' ***'
991 IF @debug = 1 PRINT @output
992 IF @report = 1
993 BEGIN
994 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
995 END
996 SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
997 END
998 BEGIN
999 SET @output = SPACE(4) + 'Deleted file ' + @reportfldr + @delfilename
1000 IF @debug = 1 PRINT @output
1001 IF @report = 1
1002 BEGIN
1003 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1004 END
1005 END
1006
1007 FETCH NEXT FROM FCUR INTO @delfilename
1008 END
1009 CLOSE FCUR
1010 DEALLOCATE FCUR
1011 END
1012 END
1013
1014 -- write to text report
1015 SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
1016 IF @debug = 1 PRINT @output
1017 IF @report = 1
1018 BEGIN
1019 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1020 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1021 END
1022
1023 -- update stage
1024 SET @stage = @stage + 1
1025 END
1026 -- if we got here due to @delfirst = 1 go back and do the backups
1027 IF @delfirst = 1
1028 GOTO DOBACKUP
1029 ELSE
1030 GOTO CLEANUP
1031
1032
1033/************************
1034 CHECKDB
1035************************/
1036
1037 CHECK_DB:
1038
1039 IF @optype = 'CHECKDB'
1040 BEGIN
1041
1042 DECLARE dcur CURSOR LOCAL FAST_FORWARD
1043 FOR SELECT dbname FROM #databases ORDER BY dbname
1044 OPEN dcur
1045 FETCH NEXT FROM dcur into @database
1046 WHILE @@FETCH_STATUS=0
1047 BEGIN
1048
1049 -- write to text report
1050 IF @report = 1
1051 BEGIN
1052 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1053 SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Check Data and Index Linkage...'
1054 IF @debug = 1 PRINT @output
1055 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1056 END
1057
1058 -- set backup start time
1059 SET @start = GETDATE()
1060
1061 SET @execmd = N'DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS'
1062 IF @debug = 1 PRINT 'DBCC Command : ' + @execmd
1063
1064 BEGIN TRY
1065
1066 EXEC(@execmd)
1067
1068 END TRY
1069 BEGIN CATCH
1070
1071 SELECT @err = @@ERROR,@ret = @err
1072 SET @errormsg = 'CheckDB of ' + @database + ' failed with Native Error : ' + CAST(@err as varchar(10))
1073 SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
1074 PRINT @output
1075 IF @report = 1
1076 BEGIN
1077 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1078 END
1079 CLOSE dcur
1080 DEALLOCATE dcur
1081 GOTO CLEANUP
1082
1083 END CATCH
1084
1085 SET @finish = GETDATE()
1086
1087 --calculate checkdb runtime
1088 SET @runtime = (@finish - @start)
1089 SET @output = SPACE(4) + 'CheckDB completed in '
1090 + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
1091 + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
1092 + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
1093 IF @debug = 1 PRINT @output
1094 IF @report = 1
1095 BEGIN
1096 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1097 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1098 END
1099 SET @stage = (@stage + 1)
1100 FETCH NEXT FROM dcur into @database
1101
1102 END
1103
1104 CLOSE dcur
1105 DEALLOCATE dcur
1106
1107 -- delete reports
1108 IF @report = 1
1109 BEGIN
1110 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1111 END
1112 GOTO DELREPORTS
1113 END
1114
1115/************************
1116 REINDEX/REORG
1117************************/
1118
1119 REINDEX:
1120
1121 IF @optype in ('REINDEX','REORG')
1122 BEGIN
1123
1124 DECLARE dcur CURSOR LOCAL FAST_FORWARD
1125 FOR SELECT dbname FROM #databases ORDER BY dbname
1126 OPEN dcur
1127 FETCH NEXT FROM dcur into @database
1128 WHILE @@FETCH_STATUS=0
1129 BEGIN
1130
1131 -- write to text report
1132 IF @report = 1
1133 BEGIN
1134 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1135 IF @optype = 'REINDEX'
1136 SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Rebuild (using original fillfactor)...'
1137 ELSE
1138 SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Reorganize...'
1139
1140 IF @debug = 1 PRINT @output
1141 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1142 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1143 END
1144
1145 -- set start time
1146 SET @start = GETDATE()
1147
1148 -- all user tables
1149 CREATE TABLE #tables(tablename sysname)
1150 EXEC(N'INSERT #tables(tablename) SELECT DISTINCT(''['' + s.[name] + ''].['' + t.[name] + '']'') FROM [' + @database + N'].sys.tables t ' +
1151 N'JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ' +
1152 N'JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ' +
1153 N'WHERE t.is_ms_shipped = 0 AND i.type>0')
1154
1155 DECLARE tcur CURSOR LOCAL FAST_FORWARD
1156 FOR SELECT tablename FROM #tables ORDER BY tablename
1157 OPEN tcur
1158 FETCH NEXT FROM tcur INTO @table
1159 WHILE @@FETCH_STATUS = 0
1160 BEGIN
1161
1162 IF @report = 1
1163 BEGIN
1164 IF @optype = 'REINDEX'
1165 SET @output = SPACE(4) + N'Rebuilding indexes for table ' + @table
1166 ELSE
1167 SET @output = SPACE(4) + N'Reorganizing indexes for table ' + @table
1168
1169 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1170 END
1171
1172
1173 IF @optype = 'REINDEX'
1174 SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REBUILD'
1175 ELSE
1176 SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REORGANIZE'
1177
1178 IF @debug = 1 PRINT 'Reindex Command : ' + @execmd
1179
1180 BEGIN TRY
1181
1182 EXEC(@execmd)
1183
1184 END TRY
1185 BEGIN CATCH
1186
1187 SELECT @err = @@ERROR,@ret = @err
1188 SET @errormsg = 'Rebuild of indexes on [' + @database + N'].' + @table + ' failed with Native Error : ' + CAST(@err as varchar(10))
1189 SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
1190 PRINT @output
1191 IF @report = 1
1192 BEGIN
1193 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1194 END
1195 CLOSE tcur
1196 DEALLOCATE tcur
1197 DROP TABLE #tables
1198 GOTO CLEANUP
1199
1200 END CATCH
1201
1202 FETCH NEXT FROM tcur INTO @table
1203 END
1204
1205 CLOSE tcur
1206 DEALLOCATE tcur
1207
1208 SET @finish = GETDATE()
1209
1210 --calculate runtime
1211 SET @runtime = (@finish - @start)
1212 SET @output = SPACE(4) + 'Index maintenance completed in '
1213 + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
1214 + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
1215 + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
1216 IF @debug = 1 PRINT @output
1217 IF @report = 1
1218 BEGIN
1219 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1220 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1221 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1222 END
1223
1224 DROP TABLE #tables
1225
1226 SET @stage = (@stage + 1)
1227 FETCH NEXT FROM dcur into @database
1228
1229 END
1230
1231 CLOSE dcur
1232 DEALLOCATE dcur
1233
1234 -- delete reports
1235 GOTO DELREPORTS
1236 END
1237
1238
1239/************************
1240 CLEAN UP
1241************************/
1242
1243 CLEANUP:
1244
1245 DROP TABLE #files
1246 DROP TABLE #exists
1247 DROP TABLE #databases
1248
1249 -- if we encountered errors deleting old backups return failure
1250 IF @delbkflag<>0
1251 BEGIN
1252 SET @errormsg = 'Expressmaint encountered errors deleting old backup files' + CHAR(13)
1253 + CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
1254 RAISERROR(@errormsg,16,1)
1255 SET @ret = 1
1256 END
1257
1258 -- if we encountered errors deleting old reports return failure
1259 IF (@delrptflag<>0 AND @delbkflag = 0)
1260 BEGIN
1261 SET @errormsg = 'Expressmaint encountered errors deleting old report files' + CHAR(13)
1262 + CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
1263 RAISERROR(@errormsg,16,1)
1264 SET @ret = 1
1265 END
1266
1267 -- if we created a file make sure we write trailer and destroy object
1268 IF @filecrt = 1
1269 BEGIN
1270 -- write final part of report
1271 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1272 SET @output = 'Expressmaint processing finished at ' + CONVERT(varchar(25),GETDATE(),100)
1273 + ' (Return Code : ' + CAST(@ret as varchar(10)) + ')'
1274 IF @debug = 1 PRINT @output
1275 EXEC sp_OAMethod @file,'WriteLine',NULL,@output
1276 EXEC sp_OAMethod @file,'WriteLine',NULL,''
1277
1278 -- destroy file object
1279 EXEC @hr=sp_OADestroy @file
1280 IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
1281 END
1282
1283 IF @report = 1
1284 BEGIN
1285 EXEC @hr=sp_OADestroy @fso
1286 IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
1287 END
1288
1289RETURN @ret
1290GO
1291
1292PRINT 'Stored Procedure created successfully'