'********************************************************************** ' restore a database from a backup file of the same name to different ' path. Fail if not sls (sqlliteSpeed) or bak (native) format ' no support for system databases or the admin database '************************************************************************ Function Main() Dim sBackupFolder, sTargetSQLServerName, sDatabaseName, sBackupFormat Dim sPrimaryLogicalFileName, sPrimaryFileLocation, sLogLogicalFileName, sLogFileLocation, sIndexesLogicalFileName, sIndexesFileLocation Dim oTargetSQLServer, oBackup Dim sFileName, sCommand, sRelocate sBackupFolder = DTSGlobalVariables("Backup Folder") sTargetSQLServerName = DTSGlobalVariables("Target SQL Server") sDatabaseName = DTSGlobalVariables("Database Name") sBackupFormat = DTSGlobalVariables("Backup Format") sPrimaryLogicalFileName = DTSGlobalVariables("Primary Logical File Name") sPrimaryFileLocation = DTSGlobalVariables("Primary File Location") sLogLogicalFileName = DTSGlobalVariables("Log Logical File Name") sLogFileLocation = DTSGlobalVariables("Log File Location") sIndexesLogicalFileName = DTSGlobalVariables("Indexes Logical File Name") sIndexesFileLocation = DTSGlobalVariables("Indexes File Location") sRelocate = "" If Not (LCase(sBackupFormat) = "bak") And Not (LCase(sBackupFormat) = "sls") Then Err.Raise 1, "RestoreDatabase", "Invalid Backup Format - must be sls (SQLLiteSpeed) or bak (native SQL backup)" End If ' no system dbs nor admin If Not (LCase(sDatabaseName) = "master") _ And Not (LCase(sDatabaseName) = "model") _ And Not (LCase(sDatabaseName) = "msdb") _ And Not (LCase(sDatabaseName) = "tempdb") _ And Not (LCase(sDatabaseName) = "admin") Then Set oTargetSQLServer = CreateObject("SQLDMO.SQLServer2") oTargetSQLServer.LoginTimeout = 15 oTargetSQLServer.LoginSecure = True oTargetSQLServer.Connect sTargetSQLServerName ' set the backup disk device name If Right(sBackupFolder, 1) <> "\" Then sBackupFolder = sBackupFolder & "\" End If sFileName = sBackupFolder & sDatabaseName & "." & sBackupFormat 'SQL Lite Speed Restore If sBackupFormat = "sls" Then sCommand = "EXEC master.dbo.xp_restore_database @database = '" & sDatabaseName & _ "', @filename = '" & sFileName & "', @filenumber = 1" If sPrimaryLogicalFileName <> "" _ And sPrimaryFileLocation <> "" Then sCommand = sCommand & ", @with = 'MOVE """ & sPrimaryLogicalFileName & """ TO """ & sPrimaryFileLocation & """'" End If If sLogLogicalFileName <> "" _ And sLogLogicalFileLocation <> "" Then sCommand = sCommand & ", @with = 'MOVE """ & sLogLogicalFileName & """ TO """ & sLogFileLocation & """'" End If If sIndexesLogicalFileName <> "" _ And sIndexesFileLocation <> "" Then sCommand = sCommand & ", @with = 'MOVE """ & sIndexesLogicalFileName & """ TO """ & sIndexesFileLocation & """'" End If 'no executewithresultsandmessages allowed so just stream QueryResult to stdout oTargetSQLServer.ExecuteWithResults(sCommand) End If 'SQL Server Restore If sBackupFormat = "bak" Then Set oRestore = CreateObject("SQLDMO.Restore") oRestore.Database = sDatabaseName oRestore.Files = sFileName oRestore.ReplaceDatabase = True If sPrimaryLogicalFileName <> "" _ And sPrimaryFileLocation <> "" Then If sRelocate = "" Then sRelocate = "[" & sPrimaryLogicalFileName & "],[" & sPrimaryFileLocation & "]" Else sRelocate = sRelocate & ",[" & sPrimaryLogicalFileName & "],[" & sPrimaryFileLocation & "]" End If End If If sLogLogicalFileName <> "" _ And sLogLogicalFileLocation <> "" Then If sRelocate = "" Then sRelocate = "[" & sLogLogicalFileName & "],[" & sLogLogicalFileLocation & "]" Else sRelocate = sRelocate & ",[" & sLogLogicalFileName & "],[" & sLogLogicalFileLocation & "]" End If End If If sIndexesLogicalFileName <> "" _ And sIndexesFileLocation <> "" Then If sRelocate = "" Then sRelocate = "[" & sIndexesLogicalFileName & "],[" & sIndexesFileLocation & "]" Else sRelocate = sRelocate & ",[" & sIndexesLogicalFileName & "],[" & sIndexesFileLocation & "]" End If End If if sRelocate <> "" Then oRestore.RelocateFiles = sRelocate End If oRestore.SQLRestore oTargetSQLServer End If oTargetSQLServer.Disconnect Set oTargetSQLServer = Nothing End If 'OK for this db to be restored Main = DTSTaskExecResult_Success End Function