Link to home
Start Free TrialLog in
Avatar of ASME
ASME

asked on

SQL 2008 restore failed "The WITH MOVE clause can be used to relocate one or more files."

The error never occurred before. I tried restoring the .bak file to a different database, but the job runs and never completes or shows progress above 0% after an hour or more. the restore usually take 50 minutes.  please advise. thanks.
SOLUTION
Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ASME
ASME

ASKER

Thank you, both. These solutions were deployed, and the error no longer exists. However, the progress of the restore never reaches beyond 0%. I have tried queries as well as SS Management Studio. I am not certain if this is due to system resources or something else. I tested a restore with a 1GB database, and that worked successfully. The actual restore that we need is over 500GB. We have not experienced this issue on this database until a week ago. I may need to submit a separate request for assistance on this, but if you have any suggestions, they would be appreciated.
Are you seeing file I/O, using SSMS Actvity Monitor for example? With a 500Gb database I can imagine tha the progress indicator might take quite a while to go from zero to nonzero. Are you saying that you routinely restore databases of this size and it normally is fast?

What changed that led to needing to do the restore that produced the original error error message? Could that same change have affected performance as well?
Avatar of ASME

ASKER

Hello again. Something definitely changed, but no one here claims that a change was made. After further review, we found in the activity monitor that there was a wait_type 'PREEMPTIVE_OS_WRITEFILEGATHER" . THE FIX WAS IN SECPOL.MSC under Local Policy -> User Right Assignment -> Perform Volume Maintenance Tasks. We added all logins associated with the database and restore job. that allowed the restore to run successfully, even though it took 4 times as long as normal. It is curious why we needed to make this change; as I mentioned no one says they made any changes. Thank you again for your help.
Please review is issue is resolved