Problem with SQL Backup

Hello,
I having trouble restoring a full followed by a differential in SQL 2000.
I have a full file and one differential I am trying to restore.
I am using the full file to restore the full and then when I try to apply
the differential, I get an error saying Cannot apply the backup
My commands are
USE MASTER
RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
norecovery,
move 'acusydb' to 'F:\dataarea\fulldb.mdf',
move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
What I get is an error Msg3136 that says
Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
The question I have is why is this happening, and 2 how can I fix it. I was
able to get the full to restore properly, but not the diff.
Any help is appreciated.
Thanks,
David
David
If these files are at the same device you need to specify a file option. If
they aren't ,make sure that you restore correct files.
"david" <david@.discussions.microsoft.com> wrote in message
news:06793D7B-CB71-4F9F-8E7C-903C3334FF58@.microsoft.com...
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with
> norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database
> dbname.
> The question I have is why is this happening, and 2 how can I fix it. I
> was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David
|||Can you be a little more specific? An example will help. The data files are
on the same physical directory. how does this make a difference or is it the
way SQL needs to see it?
Thanks,
David
"Uri Dimant" wrote:

> David
> If these files are at the same device you need to specify a file option. If
> they aren't ,make sure that you restore correct files.
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:06793D7B-CB71-4F9F-8E7C-903C3334FF58@.microsoft.com...
>
>
|||Additionally, what specifies the file number? Is this something I need to
specify when I backup the file as well? Is there any way to verify what the
file contains similar to getting the logical database name out of the file?
Also, do I need to specify move for each file I restore because of the
logical name?
I am assuming I need to.
Thanks,
David
"david" wrote:

> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David
|||Does anyone have input for me? Any help is appreciated.
Thanks,
David
"david" wrote:

> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David
|||My guess is that you did something like below:
(a) BACKUP DATABASE
(b) BACKUP DATABASE
(c) BACKUP DATABASE WITH DIFFERENTIAL
RESTORE (a)
RESTORE (c)
In order to restore (c), you need to:
RESTORE (b)
RESTORE (c)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"david" <david@.discussions.microsoft.com> wrote in message
news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...[vbcol=seagreen]
> Does anyone have input for me? Any help is appreciated.
> --
> Thanks,
> David
>
> "david" wrote:
|||Tibor:
There is a nightly full backup taken at 9:30 PM of the SQL database. We will
call this BACKUP (A)
Each morning, beginning at 8:00 AM, I am backing up the same database with
differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
If I have a failure, I want to take the full from the previous night (BACKUP
A)
and apply the appropriate differential (Backup 10AM) to bring the state of
the database to that point. There is no log backup.
I am copying these flat files to a file folder on another computer.
Through SQLQuery I am running the following restore script
This will allow me to move the full database to the new server and a new
location.
USE MASTER
RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
norecovery,
move 'acusydb' to 'F:\dataarea\fulldb.mdf',
move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
Then I try to restore the differential with the following command:
RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
Lastly, I should be able to add the following line to bring the database to
the final usable state.
RESTORE database dbname with recovery.
What is happening is I am getting the error as follows
What I get is an error Msg3136 that says
Cannot apply the backup on device F:\ftproot\fulldiff.bak to database dbname.
Prior people have stated I need to use the FILE flag, but I don't really
understand how to use it properly and I have asked for an example.
I am wondering if the path specified in the restore procedure
(F:\FTPRoot\fulldiff.bak') is the problem. Can I have two files in the same
directory?
I am assuming yes, but I am unsure how SQL handles the media.
Can you help?
Thanks,
David
"Tibor Karaszi" wrote:

> My guess is that you did something like below:
> (a) BACKUP DATABASE
> (b) BACKUP DATABASE
> (c) BACKUP DATABASE WITH DIFFERENTIAL
> RESTORE (a)
> RESTORE (c)
> In order to restore (c), you need to:
> RESTORE (b)
> RESTORE (c)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
>
>
|||david wrote:
> Tibor:
> There is a nightly full backup taken at 9:30 PM of the SQL database. We will
> call this BACKUP (A)
> Each morning, beginning at 8:00 AM, I am backing up the same database with
> differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
> If I have a failure, I want to take the full from the previous night (BACKUP
> A)
> and apply the appropriate differential (Backup 10AM) to bring the state of
> the database to that point. There is no log backup.
> I am copying these flat files to a file folder on another computer.
> Through SQLQuery I am running the following restore script
> This will allow me to move the full database to the new server and a new
> location.
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>
> Then I try to restore the differential with the following command:
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>
If you have no transaction logs, why are you specifying norecovery? Try using recovery.
|||If you are absolutely certain that you do restore the most recent db backup before the diff backup
(check against the backup history table in msdb, for instance), and you still get the error and you
don't find anything on KB i suspect a bug in SQL Server and you should open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"david" <david@.discussions.microsoft.com> wrote in message
news:9ED285B6-9549-49A5-BC0C-C01F15B2E4E6@.microsoft.com...[vbcol=seagreen]
> Tibor:
> There is a nightly full backup taken at 9:30 PM of the SQL database. We will
> call this BACKUP (A)
> Each morning, beginning at 8:00 AM, I am backing up the same database with
> differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
> If I have a failure, I want to take the full from the previous night (BACKUP
> A)
> and apply the appropriate differential (Backup 10AM) to bring the state of
> the database to that point. There is no log backup.
> I am copying these flat files to a file folder on another computer.
> Through SQLQuery I am running the following restore script
> This will allow me to move the full database to the new server and a new
> location.
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>
> Then I try to restore the differential with the following command:
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> Lastly, I should be able to add the following line to bring the database to
> the final usable state.
> RESTORE database dbname with recovery.
> What is happening is I am getting the error as follows
> What I get is an error Msg3136 that says
> Cannot apply the backup on device F:\ftproot\fulldiff.bak to database dbname.
> Prior people have stated I need to use the FILE flag, but I don't really
> understand how to use it properly and I have asked for an example.
> I am wondering if the path specified in the restore procedure
> (F:\FTPRoot\fulldiff.bak') is the problem. Can I have two files in the same
> directory?
> I am assuming yes, but I am unsure how SQL handles the media.
> Can you help?
>
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
|||I have checked the files, but I am still not sure how to solve this issue. I
am using the data from the last full and the differential that I apply is the
one say for 2PM.
Why am I getting the error? And how do I correct it? Any help is appreciated?
Anyone?
Thanks,
David
"david" wrote:

> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David
sql