scheduled backup error SQL2000

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.
bickforcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
Run this in Query Analyzer:
/*****************************************/
Use TheDatabase
Go

sp_helpdb TheDatabase

/*****************************************/


I'm also a bit confused about how you are using the term Differential....you seem to be mixing Backup Log and Differential interchangeably, but they are not.  Please clarify


0
DonKronosCommented:
I found this at the following link

http://www.timallen.org/blog/index.php?topic=geek&page=6

Look about half way down the page.  

Hope this helps.

------------------------------------

Problem: I have a SQL Server 2000 maintenance job that backs up my transaction logs every ten minutes by running this step:


BACKUP LOG [Tryout] TO [Tx01], [Tx02]
  WITH NOINIT,
       NOUNLOAD,
       NAME = N'Tryout Tx 10 Mins',
       NOSKIP,
       STATS = 10,
       DESCRIPTION = N'Tryout Tx 10 Mins',
       NOFORMAT

This returned this error:


Executed as user: Tim. The media set for database 'Tryout' has 2 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.

What was happening is that I'm trying to backup to two different backup devices on two different systems, but one of the devices was not correctly formatted.

Fix:I fixed this with the following statement:


BACKUP LOG [Tryout] TO [Tx01], [Tx02]
  WITH INIT,
       NOUNLOAD,
       NAME = N'Tryout Tx 10 Mins',
       STATS = 10,
       DESCRIPTION = N'Tryout Tx 10 Mins',
       FORMAT

You run this statement once to resync your two backup devices. After that, the normal backup statement will work correctly.

Note: This solution reformats both backup devices, so you will want to do a complete backup to a different backup device prior to doing this.
0
bickforcAuthor Commented:
I have to admit I have no idea of what I'm doing!
Like a lot of jobs I'm left to muddle through the best I can - I'm sure that sounds familiar.
Here is the result of the sp:

name          db_size       owner       dbid   created    
------------- ------------- ----------- ------ -----------
TheDatabase   129.06 MB     sa          10.00  Jan 30 2006

status  
----------------------------------------------------------------
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics

compatibility_level
-------------------
80.00

name              fileid filename                           filegroup    size     maxsize     growth usage
----------------- ------ ---------------------------------- ------------ -------- ----------- ------ ---------
TheDatabase_dat   1.00   D:\MSSQL\Data\TheDatabase.mdf      PRIMARY      39296 KB Unlimited   10%    data only
TheDatabase_log   2.00   D:\MSSQL\Data\TheDatabase_log.ldf  NULL         92864 KB Unlimited   10%    log only

What I am trying to get to is a situation where if we have a disaster, I can recover back to at most an hour ago. Am I going about it correctly?
0
Kevin HillSr. SQL Server DBACommented:
>>What I am trying to get to is a situation where if we have a disaster, I can recover back to at most an hour ago. Am I going about it correctly?<<

For that, with only a 129mb database (very small by SQL Server standards), you simply need a Full Backup overnight, with T-log backups run every hour.  Differentials are probably overkill.

THe maintenance plan wizard can do all this for you :)

DB
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bickforcAuthor Commented:
It never occurred to me to look at the Maintenance Plan - so I've learned something today thankyou.

I shall have to wait until tomorrow to see if it all has worked, but I have no reason to believe it won't!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.