Solved

scheduled backup error SQL2000

Posted on 2007-03-29
5
1,181 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:bickforc
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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
 
LVL 15

Expert Comment

by:DonKronos
Comment Utility
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
 

Author Comment

by:bickforc
Comment Utility
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
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 50 total points
Comment Utility
>>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
 

Author Comment

by:bickforc
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now