Differential Backup file fails to restore.

We are doing the following steps:-

1. Weekly Full Bckup

2.Daily Differential backup

3.hr Log back up

The following command works fine for all the Backups. it means backup file is fine.

RESTORE FILELISTONLY from DISK = 'D:\Backup\new\DB_ full.BAK'

RESTORE HEADERONLY FROM DISK = 'D:\Backup\new\DB_ ull.BAK'

RESTORE LABELONLY FROM DISK = 'D:\Backup\new\Test_full.BAK'

RESTORE VERIFYONLY FROM DISK = 'D:\Backup\new\Test_full.BAK'

Also The full back up restoration works fine:

RESTORE DATABASE Test3 FROM DISK = 'D:\Backup\new\Test_full.BAK'

WITH MOVE 'Test2_Data' TO 'F:\MSSQL2K\MSSQL\data\Test2Net_Data.MDF',

MOVE 'Test2_Log' TO 'F:\MSSQL2K\MSSQL\data\Test2Net_Log.LDF',

NORECOVERY

GO

/* RESULT :

Processed 1032 pages for database 'Test3', file 'test2_Data' on file 1.

Processed 1 pages for database 'Test3', file 'test2_Log' on file 1.

RESTORE DATABASE successfully processed 1033 pages in 1.907 seconds (4.433 MB/sec).

*/

But While restoring the Differential file it throws error:

RESTORE DATABASE Test3 from DISK = 'D:\Backup\new\test2_Diff1.bak'

WITH NORECOVERY

GO

Msg 3136, Level 16, State 0, Line 1

Cannot apply the backup on device 'D:\Backup\new\test2_Diff1.bak' to database 'Test3'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

I checked the SQL Server Log no error msg corresponding Differential backup restore.

I don't know how to proceed further.Can any one guide me how to over come this..

Thanks !

is the differential backup appended or overwritten ? ? ?........if appended choose the exact differential backup and try to restore....also try using GUI and see........|||Differential backup is a separate file...

Differential backup fails because of erroneous full backup

Hi

I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.

On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.

I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.

When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.

Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?

I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.

Any insight is greatly appreciated.

No SQL server will not trigger a backup of its own........just check in the error log when the last full backup was made.....and see if the errorenous full backup name was present in the errorlog..........|||Thanks for the response. It turns out that there was someone else who had access to the server (unbeknownst to me) that triggered the full back up. Unfortunately, this individual did not know what he was doing and deleted the full backup file shortly after it completed. This, of course, screwed up my differentials.

Different result in SP vs Query Analyzer

I'm getting some very odd behaviour which I can currently
duplicate. One of my SPs fails when it can't find a
record. I copy the SQL from the SP and execute it via
query analyzer and ta-da, the record appears.
Further investigation shows that the sql checks that a
record doesn't have a "completed" status. Comment this
out and the SP works fine.
Continuing the investigation, the developers run this SP
through debug and find the value being returned
= 'COMPLETED'. Run the *same* code in query analyzer and
the value being returned is 'PLANNED'... How is this
possible' The sql returns the correct value when run
through query analyzer, but the incorrect value when run
as a part of the SP!!
Below is the 'where' clause:
.
.
.
Where
(vtt.Trip_Id = @.FCTripId)
And (eqm.eqm_sequence_no = 2)
And (cst.cst_consign_status_desc <> 'COMPLETED')
TIA,
SJT> I'm getting some very odd behaviour which I can currently
> duplicate.
Can you give us enough information so we can try to duplicate? Table
schema, sample data, the code for the procedure maybe, desired results. See
http://www.aspfaq.com/5006
> Further investigation shows that the sql checks that a
> record doesn't have a "completed" status.
What datatype is this? How does sql "check"? What is the exact syntax you
are using? What data is actually stored in the column?
> Continuing the investigation, the developers run this SP
> through debug and find the value being returned
> = 'COMPLETED'. Run the *same* code in query analyzer and
> the value being returned is 'PLANNED'... How is this
> possible'
You're looking at a different row? The stored procedure is being executed
against the test database, and query analyzer is connected to production?
> And (cst.cst_consign_status_desc <> 'COMPLETED')
I'm going to guess that (shudder) "cst_consign_status_desc" is a CHAR
column. You should use VARCHAR so that trailing spaces are ignored, and
ensure that ANSI_PADDING is set the same in both environments to ensure that
you get consistent results.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

different environment fails to fill a dataset

I have a C# client application that uses SQL Server as a backend. It works
fine on my development workstation. But when I moved it to another test
workstation four out of the five winforms worked fine but one doesn't. The
one that doesn't can open the connection and return the server version as a
test of whether the open worked right. I does. But when you run the
oledbadapter.fill command to fill the dataset it fails. It acts as if it
can't open the server. Further reading shows that the fill command can
actually open a connection on its own if necessary without the open command.
Is there anything in the environment or SQL Server that could be causing this
rejection. The SQL Server database was detached, copied, and then reattached
on the test workstation. SO it should be a duplicate.
Randy
Did you resync the logins to the users in that db after you attached it? If
not check out sp_change_users_login in BooksOnLine.
Andrew J. Kelly SQL MVP
"rseedle" <rseedle@.discussions.microsoft.com> wrote in message
news:C298B6AF-C57D-40EE-905C-CB50F29126F3@.microsoft.com...
>I have a C# client application that uses SQL Server as a backend. It works
> fine on my development workstation. But when I moved it to another test
> workstation four out of the five winforms worked fine but one doesn't. The
> one that doesn't can open the connection and return the server version as
> a
> test of whether the open worked right. I does. But when you run the
> oledbadapter.fill command to fill the dataset it fails. It acts as if it
> can't open the server. Further reading shows that the fill command can
> actually open a connection on its own if necessary without the open
> command.
> Is there anything in the environment or SQL Server that could be causing
> this
> rejection. The SQL Server database was detached, copied, and then
> reattached
> on the test workstation. SO it should be a duplicate.
> Randy

Differencies in triggers behavior on 2000 and 2005 SQL Servers

This code works fine on MS SQL 2000 but fails on MS SQL 2005:

create database tt

go

--exec dbo.sp_dbcmptlevel @.dbname=N'tt', @.new_cmptlevel=80

go

use tt
go

create table t (id int)
go

create proc p as update t set id=id
go

create trigger t1_i on t instead of update as update t set id=id
go

create trigger t1 on t for update as if @.@.nestlevel > 5 return exec p update t set id=id
go

insert t select 1
go

update t set id=id
go

use master
go
drop database tt
go

Using database compatibility level 80 (SQL 2000, uncomment the line starting with --exec dbo.sp_dbcmptlevel...) does not make this code work like it does on SQL 2000!

We have examined this code and found that changing

create trigger t1 on t for update as if @.@.nestlevel > 5 return
exec p -- this procedure just executes "update t set id=id" and fails

to direct "update"

create trigger t1 on t for update as if @.@.nestlevel > 5 return
update t set id=id -- works fine without any errors

makes this code work fine but our procedure p is too complex to do any workarounds...

With which error does the procedure fail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Msg 570, Level 16, State 1, Procedure p, Line 2

Instead of triggers do not support direct recursion. Trigger execution failed.

|||Hi,

do you have another triggers defined on the table ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

There are no other triggers. That code (in the first message) is enough complete to reproduce the error.

Sestrin

|||I believe this has been submitted to MS Connect, http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248294 and awaiting workaround.