SQL 2000 DB restore issue

[sidradical]

Hi All,

I was hoping someone could help me with an annoying issue that I having with regards to restoring a DB on SQL 2000.

Here is my scenario.

I have taken a manual backup of two SQL 2000 DB’s from one server. These were then restored onto another server with SQL 2000. I selected a force restore and It completed error free. I am then able to access the data until rebooting the server which results in the DB’s becoming “suspect” at which point the data is gone.

I have done this before following the same process and I have never had this issue.

Any help would be appreciated.

Thanks,

 

[mutley1]

Could be a corruption in the data once it has loaded. I'd start off with reloading them so they "seem" to be OK, then immediately run a DBCC CHECKDB in QA and see if it comes back with anything dodgy.

Post back if there is any kind of error, or if that comes back error free then post if there is anything else you have checked.

HTH,

M.

 

[mutley1]

Or another quick thought. Are the data / log files on a SAN drive or somewhere where rebooting the server might lose connectivity to that drive until manually restored, i.e. can the server see the disk they are on after reboot?

 

[sidradical]

Thanks for the reply. The backups are coming from a USB HD (Not that it should matter) however I will try to cpoy them locally before the restore to see if that helps.

The drives are local as well so logs and data are not going to any networked attached storage.

i'll let you know how I get on.

 

[mutley1]

OK - yeah there shouldn't bve a prob with where the backups are, it was the mdf / ldf location I was thinking of.

Other thoughts (after the CHECKDB), have a look in the SQL Server Log (should be easy to fine as it's recycled on the reboot) and see if there are any messages that are relevant. It may be that the drive space is low and there was not enough to recover the database on reboot?

These things are always harder when the machine isn't in front of you!!!!

 

[sidradical]

Here is the output from the DBCC CHECKDB

DBCC results for 'nice_reporter'.
DBCC results for 'sysobjects'.
There are 152 rows in 3 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 93 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 637 rows in 13 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 141 rows in 14 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 50 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 14 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 444 rows in 2 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 26 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'tblGroupCategory'.
There are 185 rows in 1 pages for object 'tblGroupCategory'.
DBCC results for 'tblLogo'.
There are 0 rows in 0 pages for object 'tblLogo'.
DBCC results for 'tblOutputDestinationType'.
There are 6 rows in 1 pages for object 'tblOutputDestinationType'.
DBCC results for 'tblOutputFormat'.
There are 8 rows in 1 pages for object 'tblOutputFormat'.
DBCC results for 'tblParameterType'.
There are 8 rows in 1 pages for object 'tblParameterType'.
DBCC results for 'tblReport'.
There are 169 rows in 7 pages for object 'tblReport'.
DBCC results for 'tblReportExecutionLog'.
There are 2396 rows in 30 pages for object 'tblReportExecutionLog'.
DBCC results for 'tblReportGroupCategory'.
There are 249 rows in 1 pages for object 'tblReportGroupCategory'.
DBCC results for 'tblReportHistory'.
There are 20 rows in 1 pages for object 'tblReportHistory'.
DBCC results for 'tblReportParameter'.
There are 2997 rows in 229 pages for object 'tblReportParameter'.
DBCC results for 'tblReportSchedule'.
There are 166 rows in 1 pages for object 'tblReportSchedule'.
DBCC results for 'tblReportScheduleAlert'.
There are 0 rows in 0 pages for object 'tblReportScheduleAlert'.
DBCC results for 'tblReportScheduleStatus'.
There are 2 rows in 1 pages for object 'tblReportScheduleStatus'.
DBCC results for 'tblRptFile'.
There are 27 rows in 1 pages for object 'tblRptFile'.
DBCC results for 'tblSchedule'.
There are 140 rows in 8 pages for object 'tblSchedule'.
DBCC results for 'tblStringLanguage'.
There are 0 rows in 0 pages for object 'tblStringLanguage'.
DBCC results for 'tblStringTranslation'.
There are 0 rows in 0 pages for object 'tblStringTranslation'.
DBCC results for 'tblTemplate'.
There are 27 rows in 2 pages for object 'tblTemplate'.
DBCC results for 'tblTemplateCategory'.
There are 6 rows in 1 pages for object 'tblTemplateCategory'.
DBCC results for 'tblTemplateParameter'.
There are 365 rows in 32 pages for object 'tblTemplateParameter'.
DBCC results for 'tblLanguage'.
There are 7 rows in 1 pages for object 'tblLanguage'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'tblDataBaseDetails'.
There are 1 rows in 1 pages for object 'tblDataBaseDetails'.
DBCC results for 'tblUpdateCount'.
There are 0 rows in 0 pages for object 'tblUpdateCount'.
DBCC results for 'tblScheduleDistribution'.
There are 581 rows in 9 pages for object 'tblScheduleDistribution'.
DBCC results for 'tblAdminParam'.
There are 15 rows in 1 pages for object 'tblAdminParam'.
DBCC results for 'tblDataEntity'.
There are 14 rows in 1 pages for object 'tblDataEntity'.
DBCC results for 'tblDistributionType'.
There are 9 rows in 1 pages for object 'tblDistributionType'.
DBCC results for 'tblExecutionStatus'.
There are 8 rows in 1 pages for object 'tblExecutionStatus'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'nice_reporter'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

[mutley1]

OK - 0 alloc or consistency errors. Anything in the SQL logs when it's recovering the DB during the reboot / service startup?

 

[mutley1]

This will get the DB back to normal status quickly, but only so you can see it / the data. The prob is finding the root cause (basically so you don't have to keep restoring if you reboot).

sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO

UPDATE master.dbo.sysdatabases 
SET Status = 24 
WHERE [Name] = 'nice_reporter'
GO

sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO