September 13, 2017 at 11:33 am
I am creating a new database from the backup I have. It has been running for half an hour now but still stuck at 0%. The backup file is in Amazon so I thought there could be some latency so I ended up moving the backup file to a local drive on the server. Now I am still not able to restore the database. I have a SQL script which I ran to see and this what I got. Can someone help?
September 13, 2017 at 11:57 am
Most likely, SQL is currently working on creating the actual files on disk and zeroing out the disk sectors that's using.
Depending on several things, the size of the database and log files (not the backup file,) and the performance of the disk you're trying to restore to, this could take a while.
One way to speed it up (which would require killing the restore job,) would be to turn on Instant File Initialization for the SQL Server service account. This, for the database file (but not the Transaction log!) skips the zeroing out of the sectors used for the database file, speeding up both restores and file growths.
September 13, 2017 at 12:02 pm
newdba2017 - Wednesday, September 13, 2017 11:33 AMI am creating a new database from the backup I have. It has been running for half an hour now but still stuck at 0%. The backup file is in Amazon so I thought there could be some latency so I ended up moving the backup file to a local drive on the server. Now I am still not able to restore the database. I have a SQL script which I ran to see and this what I got. Can someone help?
You might find this article informative at least, though it does not solve your issue.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 13, 2017 at 1:30 pm
It did work and yes I don't have instant file initialization so no wonder it was taking forever. The DB it self was about 400 GB.
September 13, 2017 at 1:40 pm
newdba2017 - Wednesday, September 13, 2017 1:30 PMIt did work and yes I don't have instant file initialization so no wonder it was taking forever. The DB it self was about 400 GB.
That'll do it.
September 14, 2017 at 2:08 am
newdba2017 - Wednesday, September 13, 2017 1:30 PMIt did work and yes I don't have instant file initialization so no wonder it was taking forever. The DB it self was about 400 GB.
If it's not throwing any error immediately and also not moving the progress % , its not necessary its an issue . It starts to show the progress eventually but might take time . Resource Monitor (on windows) can be looked at to confirm that the writes are happening . I faced similar issue and cancelled the restore twice but then looked at the 'writes' at resource monitor which indicated the restore is actually working. Most of the errors are thrown immediately otherwise.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply