Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    10

    Unanswered: SQL-DMO Backup Problems

    Hi there

    I am using SQL-DMO from VB to backup a database on a remote server.

    However when I backup the database 2 times in a row, the first time the backup file is created OK, the 2nd time the database is not backed up. This appears to be because it won't write over the existing file.

    I have experimented with the SQLDMO.Backup object's expirationdate property but this does not seem to be any help. Why is this happening?

    Can anybody help?

    Code
    ===================
    '(m_SQLServer = previously defined ValidServer)

    Dim m_Backup As SQLDMO.Backup


    Set m_Backup = New SQLDMO.Backup

    m_Backup.Database = strDatabaseName

    m_Backup.Files = m_SQLServer.Registry.SQLDataRoot & "\BACKUP\Temp.bak"

    m_Backup.ExpirationDate = Now

    m_Backup.SQLBackup m_SQLServer

    Set m_Backup = Nothing

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Try not setting the m_Backup.ExpirationDate and see what happens. I think that the value of VB NOW is today's date (ie. 28-Feb-2002) and from what I've read in BOL the backup will only over write files that are older. So if you perform this code twice in one day the job will fail on the second time.

    Books Online
    The ExpirationDate property is valid only for backup data stored on disk or tape devices. Backup sets older than the expiration date can be overwritten by a later backup.

  3. #3
    Join Date
    Feb 2002
    Posts
    10

    Still doesn't work

    Thansk for your reply. However not setting an expiration date seems to have the same effect.

    I have tried not setting the expiration date, setting it to the current date, and setting it to an older date. But the result is always the same. I wonder if it is a red herring, and the problem is with overwriting a backup on a remote machine.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I do the same thing that you do but I've written my job using Perl, however the setting are the same.

    Code:
    $oBackup->{'TruncateLog'} = SQLDMOBackup_Log_TruncateOnly;
    $oBackup->{'Action'} = SQLDMOBackup_Log;
    $oBackup->{'Database'} = $dbccDB->{'Name'};
    
    $oBackup->SQLBackup($oServer);
    
    $oBackup->{'TruncateLog'} = SQLDMOBackup_Log_Truncate;
    $oBackup->{'Action'} = SQLDMOBackup_Database;
    $oBackup->{'Database'} = $dbccDB->{'Name'};
    $oBackup->{'Initialize'} = $fastdump == 1 ? 0 : 1;
    $oBackup->{'Devices'} = $dbccDB->{'Name'} . "Backup";
    
    $oBackup->SQLBackup($oServer);
    I dump the log first then the database.
    What is the error message that you get? Can you do an ON ERROR and print the error message out.

  5. #5
    Join Date
    Feb 2002
    Posts
    10
    Well Achorozy, that certainly did the trick! Thanks very much for your help.

    For reference:

    I used the following VB code to do what Achorozy does in Perl.

    With m_Backup

    .TruncateLog = SQLDMOBackup_Log_TruncateOnly
    .Action = SQLDMOBackup_Log
    .Database = strDatabaseName
    'Back up Logs
    .SQLBackup m_SQLServer

    .TruncateLog = SQLDMOBackup_Log_Truncate
    .Action = SQLDMOBackup_Database
    .Initialize = True
    .Files = strBackUpFileName
    'Back up Data
    .SQLBackup m_SQLServer


    End With

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •