how to verify restore is successful?

[conniefu]

hi,

I was trying to restore a user database from dump using nightly free utilty. My question is how to create a test case to verify the restore is good. I tried to modify some values in a table after the backup. But after the restore, the data is still the changed value.

Thanks for any input

Connie

 

[JeanNiBee]

Hi

Show us your backup and restore commands.

Usually If you don't get any errors in the error log, your restore worked.

Quite possibly in the 'flow' of backing up, modifying and then restoring you may be restoring the TX log that has the 'modified' change. If so, yes it will always have the modified value.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[conniefu]

Hi JeanNiBee,

I used a menu to restore/backup the databases. do you think i should truncate the TX log before restore for test purpose?

Thanks,

 

[JeanNiBee]

Hey

Menu? Which application are you using?

I don't think you need to truncate the transaction logs. Not yet anyway.

Here's what I thought 'may' be happenning.

1- You do a full backup of db.
2- You modify some info.
3- Behind the scenes, some process dumps the TX logs.. our company does this every 5 minutes so we have a very small window of missing info.
4- You restore the db.. (which might automatically retore the LAST dumped tx logs).
5- You have the Db, restored, WITH the modified info in it.


Let me know. Logically there is no reason for this to be happenning.




-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[sybaseguru]

You can use the somehow undocumented command dbcc dbinfo(<database_name>) to check when the database was last loaded . Do the following and look for an entry called &quot;dbi_curseqnum&quot;. The timestamp should give it away

dbcc traceon(3604)
go
dbcc dbinfo(<DATABASE_NAME)
go
Let me show you an example of output

DBINFO STRUCTURE:

offset 0=dbi_lastlr: (1158635,7)
offset 8=dbi_dpbegxact: (1128769,21)
offset 16=dbi_oldseqnum: Jan 1 1900 12:00:00:000AM

offset 24=dbi_curseqnum: May 9 2003 1:42:26:570PM

offset 32=dbi_nextseqnum: Jan 1 1900 12:00:00:000AM
offset 40=dbi_deallocpgs: 1
offset 44=dbi_2ndary_trunc_time_set: May 9 2003 1:41:40:203PM
offset 52=dbi_nextcheckpt: (empty)
offset 60=dbi_dbid: 4
offset 62=dbi_dbisize: 510
[/color]
So this database was loaded OK on May 9 2003.

Hope this helps

 

[conniefu]

Hi,

Thanks for all the help. I am using &quot;nightly&quot; free utility

http://www.edbarlow.com/document/nightly.htm#DIRECTORY_LAYOUT

for backup/restore. it has simple menu to manually backup/restore databases.

I restored the db around 9:50. then I compare the outputs from &quot;dbcc dbinfo(mydb)&quot; before and after restore.
before ----->>>> offset 24=dbi_curseqnum: Jun 11 2003 9:34:45:706AMg€0J€0J@D(@&4 $f€ $fl

after ------>>>>offset 24=dbi_curseqnum: Jun 11 2003 9:52:45:706AMg€0&æ€0&æ@D(@&4 $f€ $fl

the timestamp for &quot;before&quot; is suspicious 'cause it looks like it was just reloaded by someone. Please help me understand it. Thanks!

Connie

 

[sybaseguru]

Yes that looks like it. Now go to sybase install directory v12 & above $SYBASE/$SYBASE_ASE/install or $SYBASE/install and look at the backup errorlog. Normally there is the ASE errorlog and backup server errorlog there plus may be monitor server. Go through the errorlog and see when this database was loaded. This should show your load at 9:52 and the previous load.

 

[conniefu]

I can't find any info about database loading in the error logs. I just attached part of the log.

00:00000:00025:2003/06/10 09:38:38.39 kernel Cannot read, host process disconnected: DEVNTEDIT 148 spid: 25
00:00000:00037:2003/06/10 09:38:38.40 kernel Cannot read, host process disconnected: DEVNTEDIT 247 spid: 37
00:00000:00034:2003/06/10 09:38:38.40 kernel Cannot read, host process disconnected: DEVNTEDIT 247 spid: 34
00:00000:00038:2003/06/10 09:38:38.46 kernel Cannot read, host process disconnected: DEVNTEDIT 301 spid: 38
00:00000:00044:2003/06/10 09:38:38.46 kernel Cannot read, host process disconnected: DEVNTEDIT 148 spid: 44
00:00000:00027:2003/06/11 09:45:27.60 server DBCC TRACEON 3604, SPID 27
00:00000:00027:2003/06/11 09:54:43.31 server DBCC TRACEON 3604, SPID 27
00:00000:00027:2003/06/11 09:59:22.57 server DBCC TRACEON 3604, SPID 27
00:00000:00027:2003/06/11 10:00:28.62 server DBCC TRACEON 3604, SPID 27
00:00000:00027:2003/06/11 10:20:50.62 server DBCC TRACEOFF 3604, SPID 27
00:00000:00018:2003/06/11 13:25:45.87 server DBCC TRACEON 3604, SPID 18
00:00000:00018:2003/06/11 13:26:27.83 server DBCC TRACEOFF 3604, SPID 18

 

[sybaseguru]

NO NO I mean backup server errorlog not ASE errorlog

 

[conniefu]

It looks like the database was not restored at all yesterday morning.

Jun 9 15:58:51 2003: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Jun 9 15:58:51 2003: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Jun 9 15:58:51 2003: Backup Server: 4.58.1.1: Database grokras_db: 8574 kilobytes DUMPed.
Jun 9 15:58:51 2003: Backup Server: 3.42.1.1: DUMP is complete (database grokras_db).
Jun 11 16:35:10 2003: Backup Server: 4.41.1.1: Creating new disk file /usr/grok/backup/SYBASE/dbdumps/grok_db_dbdump.
Jun 11 16:35:10 2003: Backup Server: 6.28.1.1: Dumpfile name 'grok_db031620E93D' section number 1 mounted on disk file '
/usr/grok/backup/SYBASE/dbdumps/grok_db_dbdump'
Jun 11 16:35:12 2003: Backup Server: 4.58.1.1: Database grok_db: 2534 kilobytes DUMPed.
Jun 11 16:35:14 2003: Backup Server: 4.58.1.1: Database grok_db: 12780 kilobytes DUMPed.

 

[sybaseguru]

OK, do you want to restore it yourself. Do dbcc dbinfo before and after restoration and check the backup log as above and send the output

 

[conniefu]

sybaseguru,

Here is the dbinfo outputs before and after restore.

beforeffset 24=dbi_curseqnum: Jun 12 2003 1:33:57:743PMg€05r€05r@D(@&4 $f€ $fl

afterffset 24=dbi_curseqnum: Jun 12 2003 1:33:57:743PMg€05r€05r@D(@&4 $f€ $fl

I restored the database around 2:32pm (the local system time).

Jun 12 14:32: Restoring Database=grokras_db: file=grokras_db_dbdump.06-09-03-155852
Jun 12 14:32: ---------- BEGINNING RESTORE ----------
Jun 12 14:32: LOAD DATABASE grokras_db FROM '/usr/grok/backup/SYBASE/dbdumps/grokras_db_dbdump.06-09-03-155852'
Jun 12 14:32: Command Not Run As Debug Option Set
Jun 12 14:32: Done Load of grokras_db
Jun 12 14:32: ---------- RESTORE COMPLETED ----------

 

[JeanNiBee]

Um, your database isn't loading...

&quot;Command Not Run As Debug Option Set&quot;



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[conniefu]

JeanNiBee,

You are right, the script set debug mode by default. once i changed it, i am able to load the database. backup log also shows the loading information. )

Thanks very much! Also thanks sybaseguru for pointing the directions!

Connie