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,
DavidDavid
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...
> > 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
>
>|||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...
> 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|||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...
> > 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
>
>|||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...
> 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...
>> > 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
>>|||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|||The error message states that you did a database backup after the db backup you restore and before
the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to open a
case with MS Support for them to have a look at (unless you find something in KB that applies). See
my scripts for how to reproduce the error message:
--Work
CREATE DATABASE test
CREATE TABLE test..t(c1 int identity)
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
GO
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
--Doesn't work
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
GO
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"david" <david@.discussions.microsoft.com> wrote in message
news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
>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|||I understand. I have made sure the database files are OK. I am seeing the
same results. Can you give me additional information on how SQL applies its
backups? Are there specific criteria it is expecting when applying a
differential?
--
Thanks,
David
"Tibor Karaszi" wrote:
> The error message states that you did a database backup after the db backup you restore and before
> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to open a
> case with MS Support for them to have a look at (unless you find something in KB that applies). See
> my scripts for how to reproduce the error message:
> --Work
> CREATE DATABASE test
> CREATE TABLE test..t(c1 int identity)
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> GO
> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> --Doesn't work
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> GO
> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> >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
>|||I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
between the db backup and the diff backup you try to restore?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"david" <david@.discussions.microsoft.com> wrote in message
news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
>I understand. I have made sure the database files are OK. I am seeing the
> same results. Can you give me additional information on how SQL applies its
> backups? Are there specific criteria it is expecting when applying a
> differential?
> --
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
>> The error message states that you did a database backup after the db backup you restore and
>> before
>> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
>> open a
>> case with MS Support for them to have a look at (unless you find something in KB that applies).
>> See
>> my scripts for how to reproduce the error message:
>> --Work
>> CREATE DATABASE test
>> CREATE TABLE test..t(c1 int identity)
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
>> GO
>> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
>> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>> --Doesn't work
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
>> GO
>> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
>> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
>> >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
>>|||Good morning.
Had an interesting day yesterday that resulted in a better understanding of
the problem. I did a RESTORE HEADERONLY command on the two backup files and
noticed the date on the backup was two days earlier. I re-ran a full backup
and an another differential and was surprised the restore was successful.
I ensured the backup creation occured at the right time and now I have a
full backup that runs at 9PM each evening.
But I still have questions. This morning I took the full backup dated
11-04-2005 at 21:00. Differential files are dated 11-05-2005 at 10AM
(latest). When I tried to restore again, I got the 3136 error again. A big
disappointment.
So my questions are:
1. Can someone explain how the LSN markers are used in SQL headeronly data?
and does the differential need to match one of the lsn to function properly
on the restore.
Firstlsn, Lastlsn, Checkpointlsn, Differentiallsn
Any help as always is appreciated.
--
Thanks,
David
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
> between the db backup and the diff backup you try to restore?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
> >I understand. I have made sure the database files are OK. I am seeing the
> > same results. Can you give me additional information on how SQL applies its
> > backups? Are there specific criteria it is expecting when applying a
> > differential?
> >
> > --
> > Thanks,
> >
> > David
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> The error message states that you did a database backup after the db backup you restore and
> >> before
> >> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
> >> open a
> >> case with MS Support for them to have a look at (unless you find something in KB that applies).
> >> See
> >> my scripts for how to reproduce the error message:
> >>
> >> --Work
> >> CREATE DATABASE test
> >> CREATE TABLE test..t(c1 int identity)
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> >> GO
> >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> >>
> >> --Doesn't work
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> >> GO
> >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> >>
> >>
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "david" <david@.discussions.microsoft.com> wrote in message
> >> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> >> >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
> >>
> >>
>|||Well, I learned some interesting things out of this process. Thanks Tibor for
the assistance with this. I did indeed seem to have a SQL backup in between.
After some diligent investigation, I realized there was an additional backup
occuring in a backup job (Veritas) that was a minute after my last full. I
removed the SQL Agent job and am relying solely on the flat file backup. I
was able to sucessfully restore both databases this morning using the full
and desired differential. I am going to continually test the process this
week to make sure I can restore a number of times to ensure the problem is
really solved.
I would like to understand the LSN numbers and how SQL uses these numbers in
the HEADER INFORMATION.
Can anyone elaborate?
--
Thanks,
David
"david" wrote:
> Good morning.
> Had an interesting day yesterday that resulted in a better understanding of
> the problem. I did a RESTORE HEADERONLY command on the two backup files and
> noticed the date on the backup was two days earlier. I re-ran a full backup
> and an another differential and was surprised the restore was successful.
> I ensured the backup creation occured at the right time and now I have a
> full backup that runs at 9PM each evening.
> But I still have questions. This morning I took the full backup dated
> 11-04-2005 at 21:00. Differential files are dated 11-05-2005 at 10AM
> (latest). When I tried to restore again, I got the 3136 error again. A big
> disappointment.
> So my questions are:
> 1. Can someone explain how the LSN markers are used in SQL headeronly data?
> and does the differential need to match one of the lsn to function properly
> on the restore.
> Firstlsn, Lastlsn, Checkpointlsn, Differentiallsn
>
> Any help as always is appreciated.
> --
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
> > I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
> > between the db backup and the diff backup you try to restore?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "david" <david@.discussions.microsoft.com> wrote in message
> > news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
> > >I understand. I have made sure the database files are OK. I am seeing the
> > > same results. Can you give me additional information on how SQL applies its
> > > backups? Are there specific criteria it is expecting when applying a
> > > differential?
> > >
> > > --
> > > Thanks,
> > >
> > > David
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> The error message states that you did a database backup after the db backup you restore and
> > >> before
> > >> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
> > >> open a
> > >> case with MS Support for them to have a look at (unless you find something in KB that applies).
> > >> See
> > >> my scripts for how to reproduce the error message:
> > >>
> > >> --Work
> > >> CREATE DATABASE test
> > >> CREATE TABLE test..t(c1 int identity)
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> > >> GO
> > >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> > >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> > >>
> > >> --Doesn't work
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> > >> GO
> > >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> > >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> > >>
> > >>
> > >>
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> > >> >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
> > >>
> > >>
> >
> >