Differential restore

Yes I did restored with norecovery. I also have SP3 loaded
and have tried to do the restore both through Enterprise
Manager and the Query Analyzer. Both worked until
recently. I have made no changes to the either the
configuration or the database. this is crazy
Thanks for any help you can give me.
jeff

quote:

---

>--Original Message--
>Did you restore the full backup with NORECOVERY? If not,

---

re-restore with

quote:

---

>this option.
>--
>Tom
>----

---

--

quote:

---

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jeff Timmerman" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
>I can no longer restore a differential backup after
>restoring the fullbackup. I found the microsoft work
>around that says to do the restore throught the Query
>Analizer but that doesn't work either. Does anyone know
>how i cna fix this problem?
>

---

Just wondering about corruption here. Can you try backing up Northwind -
full and differential, plus a couple of txn logs, then restore them all? If
that fails, it could be a SQL Server problem. If it succeeds, it's your
original backups.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Timmerman" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca701c3d6cd$9aa7bdf0$a501280a@.phx.gbl...
Yes I did restored with norecovery. I also have SP3 loaded
and have tried to do the restore both through Enterprise
Manager and the Query Analyzer. Both worked until
recently. I have made no changes to the either the
configuration or the database. this is crazy
Thanks for any help you can give me.
jeff

quote:

---

>--Original Message--
>Did you restore the full backup with NORECOVERY? If not,

---

re-restore with

quote:

---

>this option.
>--
>Tom
>----

---

--

quote:

---

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jeff Timmerman" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:0a0801c3d620$25f35f30$a001280a@.phx.gbl...
>I can no longer restore a differential backup after
>restoring the fullbackup. I found the microsoft work
>around that says to do the restore throught the Query
>Analizer but that doesn't work either. Does anyone know
>how i cna fix this problem?
>

---

Differential DB Restore Script Help

