You have a VB script to take full database backup and transaction log backup using SQL DMO but when the job is executed it takes full database backup always even if in the code it is specified to take transaction log backup.
The part where full database is specified in the code works fine but the part which takes transaction log backup in fact goes ahead and takes full database backup instead of taking the transaction log backup.
However, the same code when executed from VB interface works as expected and takes transaction log backup when specified.
You may reproduce the problem on your machine by using the following code:
strSQLBackupFolderPath = "C:\"
strSQLServer = "T-ASONI-PRI"
Set oServer = CreateObject("SQLDMO.SQLServer2")
oServer.LoginSecure = True
Set oBackup = CreateObject("SQLDMO.Backup")
oBackup.Database = "Pubs"
oBackup.Action = SQLDMOBackup_Log
oBackup.Files = "c:\pubs.trn"
Set oServer = Nothing
Set oDatabaseFile = Nothing
Set oDatabaseFolder = Nothing
Set oBackupFolder = Nothing
If you run the below command to verify the kind of backup taken, you will see a full database backup was taken
RESTORE HEADERONLY FROM DISK='C:\PUBS.TRN'
No matter what we specify for oBackup.Action we end up taking Full Database Backup.
Eg: Replace oBackup.Action = SQLDMOBackup_Log by oBackup.Action = aaaaaa, it still takes the full database backup
Even attempts to take differential backup goes for a full database backup
Replace oBackup.Action = SQLDMOBackup_Log for transaction log backup by oBackup.Action = 3 for transaction log backup
No matter what we put in the script it interprets it as 0 and goes for the full database backup so we need to specify the numeric equivalent for taking the backup. So for transaction log backup we specify 3.
Abhishek Soni Support Engineer, Microsoft SQL Server.
Reviewed By Sourabh Agarwal Technical Lead, Microsoft SQL Server