DR testing

Question

DR testing

pglover

Right there with Babe

Points: 791
More actions
April 18, 2003 at 5:49 am

#159658

Hi, We had an interesting one yseterday. We were attempting a DR test on a SQL7 installation which we did not initially set up. The outfit that did said that it was bog standard except that it was Dictionary sort order and case sensitive. I restored the Veritas backup from tape, don't have the SQL Agent so then I attempted a Rebuildm to rebuild the master database. I took the default character set of 1252 and just selected dictionary order and case sensitive. Once completed I copied the master.mdf and ldf to the desired folder and attempted to start MSSQLServer service. Initially it failed with an NT internal error. Then once I did manage to get it to start and attempted a recovery of a SQL dump of the master database this failed. When I checked the errorlog I had the following
Warning: Unicode comparison flags 196608 in database differs from Server unicode comparison flags of 196609
Could not attach to the database because the character set, sort order, or unicode collation for the database differs from the server.
Is there any way of finding out what settings they did actually use?
April 21, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Run restore headeronly from disk = 'your master database backup filename' and you will see character set, sort order and unicode setting of your original SQL Server. Rebuild your system and go from there.

pglover Right there with Babe Points: 791 More actions · Answer 2

Thanks for that! I ran it on a 'spare' SQLServer server that I just happened to have and got the following

Sort Order 51 - which I have discovered is case sensitive, dict order

Code Page 1 - Was confused by this because when I go into Ent. Manager and properties it has Code Page CP1252

Unicode Locale id 1033 - which is general unicode

Unicode Comparison style 196608

But when I look in the errorlog after doing the rebuildm and recovering from the last backup I find the following

Recovery Complete

SQL Server collation is 'English' (id=1033)

comparison style is 196609

SQL Server non-unicode sort order is nocase_iso (id = 52)

SQL Server non-unicode character set is ISO_1 (id = 1)

When I look in the area to which it says it has restored the files, they are not there and MSSQLServer service will not start because of an internal error

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

You have different sort order setting in your SQL Server than the master database you tried to restore. That is the problem.

Rebuild master database with correct sort order which is 51 here and try the master database restoration again.

pglover Right there with Babe Points: 791 More actions · Answer 4

Yes, but when I did the rebuildm I selected the same settings as had been revealed to me from the restore headeronly, i.e. dict order, case sensitive, general unicode and character set 1252/iso_1. This is what is confusing me.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

I don't have test box here but different sort order / code page are definitely the place you have to look into and ensure the server you built has same sort order / code page as your master backup.

pglover Right there with Babe Points: 791 More actions · Answer 6

I ended up 'cheating' and doing a Veritas backup to tape while the SQL servr services were down and storing this as a special, not to be over-written tape. Then when I re-did the DR test I was fine because I didn't have to go through the rebuildm stage!

Have convinced the company that they need to get the SQL agent for backup exec to avoid this problem in future!

Thanks for the help and suggestions