Find a backup to rstore from takes 'Forever'
-
Hi All!
As a part of me development tasks, I have to restore databases frequently from devices - local harddisk.Most times it's just to set 'Device' as source, and click on the button to find the .bak file, ans is finished within seconds.
Other times, it takes, well not forever, but enough time to go to the coffee kitchen, brew a cop of coffee, come back, read todays news, before the manager answers.
And next time it's again a question of seconds.Any hints to what makes the difference, and solution's are appriciated very much
Best regards
Edvard Korsbæk
-
I'll assume you are referring to the time it takes to perform those restores using SSMS, since you mentioned setting the "device". Most of the time I see that taking forever is dealing with backup sets, where one bak file has multiple backups to it. SSMS can take a while trying to scan that file.
If this is a normal occurrence for you to "refresh" development with recent backups taken from production, I'd recommend taking a look at PowerShell. There is an open source project called dbatools that has a Restore-DbaDatabase command that will make your process so much easier. You can see the examples in the documentation on ways to utilize it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
Hi,
try to click the restore button from the instance, not from the database context.
We had the same problem. If we try to restore, and use the context menü from the database, every backupfile is read and listed in the windows .
If you use the restore buttom from the instance, you are able to choose your backup file within a few seconds.Kind regards,
Andreas -
Hi!
I can understand, that you do not understand where i am.
The final rstore takes a few sec's. It pretty smal databases.
On my developement PC, I have +250 different DB's
When i have to look into a problem, my custumers sends me an .bak file, and i place it in D:\_Temp\backfiles.next thing is, that i go into the manager SSMS, and try to read the .bak file into my database.
Rightclik on the database, select Restore, and then 'Restore from device'.
And now comes "forever", when i try to find the backup in D:\_Temp\backfiles.
Most times it takes seconds, and other times you just wait for explorer open - This morning + 10 minutes.
When it opens it takes a couple of museclicks to find the backup, and a few seconds to restore the DB. The average backup size is about 70 MB.Best Regards
Edvard Korsbæk
- Edvard Korsbæk - Monday, November 27, 2017 5:46 AM
Hi,
we had the same kind of "problem" with some kind of databases. If we got a lot of backups, we take log backups every 5 minutes, it takes 15 minutes, to show the restore windows in ssms. I think, ssms tries to read every header from the backup-files, to show the right information in the windows.
But if we try to restore from the instance, not from the database menue, the windows loads in a view seconds, and we are able to restore the database in a view seconds.
Sorry, I am not a native speaking person, but I try to explain as good as possible.
Have you ever tried, to take T-SQL script to check the hearder information only, if this task will take as much time as to load the restore windows in SSMS?
regards,
Andreas - But if we try to restore from the instance, not from the database menue, the windows loads in a view seconds, and we are able to restore the database in a view seconds.
[SNIP]What do you mean with this?
Not in SSMS or?
Best regards
Edvard Korsbæk
-
Hi,
I hope the screenshots could explain it much better than I could:
This way takes only view seconds to show the restore windows , where I can choose the device to restore from.
If I choose this way to restore some databases, it takes about 10 minutes to display the windows, where I can choose the backup device.🙂
-
Took old me 3 looks to find out what the difference is.
T H A N K S
Edvard Korsbæk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply