RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACE
You are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>