I am trying to restore a differential backup on top of the
restored full backup and getting the following error:
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'F:\HISTORY backup.BAK'
to database 'HISTORY'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Here is my script:
RESTORE DATABASE HISTORY
FROM DISK = 'F:\HISTORY backup.BAK'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'F:\UNDO_WPHISTORY.ldf',
MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL\Data\WPHISTORY_dat1.mdf',
MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL\Data\HISTORY_log1.ldf'
---
I am thinking that it has something to do with backup
date/time.
Any help will be appreciated.Use RESTORE HEADERONLY to investigate what is on the backup files. Also,
check the sysbackuphistory tables in msdb to determine if you had any db
backup in between the db and diff backup you try to restore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"calvin" <anonymous@.discussions.microsoft.com> wrote in message
news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
> I am trying to restore a differential backup on top of the
> restored full backup and getting the following error:
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'F:\HISTORY backup.BAK'
> to database 'HISTORY'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Here is my script:
> RESTORE DATABASE HISTORY
> FROM DISK = 'F:\HISTORY backup.BAK'
> WITH
> DBO_ONLY,
> REPLACE,
> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HISTORY_log1.ldf'
> ---
> I am thinking that it has something to do with backup
> date/time.
> Any help will be appreciated.
>|||Yes. There are Transaction log backups between the Full
backup anf the Differential backup (Differential backup is
the latest).
>--Original Message--
>Use RESTORE HEADERONLY to investigate what is on the
backup files. Also,
>check the sysbackuphistory tables in msdb to determine if
you had any db
>backup in between the db and diff backup you try to
restore.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"calvin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
>> I am trying to restore a differential backup on top of
the
>> restored full backup and getting the following error:
>> Server: Msg 3136, Level 16, State 1, Line 1
>> Cannot apply the backup on device 'F:\HISTORY
backup.BAK'
>> to database 'HISTORY'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Here is my script:
>> RESTORE DATABASE HISTORY
>> FROM DISK = 'F:\HISTORY backup.BAK'
>> WITH
>> DBO_ONLY,
>> REPLACE,
>> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
>> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
>> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HISTORY_log1.ldf'
>> ---
>> I am thinking that it has something to do with backup
>> date/time.
>> Any help will be appreciated.
>>
>
>.
>|||Silly ? but did you use the right full backup? You can
only apply a differential to its proper full.
>--Original Message--
>Yes. There are Transaction log backups between the Full
>backup anf the Differential backup (Differential backup
is
>the latest).
>
>>--Original Message--
>>Use RESTORE HEADERONLY to investigate what is on the
>backup files. Also,
>>check the sysbackuphistory tables in msdb to determine
if
>you had any db
>>backup in between the db and diff backup you try to
>restore.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"calvin" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
>> I am trying to restore a differential backup on top of
>the
>> restored full backup and getting the following error:
>> Server: Msg 3136, Level 16, State 1, Line 1
>> Cannot apply the backup on device 'F:\HISTORY
>backup.BAK'
>> to database 'HISTORY'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Here is my script:
>> RESTORE DATABASE HISTORY
>> FROM DISK = 'F:\HISTORY backup.BAK'
>> WITH
>> DBO_ONLY,
>> REPLACE,
>> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
>> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
>> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HISTORY_log1.ldf'
>> ---
>> I am thinking that it has something to do with backup
>> date/time.
>> Any help will be appreciated.
>>
>>
>>.
>.
>|||I wasn't referring to t-log backups between the db and the diff backup. I
was referring to *db backups* between the db backup you try to restore and
the diff backup you try to restore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Calvin" <anonymous@.discussions.microsoft.com> wrote in message
news:022b01c3a7d1$b509aee0$a501280a@.phx.gbl...
> Yes. There are Transaction log backups between the Full
> backup anf the Differential backup (Differential backup is
> the latest).
>
> >--Original Message--
> >Use RESTORE HEADERONLY to investigate what is on the
> backup files. Also,
> >check the sysbackuphistory tables in msdb to determine if
> you had any db
> >backup in between the db and diff backup you try to
> restore.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"calvin" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
> >> I am trying to restore a differential backup on top of
> the
> >> restored full backup and getting the following error:
> >>
> >> Server: Msg 3136, Level 16, State 1, Line 1
> >> Cannot apply the backup on device 'F:\HISTORY
> backup.BAK'
> >> to database 'HISTORY'.
> >> Server: Msg 3013, Level 16, State 1, Line 1
> >> RESTORE DATABASE is terminating abnormally.
> >>
> >> Here is my script:
> >> RESTORE DATABASE HISTORY
> >> FROM DISK = 'F:\HISTORY backup.BAK'
> >> WITH
> >> DBO_ONLY,
> >> REPLACE,
> >> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
> >> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
> >> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
> >> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
> >> Server\MSSQL\Data\HISTORY_log1.ldf'
> >> ---
> >> I am thinking that it has something to do with backup
> >> date/time.
> >>
> >> Any help will be appreciated.
> >>
> >>
> >
> >
> >.
> >|||No. There is no database backups between the last full
backup which I restored and the differential backup.
and for Allen, yes it is the correct full backup.
Thanks.
>--Original Message--
>I wasn't referring to t-log backups between the db and
the diff backup. I
>was referring to *db backups* between the db backup you
try to restore and
>the diff backup you try to restore.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Calvin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:022b01c3a7d1$b509aee0$a501280a@.phx.gbl...
>> Yes. There are Transaction log backups between the Full
>> backup anf the Differential backup (Differential backup
is
>> the latest).
>>
>> >--Original Message--
>> >Use RESTORE HEADERONLY to investigate what is on the
>> backup files. Also,
>> >check the sysbackuphistory tables in msdb to determine
if
>> you had any db
>> >backup in between the db and diff backup you try to
>> restore.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"calvin" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
>> >> I am trying to restore a differential backup on top
of
>> the
>> >> restored full backup and getting the following error:
>> >>
>> >> Server: Msg 3136, Level 16, State 1, Line 1
>> >> Cannot apply the backup on device 'F:\HISTORY
>> backup.BAK'
>> >> to database 'HISTORY'.
>> >> Server: Msg 3013, Level 16, State 1, Line 1
>> >> RESTORE DATABASE is terminating abnormally.
>> >>
>> >> Here is my script:
>> >> RESTORE DATABASE HISTORY
>> >> FROM DISK = 'F:\HISTORY backup.BAK'
>> >> WITH
>> >> DBO_ONLY,
>> >> REPLACE,
>> >> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
>> >> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft
SQL
>> >> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
>> >> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft
SQL
>> >> Server\MSSQL\Data\HISTORY_log1.ldf'
>> >> ----
-
>> >> I am thinking that it has something to do with backup
>> >> date/time.
>> >>
>> >> Any help will be appreciated.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Are you absolutely 100% certain? Did you check against the sysbackuphistory
tables in msdb?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Calvin" <anonymous@.discussions.microsoft.com> wrote in message
news:02c601c3a7da$1371a570$a501280a@.phx.gbl...
> No. There is no database backups between the last full
> backup which I restored and the differential backup.
> and for Allen, yes it is the correct full backup.
> Thanks.
>
> >--Original Message--
> >I wasn't referring to t-log backups between the db and
> the diff backup. I
> >was referring to *db backups* between the db backup you
> try to restore and
> >the diff backup you try to restore.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Calvin" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:022b01c3a7d1$b509aee0$a501280a@.phx.gbl...
> >> Yes. There are Transaction log backups between the Full
> >> backup anf the Differential backup (Differential backup
> is
> >> the latest).
> >>
> >>
> >>
> >> >--Original Message--
> >> >Use RESTORE HEADERONLY to investigate what is on the
> >> backup files. Also,
> >> >check the sysbackuphistory tables in msdb to determine
> if
> >> you had any db
> >> >backup in between the db and diff backup you try to
> >> restore.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"calvin" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
> >> >> I am trying to restore a differential backup on top
> of
> >> the
> >> >> restored full backup and getting the following error:
> >> >>
> >> >> Server: Msg 3136, Level 16, State 1, Line 1
> >> >> Cannot apply the backup on device 'F:\HISTORY
> >> backup.BAK'
> >> >> to database 'HISTORY'.
> >> >> Server: Msg 3013, Level 16, State 1, Line 1
> >> >> RESTORE DATABASE is terminating abnormally.
> >> >>
> >> >> Here is my script:
> >> >> RESTORE DATABASE HISTORY
> >> >> FROM DISK = 'F:\HISTORY backup.BAK'
> >> >> WITH
> >> >> DBO_ONLY,
> >> >> REPLACE,
> >> >> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
> >> >> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft
> SQL
> >> >> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
> >> >> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft
> SQL
> >> >> Server\MSSQL\Data\HISTORY_log1.ldf'
> >> >> ----
> -
> >> >> I am thinking that it has something to do with backup
> >> >> date/time.
> >> >>
> >> >> Any help will be appreciated.
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Different results same query between original and copied db

