'************************************************************** ' differential backup of a databases ' backup format of "sls" for SQL Lite Speed "bak" for SQL Backup ' fail if not one or the other ' could probably do either diffential log or full backups in ' one script pretty easily. I use different scripts so I can ' standardize the parameter list and remain backward compatible ' with all my currently running backups '************************************************************** Option Explicit Function Main() Dim sBackupFolder, sSourceSQLServerName, sDatabaseName, sBackupFormat Dim oSourceSQLServer, oDB, oBackup Dim sFileName, sCommand sBackupFolder = DTSGlobalVariables("Backup Folder") sSourceSQLServerName = DTSGlobalVariables("Source SQL Server") sDatabaseName = DTSGlobalVariables("Database Name") sBackupFormat = DTSGlobalVariables("Backup Format") If NOT(LCase(sBackupFormat) = "bak") and NOT(LCase(sBackupFormat) = "sls") Then Err.Raise 1, "BackupDatabaseDifferential", "Invalid Backup Format specified - must be sls (SQLLiteSpeed) or bak (native SQL backup)" End If ' do not allow differentials on system databases If NOT(sDatabaseName = "master") And _ NOT(sDatabaseName = "model") And _ NOT(sDatabaseName = "msdb") And _ NOT(sDatabaseName = "tempdb") And _ NOT(sDatabaseName = "distribution") Then Set oSourceSQLServer = CreateObject("SQLDMO.SQLServer2") oSourceSQLServer.LoginTimeout = 15 oSourceSQLServer.LoginSecure = True oSourceSQLServer.Connect sSourceSQLServerName ' make sure a good db name was provided Set oDB = oSourceSQLServer(sDatabaseName) ' set the backup disk device name If Right(sBackupFolder,1) <> "\" Then sBackupFolder = sBackupFolder & "\" End If sFileName = sBackupFolder & sDatabaseName & "_dif." & sBackupFormat 'SQL Lite Speed Backup If sBackupFormat = "sls" Then ' backup the database sCommand = "EXEC master.dbo.xp_backup_database @database = '" & sDatabaseName & _ "', @filename = '" & sFileName & "', @with = 'DIFFERENTIAL', @init = 1" ' Q279514 explains why no ExecuteWithResultsAndMessages2 ' so don't capture QueryResult, just let it stream to stdout oSourceSQLServer.ExecuteWithResults(sCommand) End If 'SQL Server Backup If sBackupFormat = "bak" Then 'SQL backup Set oBackup = CreateObject("SQLDMO.Backup") oBackup.Database = oDB.Name oBackup.Files = sFileName oBackup.Action = SQLDMOBackup_Differential ' 1 oBackup.Initialize = True oBackup.SQLBackup oSourceSQLServer End If oSourceSQLServer.DisConnect Set oSourceSQLServer = Nothing End If ' valid db requested Main = DTSTaskExecResult_Success End Function