CODEWITHSUNDEEP
sql-serverbackuprestore
Aaron
Aaron

Reputation: 387

SQL Server database restore stuck at 99%

I have a SQL Server 2005 .BAK file (created with a maintenance plan) that I am trying to restore into a different instance of SQL Server 2005.

Regardless of whether I use the UI or RESTORE DATABASE, the restore process seems to halt at 99%. If I use RESTORE VERIFYONLY FROM DISK='mybackup.bak' it reports that my backup is valid.

Interestingly, immediately after I start the restore process, a 43 GB transaction log file appears in my MSSQL\DATA directory. The .BAK file is 60 MB. The system has more than enough free space for this recovery.

Any suggestions on other restore strategies I should try? I'm going to let the restore run overnight and see what happens.

Thanks

Upvotes: 2

Views: 9372

Answers (1)

Ryk
Ryk

Reputation: 3102

If you have a 43Gb transaction log, depending on the speed of your disks, the restore will take a while. What that means is that when you backed up your db, it had a 43Gb log file and it was empty. So when you restore, the SQL Server has to go and allocate 43Gb for the log file, and it has to physically go and zero out 43Gb, and this takes a while.

What I suggest is to wait a while for restore to complete, couple hours maybe, or do a transaction log backup on production, then shrink the trans log file to say 100Mb and then do a backup, and restore from that.

IT is very important to understand recovery models, and backup models used. Dont just slap a db on production and let it run, if you dont manage it, you will end up with big problems.

Upvotes: 9

Related Questions