Can I do this ?

So I have
a Full DB Backup taken at 7am
a Diff backup taken at 9am
a Diff backup taken at 11am
Can I do this ?
Restore Full DB Backup of 7am with noreceovery followed by
Restore Diff Backup of 9am with noreceovery followed by
Restore Diff Backup of 11am with recovery.
I know I could do a restore of the Full DB Backup of 7am followed by Restore
of Diff backup of 11am with recovery and it will work... But i want to know
if the above will work whereby i restore both the differentials one after
another
ThanksHassan wrote:
> So I have
> a Full DB Backup taken at 7am
> a Diff backup taken at 9am
> a Diff backup taken at 11am
> Can I do this ?
> Restore Full DB Backup of 7am with noreceovery followed by
> Restore Diff Backup of 9am with noreceovery followed by
> Restore Diff Backup of 11am with recovery.
> I know I could do a restore of the Full DB Backup of 7am followed by Resto
re
> of Diff backup of 11am with recovery and it will work... But i want to kno
w
> if the above will work whereby i restore both the differentials one after
> another
> Thanks
Did you try it?
CREATE DATABASE foob;
GO
BACKUP DATABASE foob TO DISK = 'C:\foob.bak' WITH INIT;
BACKUP DATABASE foob TO DISK = 'C:\foob1.bak' WITH DIFFERENTIAL, INIT;
BACKUP DATABASE foob TO DISK = 'C:\foob2.bak' WITH DIFFERENTIAL, INIT;
GO
USE master
RESTORE DATABASE foob FROM DISK = 'C:\foob.bak' WITH REPLACE,
NORECOVERY;
RESTORE DATABASE foob FROM DISK = 'C:\foob1.bak' WITH NORECOVERY;
RESTORE DATABASE foob FROM DISK = 'C:\foob2.bak' WITH RECOVERY;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hello Hassan,
You can do that; but will not make much difference. This is more than enough
to restore the FULL database backup with NORECOVERY follwed with
the last DIFFERENTIAL backup with RECOVERY. But for testing you could try
restore both DIFFERENTIAL backup and it will work fine..
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e7azxe8GHHA.3668@.TK2MSFTNGP02.phx.gbl...
> So I have
> a Full DB Backup taken at 7am
> a Diff backup taken at 9am
> a Diff backup taken at 11am
> Can I do this ?
> Restore Full DB Backup of 7am with noreceovery followed by
> Restore Diff Backup of 9am with noreceovery followed by
> Restore Diff Backup of 11am with recovery.
> I know I could do a restore of the Full DB Backup of 7am followed by
> Restore of Diff backup of 11am with recovery and it will work... But i
> want to know if the above will work whereby i restore both the
> differentials one after another
> Thanks
>

Can I do this ?

