[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

scheduled backup error SQL2000

Posted on 2007-03-29
5
Medium Priority
?
1,198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

649 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