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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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_WRITEFILEGA THER" . 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
ASKER