BuckTheBug.net

IT › Helios ›

Zálohování na SQL 2005 EE pomocí VBS skriptu

Buck the Bug

To, že u Express Editon SQL Serveru 2005 chybí Agent, je věc známá a smůtná. Zálohování lze řešit různě, např. pomocí naplánovaného T-SQL skriptu v konzoli osql.exe. Další cestou je použití SQL-DMO objektů. Na ty lze "sahat" z různých prostředí, např. z VBS. Pěkný příklad jsem našel na databasejournal.com a trochu rozšířil.
Testováno na Windows XP a Windows Server 2003 (bez R2).

' from http://www.databasejournal.com/features/mssql/article.php/1489681 '

Dim oServer, oDatabase, oBackup

Dim sSQLInstance
Dim sBAKFilePath, sZIPFilePath, sBAKFile
Dim sNamePatern
Dim sTimeSuffix

'change this to where ever you want to place your backup files, no trailing
'backslash, we add it below
sBAKFilePath = "C:\Backup"
iBAKDirExpire = 3
sZIPFilePath = "C:\Backup\ZIPs"
iZIPDirExpire = 30
sSQLInstance = ".\sql2005"
sNamePatern = "Demo2007"

'---------------------------------------------------------------------------------------
sTimeSuffix = Year(now) & right("0"&(month(now)),2) & right("0"&(Day(now)),2)
sTimeSuffix = sTimeSuffix & "_" & right("0"&(hour(now)),2) & right("0"&(minute(now)),2)& right("0"&(second(now)),2)

' for zipping
Set oShellApp = CreateObject("Shell.Application")
Set oShell = WScript.CreateObject("WScript.Shell")

'we need a backup object in addition to the sqlserver one
Set oServer = CreateObject("SQLDmo.SqlServer")
Set oBackup = CreateObject("SQLDmo.Backup")

oServer.LoginSecure = True
oServer.Connect sSQLInstance

'this will do a full backup of database by "Patern" to a file (not a
'device

For Each oDatabase In oServer.Databases
If instr(UCase(oDatabase.Name),ucase(sNamePatern)) > 0 Then
oBackup.Database = oDatabase.Name
'remove any previous backup - same as using T-SQL with init
oBackup.Initialize = True
'dynamically create the name of the backup file
sBAKFile = sBAKFilePath & "\" & oDatabase.Name & "_"& sTimeSuffix & ".bak"
oBackup.Files = sBAKFile

'set the action property as needed
'0 = Full backup
'1 = Differential
'2 = Specified files only
'3 = Log backup
oBackup.Action = 0
oBackup.BackupSetName = "VBS automatic backup"
oBackup.SQLBackup oServer

'zip file
stm_zip sBAKFile, sZIPFilePath & "\" & oDatabase.Name & "_"& sTimeSuffix & ".zip"
End if
Next

'clean up
Set oBackup = Nothing
oServer.DisConnect
Set oServer = Nothing

'Delete old files
Call stm_deleteold(sBAKFilePath, iBAKDirExpire)
Call stm_deleteold(sZIPFilePath, iZIPDirExpire)

Function stm_zip(source, zipfile)
'http://www.tek-tips.com/viewthread.cfm?qid=1231429&page=7
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(zipfile, 8, vbtrue)
BlankZip = "PK" & Chr(5) & Chr(6)
For x = 0 to 17
BlankZip = BlankZip & Chr(0)
Next
ts.Write BlankZip
ts.Close
Set fso = Nothing
Set ts = nothing

Set objShell = CreateObject("Shell.Application")
Set WshShell = WScript.CreateObject("WScript.Shell")
Set DestFldr=objShell.NameSpace(zipfile)

Call DestFldr.MoveHere (source,16+4)
' wait for correct close of file
WScript.sleep (10000)
End Function

Function stm_deleteold(dir, days)
'http://thebackroomtech.wordpress.com/2007/06/12/howto-automatically-remove-files-older-than-x-days/'
Dim Fso
Dim Directory
Dim Modified
Dim Files

Set Fso = CreateObject("Scripting.FileSystemObject")
Set Directory = Fso.GetFolder(dir)
Set Files = Directory.Files
For Each Modified in Files
If DateDiff("D", Modified.DateLastModified, Now) > days Then Modified.Delete
Next
End Function