Hi all,

I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong.

Has anybody seen this behavior?

Thanks for your help

Luis Torresdo u have Nondeterministic Functions like rand() ..??|||nope, but I solved the problem, I didnt have the service pack 3 installed on my laptop and after installing it everything worked perfect.

Thanks for your help :)

Luis Torres|||would like to know wat SP3 did !!!|||Yeah, I would love to, I like the solution better when I understand the problem

Different Execution Plans -> Same Query, Same Database, Different SQL Server Install

Background: Same database, restored from development to production.

Indexes up to date. Service Pack 3 on Production. Originally RTM on Development, upgraded to Sp4, Execution Plan remained the same.

The query itself is not my concern, but that there is such a wide difference in performance between 2 boxes. Is there a setting on the Production that might be slowing it down? My system seems to have selected a worktable, is there a way I can turn that off to mimic the server?

I want to optimize the queries to perform best on the Production server, which is hard to do if my server's chosing different execution plans.

Is there anyway to determine why the Production machine would choose one plan vs. the development machine?
Development:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Index Spool(SEEK:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
| |--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

Production:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]), WHERE:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

The following is the output of the statistics on my Local Development
Server:
Table 'CONSUMER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3187 ms, elapsed time = 1877 ms.
This is the output of the statistics on the Production environment:
Table 'CONSUMER'. Scan count 88857, logical reads 274907, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 41708, logical reads 48294390, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 690218 ms, elapsed time = 225583 ms.

Have you checked that the statistics are equal for the indexes on both servers?

Look for DBCC SHOW_STATISTICS in the BOL.
|||

Try and rebuild the Indexes on Production Database

DBCC DBREINDEX

|||

The databases were identical, both restored from the same backup resources on the same day.

Thank you for your responses, and I will attempt both, but I don't believe that the statistics or indexes would be different if drawn from the same resource?

|||This is not due to statistics or indexes. It should be identical when you restore from a backup of a database. There are other factors that can affect the query plan - number of processors, memory, maxdop settings etc. Additionally you should try to keep the service pack level the same between the machines. The main reason is that you could get different plans due to changes in service pack and some could be bugs also. So it is best to compare servers that are on the same service pack too. If you are seeing worse performance than SP3 for the same query/machine configuration then it is most probably a bug. I would encourage you to create a bug using the MSDN Product Feedback Center with the repro steps. Also, please use SET STATISTICS PROFILE to check the actual query plan/execution times.|||

It should indeed be identical when you restore but a colleague here has experienced some strange behavior after a restore several times. He claims that after an update of the statistics everything functioned as expected. Now I can't say I fully support this statement as I have no personal experience with this.

We tend to keep our service packs identical to production, I think this should be a rule for every development environment.

You should update your weblog Uma, you still have to answer my NOLOCK question :-)

|||

Thanks for your reply,

The production is running SP3a. I tested the query on development environment at both RTM and SP4 (not realizing the production was still only at SP3) and I did get the same plans between RTM and SP4.

The production machine is Windows 2003 server dual processor,Intel Xeon 3.6ghz, with 3.5 GB Memory, it is part of a cluster server.
The development machine is Windows XP dual processor, Intel Pentium 4 2.6ghz, with 1 GB Memory.

I will compare the profiles in the am.