So I have
a Full DB Backup taken at 7am
a Diff backup taken at 9am
a Diff backup taken at 11am
Can I do this ?
Restore Full DB Backup of 7am with noreceovery followed by
Restore Diff Backup of 9am with noreceovery followed by
Restore Diff Backup of 11am with recovery.
I know I could do a restore of the Full DB Backup of 7am followed by Restore
of Diff backup of 11am with recovery and it will work... But i want to know
if the above will work whereby i restore both the differentials one after
another
Thanks
Hassan wrote:
> So I have
> a Full DB Backup taken at 7am
> a Diff backup taken at 9am
> a Diff backup taken at 11am
> Can I do this ?
> Restore Full DB Backup of 7am with noreceovery followed by
> Restore Diff Backup of 9am with noreceovery followed by
> Restore Diff Backup of 11am with recovery.
> I know I could do a restore of the Full DB Backup of 7am followed by Restore
> of Diff backup of 11am with recovery and it will work... But i want to know
> if the above will work whereby i restore both the differentials one after
> another
> Thanks
Did you try it?
CREATE DATABASE foob;
GO
BACKUP DATABASE foob TO DISK = 'C:\foob.bak' WITH INIT;
BACKUP DATABASE foob TO DISK = 'C:\foob1.bak' WITH DIFFERENTIAL, INIT;
BACKUP DATABASE foob TO DISK = 'C:\foob2.bak' WITH DIFFERENTIAL, INIT;
GO
USE master
RESTORE DATABASE foob FROM DISK = 'C:\foob.bak' WITH REPLACE,
NORECOVERY;
RESTORE DATABASE foob FROM DISK = 'C:\foob1.bak' WITH NORECOVERY;
RESTORE DATABASE foob FROM DISK = 'C:\foob2.bak' WITH RECOVERY;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hello Hassan,
You can do that; but will not make much difference. This is more than enough
to restore the FULL database backup with NORECOVERY follwed with
the last DIFFERENTIAL backup with RECOVERY. But for testing you could try
restore both DIFFERENTIAL backup and it will work fine..
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e7azxe8GHHA.3668@.TK2MSFTNGP02.phx.gbl...
> So I have
> a Full DB Backup taken at 7am
> a Diff backup taken at 9am
> a Diff backup taken at 11am
> Can I do this ?
> Restore Full DB Backup of 7am with noreceovery followed by
> Restore Diff Backup of 9am with noreceovery followed by
> Restore Diff Backup of 11am with recovery.
> I know I could do a restore of the Full DB Backup of 7am followed by
> Restore of Diff backup of 11am with recovery and it will work... But i
> want to know if the above will work whereby i restore both the
> differentials one after another
> Thanks
>
sql

Can I do this ?

So I have
a Full DB Backup taken at 7am
a Diff backup taken at 9am
a Diff backup taken at 11am
Can I do this ?
Restore Full DB Backup of 7am with noreceovery followed by
Restore Diff Backup of 9am with noreceovery followed by
Restore Diff Backup of 11am with recovery.
I know I could do a restore of the Full DB Backup of 7am followed by Restore
of Diff backup of 11am with recovery and it will work... But i want to know
if the above will work whereby i restore both the differentials one after
another
ThanksHassan wrote:
> So I have
> a Full DB Backup taken at 7am
> a Diff backup taken at 9am
> a Diff backup taken at 11am
> Can I do this ?
> Restore Full DB Backup of 7am with noreceovery followed by
> Restore Diff Backup of 9am with noreceovery followed by
> Restore Diff Backup of 11am with recovery.
> I know I could do a restore of the Full DB Backup of 7am followed by Restore
> of Diff backup of 11am with recovery and it will work... But i want to know
> if the above will work whereby i restore both the differentials one after
> another
> Thanks
Did you try it?
CREATE DATABASE foob;
GO
BACKUP DATABASE foob TO DISK = 'C:\foob.bak' WITH INIT;
BACKUP DATABASE foob TO DISK = 'C:\foob1.bak' WITH DIFFERENTIAL, INIT;
BACKUP DATABASE foob TO DISK = 'C:\foob2.bak' WITH DIFFERENTIAL, INIT;
GO
USE master
RESTORE DATABASE foob FROM DISK = 'C:\foob.bak' WITH REPLACE,
NORECOVERY;
RESTORE DATABASE foob FROM DISK = 'C:\foob1.bak' WITH NORECOVERY;
RESTORE DATABASE foob FROM DISK = 'C:\foob2.bak' WITH RECOVERY;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hello Hassan,
You can do that; but will not make much difference. This is more than enough
to restore the FULL database backup with NORECOVERY follwed with
the last DIFFERENTIAL backup with RECOVERY. But for testing you could try
restore both DIFFERENTIAL backup and it will work fine..
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e7azxe8GHHA.3668@.TK2MSFTNGP02.phx.gbl...
> So I have
> a Full DB Backup taken at 7am
> a Diff backup taken at 9am
> a Diff backup taken at 11am
> Can I do this ?
> Restore Full DB Backup of 7am with noreceovery followed by
> Restore Diff Backup of 9am with noreceovery followed by
> Restore Diff Backup of 11am with recovery.
> I know I could do a restore of the Full DB Backup of 7am followed by
> Restore of Diff backup of 11am with recovery and it will work... But i
> want to know if the above will work whereby i restore both the
> differentials one after another
> Thanks
>

can i create a database from backup file

hi

is it possible to create a database using backup in sql server DE?

like we can do in full edition using restore option........

yes its possible to create a db by restoring it from backup file.......regardless of the edition i believe.......|||In order to RESTORE a non-existant database, it may be necessary to first create an 'empty' database with the same name, and then RESTORE over the top of that.|||Be aware that the files might not be stored on the same location as the original files were (e.g. the drive letter of the former server does not exists). You will have to use the with move option then (or simply use the gui and specify other file locations as you will see during the recovery that the original file locations are stored within the backup set.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Can I carry my existing DB users to a new server and restore??

Hi,

Quick question, I have about 20 users in my local server and database. We
are looking to restore that database to a new server in a new network and
still be able to retain the database users.

We have created all 20 logins in the new server (exact names) but when we
restore the database I do not see the users anymore!

Is there something we can do to preserve them or a script we can run to link
them again?

Thanks a bunch!!!

S"SD" <npspam@.nowhere.net> wrote in message
news:138249382eb68f5a6766a476adc3da38@.news.teranew s.com...
> Hi,
> Quick question, I have about 20 users in my local server and database. We
> are looking to restore that database to a new server in a new network and
> still be able to retain the database users.
> We have created all 20 logins in the new server (exact names) but when we
> restore the database I do not see the users anymore!

sp_change_users_login is probably what you need.

> Is there something we can do to preserve them or a script we can run to
link
> them again?
> Thanks a bunch!!!
> S
>|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Jtjc.122289$e17.75072@.twister.nyroc.rr.com>...
> "SD" <npspam@.nowhere.net> wrote in message
> news:138249382eb68f5a6766a476adc3da38@.news.teranew s.com...
> > Hi,
> > Quick question, I have about 20 users in my local server and database. We
> > are looking to restore that database to a new server in a new network and
> > still be able to retain the database users.
> > We have created all 20 logins in the new server (exact names) but when we
> > restore the database I do not see the users anymore!
> sp_change_users_login is probably what you need.
>
> > Is there something we can do to preserve them or a script we can run to
> link
> > them again?
> > Thanks a bunch!!!
> > S

You can run the following script to generate a create script for all
of your users. It will generate a create statement for each login
using the encrypted password and SID. Replace "MY_DB" in the script
with your database name, then run the script against your source
database. Copy the output of the script to your destination server and
run it. Then, when you restore your database, your users will
automatically be synched with their logins. Hope this helps!

Lisa

-----
--Description: Scripts logins for one db
--*****IMPORTANT replace MY_DB with the name of the database that
holds the users that you need scripted

-- Begin Script, Create sp_help_revlogin procedure --
USE master
GO
SET NOCOUNT ON
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_MY_DBLogins @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)

IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

ELSE
DECLARE login_curs CURSOR FOR
SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus,
@.binpwd
IF (@.@.fetch_status = -1)
BEGIN
--PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd
+ ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' +
@.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus,
@.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
-- End Script --
--
EXEC master..sp_help_MY_DBLogins
--drop proc sp_help_MY_DBLogins

Can anyone help solve this puzzle?

