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


backupSQLandZIP.rar

 IT  Helios  7 Komentáře Buck the Bug, June 7th, 2008



Aktuální články

Aktuální komentáře

Kategorie

Linky

RSS blogu

Podporuji

Behej.com
Running Training Log
Opera, the fastest and most secure web browser Connectria.com

Reklama


Evidence