sql serversql server 2014ssms
I am using SQL server 2014 to restore backups using the GUI, however, the back sets to restore are from old backups that are no longer available along with transaction logs that span months ago.
How can I repair the wizard so that it populates the most recent full and diffs and transaction logs like the way it used to work by default?
Here is a sample screenshot of the of the restore database screen I am making reference to
As with all things in SQL Server, it depends.
First thing you need to do is make sure you understand what each type of backup does.
Books Online has all the gooey details, but here's my summary.
A FULL backup contains everything within the database. A DIFFERENTIAL backup is cumlative NOT incremental. In your example, if your database failed on the 12th, then you'd only need to restore the full backup from the 1st and then the most recent differential on the 12th, then followed by all the transaction log backups upto the failure. A TRANSACTION LOG backup is only needed for databases using the full or bulk-logged recovery model. If you're using the simple recovery model then transaction log backups are not needed.
Now that we've cleared that up...Designing a backup schedule really depends on how much data you need to recovery and how fast you need to recover it in the event of a diaster. I would recommend starting with a full backup each day. You can always reduce the frequency later. Remember the differential backup is cumlative since the last full, so depending on the amount change going on in your database the differential could be larger than the full backup after a few days. If you do a full backup each day, then you may not need to use differentials at all; however you could still do it once a day and schedule it at 12 noon. The transaction log backup only backs up the log. The frequency of the log backup will determine how much data you're willing to lose in the event of a failure. If you run your log backup every 15 minutes, then you would expect to lose up to the last 15 minutes of data that changed. 15 minutes is a good frequency, but every 30 minutes works perfectly for my environment.
As I said earlier, it all depends on your environment. After you've designed and setup your backup schedule, remember to test it on an alternate server. Practice restoring your full, diff, and log backups so that you know everything works like you designed it.
Books Online has some good info if you plan use Maintenance Plans, but if you really want flexibility then check out Ola Hallengren's backup scripts.
I would try scripting out the operation instead of running whatever Management Studio is doing "for" you. You shouldn't need to use xp_fileexist (assuming you can validate that the files actually exist in the place the script says). Using 2012 SSMS, I am restoring to a point in time that does not coincide directly with a log backup:
The resulting script in my case was:
BACKUP LOG ... WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY
RESTORE DATABASE ... WITH FILE = 1, NORECOVERY, NOUNLOAD
RESTORE LOG ... WITH FILE = 1, NORECOVERY, NOUNLOAD
...
RESTORE LOG ... FROM DISK = [...path from line 1...] WITH NOUNLOAD, STOPAT = <time>
Best Answer
The Wizard is really just a listing of backup history records in MSDB on that local server. This wizard is not necessary to do backups. In fact, you should run a process to purge backup history on a regular basis. When I do a SQL Server Health Check for a client, I consider it a finding (relatively minor but still a finding) when this history is purged.
I am wondering if you are attempting to restore to a different server which also has that database on it?
Instead, choose Device and browse to your backup file. You'll see the right backupset to restore to inside of that backup file.
The dialog here is sort of a helper if you are restoring on the same server where your SQL Agent backups are taken but if you take backups in different ways, if you clean up history or if you are restoring to a different server, that dialog is likely not so helpful.