Scheduled backup error

  • Yesterday, I made a small change to the backup on two databases. It was decided that now that they are being more heavily used we should be running differential log backups throughout the day in addition to the nightly ones.

    As you can probably guess by now one database backup works fine and the other does not!

    The SQL for both database backups is the same:

    MAIN BACKUP - each weekday at 10pm

    step 1:

    BACKUP DATABASE [TheDatabase] TO [TheDatabaseBACKUP]  WITH  INIT ,

     NOUNLOAD ,  NAME = N'TheDatabase backup',  NOSKIP,

      STATS = 10,  NOFORMAT

    step 2:

    BACKUP log [TheDatabase] TO [TheDatabaseLOGBACKUP] WITH  INIT

    DIFFERENTIAL BACKUP - every hour between 7am and 9pm

    step 1:

    BACKUP log [TheDatabase] TO [TheDatabaseLOGBACKUP] WITH  NOINIT

     

    The error I get is:

    Executed as user: SERVER\******. The media set for database 'TheDatabase' has 3 family members but only 1 are provided.  All members must be provided. [SQLSTATE 42000] (Error 3132)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.


  • I assume that you use backup device. Did you check if all 3 members are there?

  • You are correct - I am using a backup device.

    How do I check which members there are?


  • The backup device is used with other 2 devices. Backup data are evenly split into these devices (members). To find the members, you can choose Properties by right-clicking device icons. Then, click Content to find your necessary info.

    If you can include all members in your script, you should be able to run it. An example is:

    BACKUP DATABASE [pubs] TO [myDevice], [yourDevice]  WITH  INIT

  • Open Enterprise Manager. Expand until you see your database. Right click on it. Select Properties. Go to the tab for the Transaction Log. How many files are listed?

    -SQLBill

  • "Open Enterprise Manager. Expand until you see your database. Right click on it. Select Properties. Go to the tab for the Transaction Log. How many files are listed?"

     

    One file.


  • I get the exact same error. When I list the files in Enterprise Manager, I too see only one member. How can this be?

  • This really started happening after I added another file to the media family, but I promptly removed it because I didn't need it. I think something is out of sync in SQL Server, but I all I did was use Enterprise Manager normally. Is this a known bug?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply