?
Solved

scheduled backup error SQL2000

Posted on 2007-03-29
5
Medium Priority
?
1,202 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
ID: 18815458
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
ID: 18815519
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
ID: 18815704
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 200 total points
ID: 18815812
>>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
ID: 18816284
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 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