Attempt to take Differential or Transaction Log backups using SQL DMO in a job goes for full database backup always

  • Article

 

Problem:

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:

Dim oBackup

Dim oRestore

Dim oBackupFolder

Dim strSQLServer

Dim strSQLBackupFolderPath

strSQLBackupFolderPath = "C:\"

strSQLServer = "T-ASONI-PRI"

Set oServer = CreateObject("SQLDMO.SQLServer2")

oServer.LoginSecure = True

oServer.Connect strSQLServer

Set oBackup = CreateObject("SQLDMO.Backup")

oBackup.Database = "Pubs"

oBackup.Action = SQLDMOBackup_Log

oBackup.Files = "c:\pubs.trn"

oBackup.SQLBackup oServer

oServer.DisConnect

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'

BackupType

----------

1        

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   

Resolution/Workaround:

Replace oBackup.Action = SQLDMOBackup_Log for transaction log backup by oBackup.Action = 3 for transaction log backup

Cause:

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