September 22, 2017 at 3:02 pm
After restore a database database1 from production to a test server and called it database2, I did not do a full backup, but I did a differential backup for database2.
Now I would like to restore database database3 based on the backup of database 2.
So I go to management studio, right click restore - Source I chose database2, destination I chose database3.
Then I got a screen keep flashing, it shows some blinking message on top of the screen:
"Creating restore plan: selecting backupsets," the screen keep blinking and blinking and never to an end.
I thought I early asked a question about after restoring from a database, should we immediately do a full backup of the restored database, the answer is not necessary.
But if I don't do a full backup, it caused this issue.
Thanks
September 22, 2017 at 4:42 pm
sqlfriends - Friday, September 22, 2017 3:02 PMAfter restore a database database1 from production to a test server and called it database2, I did not do a full backup, but I did a differential backup for database2.
Now I would like to restore database database3 based on the backup of database 2.So I go to management studio, right click restore - Source I chose database2, destination I chose database3.
Then I got a screen keep flashing, it shows some blinking message on top of the screen:"Creating restore plan: selecting backupsets," the screen keep blinking and blinking and never to an end.
I thought I early asked a question about after restoring from a database, should we immediately do a full backup of the restored database, the answer is not necessary.
But if I don't do a full backup, it caused this issue.Thanks
May not have anything to do with the backups you have taken or not taken for the database - the issue is usually caused by having a significant number of backups in msdb. It's different in different versions of SSMS as well. Do you clean up the backup history using sp_delete_backuphistory on a regular basis?
Restores work much better if you use t-sql instead of the GUI in SSMS. There are other issues with the restores through SSMS which can be addressed by using t-sql for the restores.
Sue
September 22, 2017 at 4:55 pm
I do clean up histories every 2 months.
Also if I did a full backup for database2 instead of differential, then I run the same steps I described above, I don't have any problem, the database3 restored right away.
So I kind of think after a restore , I should immediately do a full backup of the restored database, so the backup chain can work correctly, and have no problem for subsequent restores.
I know T-SQL works in many cases, but management studio suppose to be a good tool too. and this is a simple and often used feature, thought this can be done on SSMS
Thanks,
September 22, 2017 at 5:09 pm
By the way the restore from production server to test server for database 2 is done by using powershell script.
September 22, 2017 at 5:16 pm
sqlfriends - Friday, September 22, 2017 4:55 PMI do clean up histories every 2 months.
Also if I did a full backup for database2 instead of differential, then I run the same steps I described above, I don't have any problem, the database3 restored right away.
So I kind of think after a restore , I should immediately do a full backup of the restored database, so the backup chain can work correctly, and have no problem for subsequent restores.
I know T-SQL works in many cases, but management studio suppose to be a good tool too. and this is a simple and often used feature, thought this can be done on SSMSThanks,
I would still think it's related to the history. But I'm also not following your order of what is restored when from what server. However, it has been this exact issue for several others and most I've seen were on 2014. Two months can be too much history but it depends on the number of databases, frequency of all the types of backups.
For the restores - actually t-sql works in ALL cases, not many (as long as it's a good back which is the case for anything no matter what). It is SSMS that will not work correctly in some cases. Depending on what it pulls up for the list of backups is at times not reliable in SSMS. I can query the msdb tables and always get the correct files needed to restore a database. And when something is often used, it is even more reason to have scripts. Scripts provide repeatable a processes. And if you ever work with a change control board, you better be able to do things in scripts.
Sue
September 22, 2017 at 5:26 pm
Ok I will do it in script.
However I just wonder why this happens on SSMS, is it a bug?
I don't have many backups on these server, only 4 small databases on the server about 1000 history backup files including full, differential and log.
September 22, 2017 at 5:28 pm
sqlfriends - Friday, September 22, 2017 5:09 PMBy the way the restore from production server to test server for database 2 is done by using powershell script.
Now that I read it more, You wrote:
I did not do a full backup, but I did a differential backup for database2.
Now I would like to restore database database3 based on the backup of database 2.
You can't do a restore using just a differential backup.
Sue
September 22, 2017 at 5:41 pm
sqlfriends - Friday, September 22, 2017 5:26 PMOk I will do it in script.However I just wonder why this happens on SSMS, is it a bug?
I don't have many backups on these server, only 4 small databases on the server about 1000 history backup files including full, differential and log.
I keep posting just after your posts so I am behind one post of yours all the time 🙂 I don't see your latest post until after I have already posted about the one before your last one.
SSMS has a few buggy things with it when trying to restore and it depends on the version of SSMS you are using. If you were trying to restore and there were no full backups, just the differential, it may have had problems trying to create the backup file list. It usually is problems related to creating that list of files when using SSMS.
I know it queries the tables in msdb to get that list. You could always run an extended events session or a trace to see more details of what it's doing. The number of backups in the history isn't that bad but it could just be the lack of a full backup.
Sue
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply