This script written in vbscript generates a csv file with backup report (full/diff/tran log) for all databases on all servers in the company(server list to be provided in a text file)
' Author - Pradeep Singh
' email - pradeep_singh37@yahoo.com
'Prerequisites:
'1. c:\Workfolder - this folder should be created manually
'2. a file named servers.txt in that folder containing list of servers
'3. osql should be present on the system
'4. tested on 2000 and 2005 servers
'5. a csv file will be created in c:\workfolder with backup details
<HTML>
<HEAD>
<title>Backup Audit Report</title>
<body >
<script language="vbscript">
<!--
'msgbox "Please create a folder c:\WorkFolder and put the list of servers in a file with the name servers.txt and then hit OK to continue!!"
dim servers, filesys ,objFile
set objFSO=CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\WorkFolder\Servers.txt", 1)
Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
'document.write (arrFileLines(i))
i = i + 1
Loop
objFile.Close
For l = LBound(arrFileLines) to UBound(arrFileLines) Step 1
'Document.write arrfilelines(l)
' 1. check for servers connectivity
Set WshShell = CreateObject("WScript.Shell")
ostr = "osql -Q " & """select name from sysdatabases where dbid=1 """ & " -E -S" & arrfilelines(l) & " -o " & "c:\Workfolder\output_" & Replace(arrfilelines(l), "\", "-") & ".txt "
WshShell.Run ostr
Next
For i = 1 To 1000000
For j = 1 To 100
next
Next
' Create a file for output here!!
Dim Bkp
Set Bkp=objfso.createTextfile ("C:\WorkFolder\BackupReport.csv",True)
str= "Instance Name,Database Name,Size,Owner,Status,UserAccess,Recovery Model,Full Backup Path,Last Full Backup Start, Last Full Backup End,Delay in days, Full backup duration,2nd Last Full Backup Start ,2nd Last Full Backup End, Diff Backup Path,Last Diff Backup start,Last Diff Backup End,Delay in Hours, Diff backup duration,2nd Last Diff Backup Start,2nd Last Diff Backup End, Tran Log Backup Path,Last Tran log Backup Start,Last Tran log Backup End,Hours since last Tran Log Backup, Tran Log Backup Duration,2nd Last Tran log Backup Start,2nd Last Tran log Backup End, "
bkp.writeline str
Dim objConnection,found1,x
Set objConnection = CreateObject("ADODB.Connection")
For l = LBound(arrFileLines) to uBound(arrFileLines) Step 1
' read the files one by one
' open only if it has master in it.
Dim objServers, fln
fln="c:\Workfolder\output_" & Replace(arrfilelines(l), "\", "-") & ".txt "
'Document.write fln
Set objservers=objFSO.OpenTextFile(fln, 1)
found1="a"
Do Until objservers.AtEndOfStream
x= objservers.ReadLine
If InStr(1, x, "master")>1 Then found1="b"
Loop
If found1="b" then
If objConnection.state=1 Then objConnection.close
objConnection.ConnectionString = "Provider=SQLOLEDB; Data Source=" & arrfilelines(l) & ";Initial Catalog=master;Integrated Security=SSPI;"
objConnection.ConnectionTimeout = 0
objConnection.Open
objConnection.Execute "create table #DB_Info (name varchar(255),db_size varchar(20), Owner varchar(255),db_id varchar(4),created datetime,Status varchar(500),compatibility varchar(10))"
objconnection.execute "insert into #DB_Info exec sp_helpdb"
Dim rs, rs2, sql, dt
Set rs=CreateObject("ADODB.Recordset")
Set rs2=CreateObject("ADODB.Recordset")
sql="select * from #DB_Info"
rs.open sql, objconnection, 3,3
'Document.write "<BR>"
While Not rs.eof
'Document.write rs.fields(1)
str=arrfilelines(l)+ ","
str=str+Trim(rs(0))+ ","
str=str+Trim(rs(1))+ ","
str=str+Trim(rs(2))+ ","
str=str+Mid(rs(5), InStr(1, rs(5), "Status=") + 7, (InStr(1, rs(5), "Update")) - (InStr(1, rs(5), "Status")) - 9)+ ","
str=str+ Mid(rs(5), InStr(1, rs(5), "UserAccess=") + 11, (InStr(1, rs(5), "UserAccess")) - (InStr(1, rs(5), "Updateability")) - 16)+ ","
str=str+ Mid(rs(5), InStr(1, rs(5), "Recovery=") + 9, (InStr(1, rs(5), "Version")) - (InStr(1, rs(5), "Recovery")) - 10)
sql="select top 2 physical_device_name, backup_start_date, backup_finish_date, cast(datediff(day, backup_start_date, getdate())as varchar(20)) + ' Days' Delay,cast(datediff(hour, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(minute, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(second, backup_start_date, backup_finish_date) as varchar(20)) Duration,type from msdb..backupmediafamily,msdb..backupset where msdb..backupmediafamily.media_set_id=msdb..backupset.media_set_id and type='D' and database_name='" & rs(0) & "' order by backup_set_id desc"
'sql = "select top 2 backup_finish_date from msdb..backupset where database_name='" & rs(0) & "' and type='D' order by backup_set_id desc"
'Document.write sql
'rs.close
If rs2.state=1 Then rs2.close
rs2.open sql, objconnection, 3,3
If rs2.RecordCount > 0 Then dt = CStr(rs2(0))+","+CStr(rs2(1))+","+CStr(rs2(2))+","+CStr(rs2(3))+","+CStr(rs2(4)) Else dt = "NULL,NULL,NULL,NULL,NULL"
str=str+ dt+ ","
If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(1))+","+CStr(rs2(2)) Else dt = "NULL,NULL"
str=str+ dt+ ", "
sql="select top 2 physical_device_name, backup_start_date, backup_finish_date, cast(datediff(hour, backup_start_date, getdate())as varchar(20)) + ' Hours' Delay,cast(datediff(hour, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(minute, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(second, backup_start_date, backup_finish_date) as varchar(20)) Duration,type from msdb..backupmediafamily,msdb..backupset where msdb..backupmediafamily.media_set_id=msdb..backupset.media_set_id and type='I' and database_name='" & rs(0) & "' order by backup_set_id desc"
'sql = "select top 2 backup_finish_date from msdb..backupset where database_name='" & rs(0) & "' and type='I' order by backup_set_id desc"
'Document.write sql
'rs.close
If rs2.state=1 Then rs2.close
rs2.open sql, objconnection, 3,3
If rs2.RecordCount > 0 Then dt = CStr(rs2(0))+","+CStr(rs2(1))+","+CStr(rs2(2))+","+CStr(rs2(3))+","+CStr(rs2(4)) Else dt = "NULL,NULL,NULL,NULL,NULL"
str=str+ dt+ ","
If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(1))+","+CStr(rs2(2)) Else dt = "NULL,NULL"
str=str+ dt+ ", "
'If rs2.RecordCount > 0 Then dt = CStr(rs2(0)) Else dt = "NULL"
'str=str+ dt+ ","
'
' If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(0)) Else dt = "NULL"
' str=str+ dt+ ", "
sql="select top 2 physical_device_name, backup_start_date, backup_finish_date, cast(datediff(hour, backup_start_date, getdate())as varchar(20)) + ' Hours' Delay,cast(datediff(hour, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(minute, backup_start_date, backup_finish_date) as varchar(20))+':'+cast(datediff(second, backup_start_date, backup_finish_date) as varchar(20)) Duration,type from msdb..backupmediafamily,msdb..backupset where msdb..backupmediafamily.media_set_id=msdb..backupset.media_set_id and type='L' and database_name='" & rs(0) & "' order by backup_set_id desc"
'sql = "select top 2 backup_finish_date from msdb..backupset where database_name='" & rs(0) & "' and type='L' order by backup_set_id desc"
'Document.write sql
'rs.close
If rs2.state=1 Then rs2.close
rs2.open sql, objconnection, 3,3
If rs2.RecordCount > 0 Then dt = CStr(rs2(0))+","+CStr(rs2(1))+","+CStr(rs2(2))+","+CStr(rs2(3))+","+CStr(rs2(4)) Else dt = "NULL,NULL,NULL,NULL,NULL"
str=str+ dt+ ","
If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(1))+","+CStr(rs2(2)) Else dt = "NULL,NULL"
str=str+ dt+ ", "
'If rs2.RecordCount > 0 Then dt = CStr(rs2(0)) Else dt = "NULL"
'str=str+ dt+ ","
' If rs2.RecordCount > 1 Then rs2.movenext:dt = CStr(rs2(0)) Else dt = "NULL"
' str=str+ dt+ ", "
document.write str
bkp.WriteLine str
document.write "<br>"
'WshShell.Run ostr 'run batch file, Use your absolute path here
rs.movenext
Wend
End if
Next
-->
Function ConnectToServer()
End Function
</script>
</HEAD>
</HTML>
This article from Warren Campbell shows a process to recreate permissions in development environments after restoring a database from a production instance.
2011-12-05
6,067 reads
ckup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. This document provides guidance related to tuning options for backup performance. All of the information and test results presented here were done specifically by using the backup compression feature of SQL Server 2008; however, they apply broadly to any backup scenario whether backup compression is used or not
2008-05-12
2,214 reads
Rodney Landrum presents a creative solution for dynamic reporting across all of his SQL Servers, based on use of Red Gate's SQL Backup and SQL Multi Script.
2008-01-22
2,839 reads
You have many SQL Servers to administer and you just want to know when the last backup was taken for each database on each instance. This stored procedure will help.
2006-04-13
4,271 reads
This is one of the very useful features in SQL Server 2000 and if you ever have the need to perform this actiom, you will appreciate the knowledge in this article.
2005-06-15
13,155 reads