I have one SQL instance that is giving me problems: everytime I restore a
database to it, the database has many consistency errors.
I have 3 servers I can test with: Server A, Server B, and Server C. Server
C is the one that's giving me problems.
I run a DBCC CheckDB on a database on Server A, then back it up. If I then
try to restore that backup on Server C, the backup works but DBCC CheckDB
reveals numerous errors. However, I can take that same backup file and
restore it on Server B, and everything is fine.
I can spend lots of time with my corrupt database on Server C and using DBCC
CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
get the database to report that it has no consitency errors. HOWEVER, if I
then try to back up that database and restore it on either Server A or
Server B, the restore works ok, but the database again is full of
consistency errors. Restore/backup works with no problems whatsoever
between Server A and Server B.
All of these problems apply not only to backup/restore, but also if I try to
just detach from Server A (or B) and attach to Server C.
I have sees several errors in the SQL log, on Server C, 604 (and some
others). I wish I had the complete list but I have re-installed SQL and
lost the logs.
I thought maybe it was an problem with the install, but as I just mentioned,
I completely re-installed on Server C, but the problems continue. To this
point in the current logs, there are no errors.
At this point I am thinking hardware, but the network guys insist that it
has passed all tests. Next I am going to run SQLIOStress (
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
and see if that turns anything up.
Oh, info on the servers:
Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
(Build 2195 SP3)
Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 2003
Standard Edition (Build 3790.srv03_rtm.030324-2048)
Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, no
difference in behavior
Any help/suggestions/links would be greatly appreciated. Please post back
here so that maybe any other unlucky soul that has this problem can benefit
from the smart folks out there.
thanks,
Greg
gjleclair AT hotmail DOT comGreg,
Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
server A.
Step 2: see if you are still having trouble.
I wouldn't be so sure it's server C with the problem. Server A, which
has no SQL Server service packs installed, created the backup.
You mentioned briefly a problem detaching from B and attaching to C. If
that is a problem that is reproducible from scratch without any contact
with Server A, post the details of what's going on with that.
There are hardware issues that can cause this kind of problem that might
not be called hardware problems, such as the one described in
http://support.microsoft.com:80/support/kb/articles/q268/4/81.asp, but I
doubt you'll get much help asking why things aren't working on an
non-updated server.
SK
Greg wrote:
>I have one SQL instance that is giving me problems: everytime I restore a
>database to it, the database has many consistency errors.
>I have 3 servers I can test with: Server A, Server B, and Server C. Server
>C is the one that's giving me problems.
>I run a DBCC CheckDB on a database on Server A, then back it up. If I then
>try to restore that backup on Server C, the backup works but DBCC CheckDB
>reveals numerous errors. However, I can take that same backup file and
>restore it on Server B, and everything is fine.
>I can spend lots of time with my corrupt database on Server C and using DBCC
>CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
>get the database to report that it has no consitency errors. HOWEVER, if I
>then try to back up that database and restore it on either Server A or
>Server B, the restore works ok, but the database again is full of
>consistency errors. Restore/backup works with no problems whatsoever
>between Server A and Server B.
>All of these problems apply not only to backup/restore, but also if I try to
>just detach from Server A (or B) and attach to Server C.
>I have sees several errors in the SQL log, on Server C, 604 (and some
>others). I wish I had the complete list but I have re-installed SQL and
>lost the logs.
>I thought maybe it was an problem with the install, but as I just mentioned,
>I completely re-installed on Server C, but the problems continue. To this
>point in the current logs, there are no errors.
>At this point I am thinking hardware, but the network guys insist that it
>has passed all tests. Next I am going to run SQLIOStress (
>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
>and see if that turns anything up.
>
>Oh, info on the servers:
>Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
>(Build 2195 SP3)
>Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 2003
>Standard Edition (Build 3790.srv03_rtm.030324-2048)
>Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
>(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, no
>difference in behavior
>
>Any help/suggestions/links would be greatly appreciated. Please post back
>here so that maybe any other unlucky soul that has this problem can benefit
>from the smart folks out there.
>
>thanks,
>Greg
>gjleclair AT hotmail DOT com
>
>|||Thanks for the reply, Steve.
I understand that applying the service packs would make the most sense as a
starting point...but the powers that be do not want me to do that since
"that's only one that's working".
The reason I didn't think it was server A is that all interaction between
Server A and Server B (backup/restore, attach/detach) work fine. Any
interaction between Server B and Server C result in the same consistency
problems.
As an update the SQLIOstress failed out with a series of these errors:
>>>>*** ERROR: LSN for page 277534 is out of sequence. Expected:
605885 and found 601789 in sector 15. Probably a torn page.
>>>> Byte 513 in pattern is [>] and from read is [.]
Followed by this:
>>>>----
--
>>>>Expected pattern [C] in file for page 277534.
>>>>Bytes read = 8192
>>>>Potential torn write, lost write or stale read may have been
encountered
>>>>----
--
I'm guessing that we have a hardware issue. Any thoughts? It seems like it
is up to me to "prove" that the hardware is having issues but I'm not really
sure what those errors indicate.
Once again, any help is appreciated.
-Greg
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...
> Greg,
> Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
> server A.
> Step 2: see if you are still having trouble.
> I wouldn't be so sure it's server C with the problem. Server A, which
> has no SQL Server service packs installed, created the backup.
> You mentioned briefly a problem detaching from B and attaching to C. If
> that is a problem that is reproducible from scratch without any contact
> with Server A, post the details of what's going on with that.
> There are hardware issues that can cause this kind of problem that might
> not be called hardware problems, such as the one described in
> http://support.microsoft.com:80/support/kb/articles/q268/4/81.asp, but I
> doubt you'll get much help asking why things aren't working on an
> non-updated server.
> SK
> Greg wrote:
> >I have one SQL instance that is giving me problems: everytime I restore
a
> >database to it, the database has many consistency errors.
> >I have 3 servers I can test with: Server A, Server B, and Server C.
Server
> >C is the one that's giving me problems.
> >
> >I run a DBCC CheckDB on a database on Server A, then back it up. If I
then
> >try to restore that backup on Server C, the backup works but DBCC CheckDB
> >reveals numerous errors. However, I can take that same backup file and
> >restore it on Server B, and everything is fine.
> >
> >I can spend lots of time with my corrupt database on Server C and using
DBCC
> >CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to
finally
> >get the database to report that it has no consitency errors. HOWEVER, if
I
> >then try to back up that database and restore it on either Server A or
> >Server B, the restore works ok, but the database again is full of
> >consistency errors. Restore/backup works with no problems whatsoever
> >between Server A and Server B.
> >
> >All of these problems apply not only to backup/restore, but also if I try
to
> >just detach from Server A (or B) and attach to Server C.
> >
> >I have sees several errors in the SQL log, on Server C, 604 (and some
> >others). I wish I had the complete list but I have re-installed SQL and
> >lost the logs.
> >
> >I thought maybe it was an problem with the install, but as I just
mentioned,
> >I completely re-installed on Server C, but the problems continue. To
this
> >point in the current logs, there are no errors.
> >
> >At this point I am thinking hardware, but the network guys insist that it
> >has passed all tests. Next I am going to run SQLIOStress (
>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com
:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
> >and see if that turns anything up.
> >
> >
> >Oh, info on the servers:
> >
> >Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
> >(Build 2195 SP3)
> >Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server
2003
> >Standard Edition (Build 3790.srv03_rtm.030324-2048)
> >Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
> >(Build 2195 SP4) NOTE: the new install has no SQL service packs applied,
no
> >difference in behavior
> >
> >
> >Any help/suggestions/links would be greatly appreciated. Please post
back
> >here so that maybe any other unlucky soul that has this problem can
benefit
> >from the smart folks out there.
> >
> >
> >thanks,
> >Greg
> >
> >gjleclair AT hotmail DOT com
> >
> >
> >
> >
>|||Greg,
It sure sounds like a hardware issue, but I don't have any great
suggestions. If you haven't already looked, see if these Knowledge Base
articles help at all:
http://support.microsoft.com:80/support/kb/articles/q231/6/19.asp
(and the articles it references at the bottom)
http://support.microsoft.com/default.aspx?scid=kb;en-us;268481
It is sounding more like it's server C and not the service pack, but
you never know... It does seem a little strange that restore fails so
easily, yet you have to work to get SQLIOstress to error out.
SK
Greg wrote:
>Thanks for the reply, Steve.
>I understand that applying the service packs would make the most sense as a
>starting point...but the powers that be do not want me to do that since
>"that's only one that's working".
>The reason I didn't think it was server A is that all interaction between
>Server A and Server B (backup/restore, attach/detach) work fine. Any
>interaction between Server B and Server C result in the same consistency
>problems.
>As an update the SQLIOstress failed out with a series of these errors:
>
>
>>>>*** ERROR: LSN for page 277534 is out of sequence. Expected:
>>>>
>>>>
>605885 and found 601789 in sector 15. Probably a torn page.
>
>>>>Byte 513 in pattern is [>] and from read is [.]
>>>>
>>>>
>Followed by this:
>
>>>>----
>>>>
>>>>
>--
>
>>>>Expected pattern [C] in file for page 277534.
>>>>Bytes read = 8192
>>>>Potential torn write, lost write or stale read may have been
>>>>
>>>>
>encountered
>
>>>>----
>>>>
>>>>
>--
>I'm guessing that we have a hardware issue. Any thoughts? It seems like it
>is up to me to "prove" that the hardware is having issues but I'm not really
>sure what those errors indicate.
>Once again, any help is appreciated.
>-Greg
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...
>
>>Greg,
>>Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
>>server A.
>>Step 2: see if you are still having trouble.
>>I wouldn't be so sure it's server C with the problem. Server A, which
>>has no SQL Server service packs installed, created the backup.
>>You mentioned briefly a problem detaching from B and attaching to C. If
>>that is a problem that is reproducible from scratch without any contact
>>with Server A, post the details of what's going on with that.
>>There are hardware issues that can cause this kind of problem that might
>>not be called hardware problems, such as the one described in
>>http://support.microsoft.com:80/support/kb/articles/q268/4/81.asp, but I
>>doubt you'll get much help asking why things aren't working on an
>>non-updated server.
>>SK
>>Greg wrote:
>>
>>I have one SQL instance that is giving me problems: everytime I restore
>>
>a
>
>>database to it, the database has many consistency errors.
>>I have 3 servers I can test with: Server A, Server B, and Server C.
>>
>Server
>
>>C is the one that's giving me problems.
>>I run a DBCC CheckDB on a database on Server A, then back it up. If I
>>
>then
>
>>try to restore that backup on Server C, the backup works but DBCC CheckDB
>>reveals numerous errors. However, I can take that same backup file and
>>restore it on Server B, and everything is fine.
>>I can spend lots of time with my corrupt database on Server C and using
>>
>DBCC
>
>>CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to
>>
>finally
>
>>get the database to report that it has no consitency errors. HOWEVER, if
>>
>I
>
>>then try to back up that database and restore it on either Server A or
>>Server B, the restore works ok, but the database again is full of
>>consistency errors. Restore/backup works with no problems whatsoever
>>between Server A and Server B.
>>All of these problems apply not only to backup/restore, but also if I try
>>
>to
>
>>just detach from Server A (or B) and attach to Server C.
>>I have sees several errors in the SQL log, on Server C, 604 (and some
>>others). I wish I had the complete list but I have re-installed SQL and
>>lost the logs.
>>I thought maybe it was an problem with the install, but as I just
>>
>mentioned,
>
>>I completely re-installed on Server C, but the problems continue. To
>>
>this
>
>>point in the current logs, there are no errors.
>>At this point I am thinking hardware, but the network guys insist that it
>>has passed all tests. Next I am going to run SQLIOStress (
>>
>>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com
>>
>:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
>
>>and see if that turns anything up.
>>
>>Oh, info on the servers:
>>Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
>>(Build 2195 SP3)
>>Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server
>>
>2003
>
>>Standard Edition (Build 3790.srv03_rtm.030324-2048)
>>Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
>>(Build 2195 SP4) NOTE: the new install has no SQL service packs applied,
>>
>no
>
>>difference in behavior
>>
>>Any help/suggestions/links would be greatly appreciated. Please post
>>
>back
>
>>here so that maybe any other unlucky soul that has this problem can
>>
>benefit
>
>>from the smart folks out there.
>>
>>thanks,
>>Greg
>>gjleclair AT hotmail DOT com
>>
>>
>>
>
>

Can anyone help solve this puzzle?

I have one SQL instance that is giving me problems: everytime I restore a
database to it, the database has many consistency errors.
I have 3 servers I can test with: Server A, Server B, and Server C. Server
C is the one that's giving me problems.
I run a DBCC CheckDB on a database on Server A, then back it up. If I then
try to restore that backup on Server C, the backup works but DBCC CheckDB
reveals numerous errors. However, I can take that same backup file and
restore it on Server B, and everything is fine.
I can spend lots of time with my corrupt database on Server C and using DBCC
CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
get the database to report that it has no consitency errors. HOWEVER, if I
then try to back up that database and restore it on either Server A or
Server B, the restore works ok, but the database again is full of
consistency errors. Restore/backup works with no problems whatsoever
between Server A and Server B.
All of these problems apply not only to backup/restore, but also if I try to
just detach from Server A (or B) and attach to Server C.
I have sees several errors in the SQL log, on Server C, 604 (and some
others). I wish I had the complete list but I have re-installed SQL and
lost the logs.
I thought maybe it was an problem with the install, but as I just mentioned,
I completely re-installed on Server C, but the problems continue. To this
point in the current logs, there are no errors.
At this point I am thinking hardware, but the network guys insist that it
has passed all tests. Next I am going to run SQLIOStress (
http://support.microsoft.com/defaul...&NoWebContent=1 )
and see if that turns anything up.
Oh, info on the servers:
Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
(Build 2195 SP3)
Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 2003
Standard Edition (Build 3790.srv03_rtm.030324-2048)
Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, no
difference in behavior
Any help/suggestions/links would be greatly appreciated. Please post back
here so that maybe any other unlucky soul that has this problem can benefit
from the smart folks out there.
thanks,
Greg
gjleclair AT hotmail DOT comGreg,
Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
server A.
Step 2: see if you are still having trouble.
I wouldn't be so sure it's server C with the problem. Server A, which
has no SQL Server service packs installed, created the backup.
You mentioned briefly a problem detaching from B and attaching to C. If
that is a problem that is reproducible from scratch without any contact
with Server A, post the details of what's going on with that.
There are hardware issues that can cause this kind of problem that might
not be called hardware problems, such as the one described in
http://support.microsoft.com:80/sup.../q268/4/81.asp, but I
doubt you'll get much help asking why things aren't working on an
non-updated server.
SK
Greg wrote:

quote:

---

>I have one SQL instance that is giving me problems: everytime I restore a
>database to it, the database has many consistency errors.
>I have 3 servers I can test with: Server A, Server B, and Server C. Server
>C is the one that's giving me problems.
>I run a DBCC CheckDB on a database on Server A, then back it up. If I then
>try to restore that backup on Server C, the backup works but DBCC CheckDB
>reveals numerous errors. However, I can take that same backup file and
>restore it on Server B, and everything is fine.
>I can spend lots of time with my corrupt database on Server C and using DBC
C
>CheckDB (repair_rebuild or repair_allow_data_loss) and dbreindex to finally
>get the database to report that it has no consitency errors. HOWEVER, if I
>then try to back up that database and restore it on either Server A or
>Server B, the restore works ok, but the database again is full of
>consistency errors. Restore/backup works with no problems whatsoever
>between Server A and Server B.
>All of these problems apply not only to backup/restore, but also if I try t
o
>just detach from Server A (or B) and attach to Server C.
>I have sees several errors in the SQL log, on Server C, 604 (and some
>others). I wish I had the complete list but I have re-installed SQL and
>lost the logs.
>I thought maybe it was an problem with the install, but as I just mentioned
,
>I completely re-installed on Server C, but the problems continue. To this
>point in the current logs, there are no errors.
>At this point I am thinking hardware, but the network guys insist that it
>has passed all tests. Next I am going to run SQLIOStress (
>http://support.microsoft.com/defaul...&NoWebContent=1 )
>and see if that turns anything up.
>
>Oh, info on the servers:
>Server A: SQL: 8.00.194 RTM Enterprise Edition OS: Windows 2000
>(Build 2195 SP3)
>Server B: SQL: 8.00.760 SP3 Developer Edition OS: Windows Server 200
3
>Standard Edition (Build 3790.srv03_rtm.030324-2048)
>Server C: SQL: 8.00.760 SP3 Enterprise Edition OS: Windows 2000
>(Build 2195 SP4) NOTE: the new install has no SQL service packs applied, n
o
>difference in behavior
>
>Any help/suggestions/links would be greatly appreciated. Please post back
>here so that maybe any other unlucky soul that has this problem can benefit
>from the smart folks out there.
>
>thanks,
>Greg
>gjleclair AT hotmail DOT com
>
>

---

|||Thanks for the reply, Steve.
I understand that applying the service packs would make the most sense as a
starting point...but the powers that be do not want me to do that since
"that's only one that's working".
The reason I didn't think it was server A is that all interaction between
Server A and Server B (backup/restore, attach/detach) work fine. Any
interaction between Server B and Server C result in the same consistency
problems.
As an update the SQLIOstress failed out with a series of these errors:

quote:

---

605885 and found 601789 in sector 15. Probably a torn page.[QUOTE]
Followed by this:
[QUOTE]
--[QUOTE]
encountered[QUOTE]
--
I'm guessing that we have a hardware issue. Any thoughts? It seems like it
is up to me to "prove" that the hardware is having issues but I'm not really
sure what those errors indicate.
Once again, any help is appreciated.
-Greg
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...[QUOTE]
> Greg,
> Step 1: install SQL Server service pack 3a and Win2K service pack 4 on
> server A.
> Step 2: see if you are still having trouble.
> I wouldn't be so sure it's server C with the problem. Server A, which
> has no SQL Server service packs installed, created the backup.
> You mentioned briefly a problem detaching from B and attaching to C. If
> that is a problem that is reproducible from scratch without any contact
> with Server A, post the details of what's going on with that.
> There are hardware issues that can cause this kind of problem that might
> not be called hardware problems, such as the one described in
> http://support.microsoft.com:80/sup.../q268/4/81.asp, but I
> doubt you'll get much help asking why things aren't working on an
> non-updated server.
> SK
> Greg wrote:
>
a[QUOTE]
Server[QUOTE]
then[QUOTE]
DBCC[QUOTE]
finally[QUOTE]
I[QUOTE]
to[QUOTE]
mentioned,[QUOTE]
this[QUOTE]
>http://support.microsoft.com/defaul...t.microsoft.com

---

:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )

quote:

---

2003[QUOTE]
no[QUOTE]
back[QUOTE]
benefit[QUOTE]
>

---

|||Greg,
It sure sounds like a hardware issue, but I don't have any great
suggestions. If you haven't already looked, see if these Knowledge Base
articles help at all:
http://support.microsoft.com:80/sup...s/q231/6/19.asp
(and the articles it references at the bottom)
http://support.microsoft.com/defaul...kb;en-us;268481
It is sounding more like it's server C and not the service pack, but
you never know... It does seem a little strange that restore fails so
easily, yet you have to work to get SQLIOstress to error out.
SK
Greg wrote:

quote:

---

>Thanks for the reply, Steve.
>I understand that applying the service packs would make the most sense as a
>starting point...but the powers that be do not want me to do that since
>"that's only one that's working".
>The reason I didn't think it was server A is that all interaction between
>Server A and Server B (backup/restore, attach/detach) work fine. Any
>interaction between Server B and Server C result in the same consistency
>problems.
>As an update the SQLIOstress failed out with a series of these errors:
>
>
>605885 and found 601789 in sector 15. Probably a torn page.
>
>Followed by this:
>
>
>--
>
>encountered
>
>--
>I'm guessing that we have a hardware issue. Any thoughts? It seems like i
t
>is up to me to "prove" that the hardware is having issues but I'm not reall
y
>sure what those errors indicate.
>Once again, any help is appreciated.
>-Greg
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23bYZld26DHA.2556@.TK2MSFTNGP09.phx.gbl...
>
>a
>
>Server
>
>then
>
>DBCC
>
>finally
>
>I
>
>to
>
>mentioned,
>
>this
>
>:80/support/kb/articles/q231/6/19.asp&NoWebContent=1 )
>
>2003
>
>no
>
>back
>
>benefit
>
>
>

---