June 20, 2008 at 6:10 am
SQL 2005 Restore running 2 hr 45 min, SQL 2000 restore took 1 hr 15 min. The database is 54GB. We just upgraded hardware but same OS(Windows 2003) but with SQL 2005.
June 20, 2008 at 7:28 am
It Does that the First Time on such a big DATABASE. I had a 79 GB Db and it took me.. approx 4 Hrs to restore.. after that it seems ok.. but even on SQL 2000 it took me long enough.. to do a production to test restore... Same hardware...
Anyway.. it is trying to upgrade the pages/extents i think...
After the restore.... change the Compatibility level and run DBCC UPDATEUSAGE ( ).. this will corrects pages and row count inaccuracies.
then ttry running a DBCC CHECKDB( ) WITh PHYSICAL_ONLY.
If Possible, try to REINDEX all indexes. Just for the sake of it... as you will still be in a Outage mode and why not make good use of the outage..
Maninder
www.dbanation.com
June 20, 2008 at 7:43 am
I thought maybe the first time it would be a possiblity but then I restored that db that already went through the 2005 restore to another SQL 2005 server and it ran just as long but I did notice that the Compatibilty was 80 and not 90. I will try a backup and restore of the same db with 90. I wouldn't think it would be that big of difference but it will take awhile to find out.
June 20, 2008 at 8:17 am
What is the RECOVERY MODe of your database. is it FULL/SIMPLE/BULKLOGGED.
What is the Size of your transaction LOG.
These Things will affect the RESTORE Process.
Maninder
www.dbanation.com
June 20, 2008 at 8:39 am
The recovery mode is simple. The log is 500MB.
June 20, 2008 at 8:42 am
I am sure you do, but just for the sake of asking:
how frequent are your backups?
Maninder
www.dbanation.com
June 20, 2008 at 8:48 am
How many Filegroups and how many file in each Filegroup you have for this DB.
Maninder
www.dbanation.com
June 20, 2008 at 8:50 am
Just one file group
June 20, 2008 at 8:51 am
We backup nightly but really the first time we hit an noticable issue with backup and restore for SQL2005.
June 20, 2008 at 10:47 am
did you try changing the cmptlevel to 90 and then do a backup and restore.. is it the same?
Maninder
www.dbanation.com
June 24, 2008 at 6:16 am
We finally got a chance to try restoring with the compatibility level at 90 it did not help.
June 24, 2008 at 7:01 am
Well, it might also depend on your hardware and the processes on the server, when you are attempting the restore.
Take a look here: http://support.microsoft.com/kb/924807
Maninder
www.dbanation.com
June 24, 2008 at 2:02 pm
Our problem ended up being hardware the admin found out that the server was missing battery write cache for the controllers.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply