October 19, 2017 at 10:36 am
Each day I create a FULL backup of a database using:
BACKUP DATABASE [utility] TO DISK = 'U:\Database Backups Staging\Temp Backups\utility_10_19_17.bak' WITH COMPRESSION, INIT;
then, periodically, I create DIFFs using:
BACKUP DATABASE [utility] TO DISK = 'U:\Database Backups Staging\Temp Backups\utility_10_19_17.bak' WITH COMPRESSION, DIFFERENTIAL, NOINIT;
When I go in to SSMS to do the restore I only see the FULL and the latest DIFF. For example, if I ran the DIFF line above 3 times I would only see file 1 (FULL) and file 4 (the latest DIFF) but no option to restore to the other DIFFs in between (files 2 and 3). Isn't' that what the NOINIT clause is for in the DIFF statement?
Thanks.
October 19, 2017 at 11:01 am
patreddy - Thursday, October 19, 2017 10:36 AMEach day I create a FULL backup of a database using:
BACKUP DATABASE [utility] TO DISK = 'U:\Database Backups Staging\Temp Backups\utility_10_19_17.bak' WITH COMPRESSION, INIT;then, periodically, I create DIFFs using:
BACKUP DATABASE [utility] TO DISK = 'U:\Database Backups Staging\Temp Backups\utility_10_19_17.bak' WITH COMPRESSION, DIFFERENTIAL, NOINIT;When I go in to SSMS to do the restore I only see the FULL and the latest DIFF. For example, if I ran the DIFF line above 3 times I would only see file 1 (FULL) and file 4 (the latest DIFF) but no option to restore to the other DIFFs in between (files 2 and 3). Isn't' that what the NOINIT clause is for in the DIFF statement?
Thanks.
You are appending your backups (full and diff) into one file. First, not a good practice, but I will leave that for another time.
What exactly are you trying to accomplish? If you are restoring your database to the latest backup, then all you need is the Full Backup (file 1) , and the most recent diff backup (in this case file 4).
October 19, 2017 at 11:12 am
We load a lot of files to a very large, read-heavy, database. We create a FULL backup at the time the first file of the day is loaded and then a DIFF before each additional file. It is not unusual for us to need to roll back to just before a particular file is loaded, hence the DIFFs at each load. So...if we run in to a problem with data in the 4th file loaded in a given day then we'd want to revet back tothe FULL and the 2nd DIFF. It makes sense - and I thought a single backup set would be capable of holding a FULL plus multiple DIFFs - is that not the case?
October 19, 2017 at 11:23 am
patreddy - Thursday, October 19, 2017 11:12 AMWe load a lot of files to a very large, read-heavy, database. We create a FULL backup at the time the first file of the day is loaded and then a DIFF before each additional file. It is not unusual for us to need to roll back to just before a particular file is loaded, hence the DIFFs at each load. So...if we run in to a problem with data in the 4th file loaded in a given day then we'd want to revet back tothe FULL and the 2nd DIFF. It makes sense - and I thought a single backup set would be capable of holding a FULL plus multiple DIFFs - is that not the case?
It does. You just have to specify which files you are going to use. From your description file 1 will always be the full backup. You restore that first with NORECOVERY. You the restore the differential backup you want by specifying which file that may be (file 2, file 3, whatever).
I just would make each backup (full and differential) to their own files.
October 19, 2017 at 11:30 am
patreddy - Thursday, October 19, 2017 10:36 AMEach day I create a FULL backup of a database using:
BACKUP DATABASE [utility] TO DISK = 'U:\Database Backups Staging\Temp Backups\utility_10_19_17.bak' WITH COMPRESSION, INIT;then, periodically, I create DIFFs using:
BACKUP DATABASE [utility] TO DISK = 'U:\Database Backups Staging\Temp Backups\utility_10_19_17.bak' WITH COMPRESSION, DIFFERENTIAL, NOINIT;When I go in to SSMS to do the restore I only see the FULL and the latest DIFF. For example, if I ran the DIFF line above 3 times I would only see file 1 (FULL) and file 4 (the latest DIFF) but no option to restore to the other DIFFs in between (files 2 and 3). Isn't' that what the NOINIT clause is for in the DIFF statement?
Thanks.
In SSMS, the restore defaults to the last backups. You need to click on the timeline and change the restore times to move the restore to a different differential. Based upon the time you select, it will change the differential (in the Position column). It will also interpret that as a restore to a point in time and you will need the required logs. There may be other ways - I don't use SSMS for backups or restores.
It's easier to manage and control if you restore using t-sql statements. You just use WITH FILE = n to specify which file (what's seen as position in SSMS) to speciy which file to restore from a backup file with multiple backups.
Sue
October 19, 2017 at 11:48 am
Thank you for the replies. I think SSMS used to show a bunch of DIFFs with only the last one checked, but that may have been long ago. Choosing the DIFF I need in mt T-Sql statement worked like a charm. I'll consider creating different files, too, but I fail to see the advantage.
P
October 19, 2017 at 11:51 am
patreddy - Thursday, October 19, 2017 11:48 AMThank you for the replies. I think SSMS used to show a bunch of DIFFs with only the last one checked, but that may have been long ago. Choosing the DIFF I need in mt T-Sql statement worked like a charm. I'll consider creating different files, too, but I fail to see the advantage.P
If, for any reason, your disk file with the full backup and multiple differential backup files goes corrupt, you lose all the files.
October 19, 2017 at 11:54 am
Yeah, I can see that as an argument for multiple files - but also...the more files the better the chance one becomes corrupted. Considering we do a daily full and diffs in between we'e ok losing a file if it just means we need to roll back an entire day. But...I'll give your suggestion more thought. Might be the way to go. Thanks!
October 19, 2017 at 12:02 pm
patreddy - Thursday, October 19, 2017 11:48 AMThank you for the replies. I think SSMS used to show a bunch of DIFFs with only the last one checked, but that may have been long ago. Choosing the DIFF I need in mt T-Sql statement worked like a charm. I'll consider creating different files, too, but I fail to see the advantage.P
Out of curiosity, I just pulled up an older version of SSMS and it does list the differentials separately when they are in a single file.
Part of my reason for separate files is that if something by odd chance happens to one file, I'd rather it affect one backup than numerous backups. And for me, it just seems easier to manage for some things where I would want one backup instead of several.
Sue
October 19, 2017 at 12:12 pm
Sue_H - Thursday, October 19, 2017 12:02 PMpatreddy - Thursday, October 19, 2017 11:48 AMThank you for the replies. I think SSMS used to show a bunch of DIFFs with only the last one checked, but that may have been long ago. Choosing the DIFF I need in mt T-Sql statement worked like a charm. I'll consider creating different files, too, but I fail to see the advantage.P
Out of curiosity, I just pulled up an older version of SSMS and it does list the differentials separately when they are in a single file.
Part of my reason for separate files is that if something by odd chance happens to one file, I'd rather it affect one backup than numerous backups. And for me, it just seems easier to manage for some things where I would want one backup instead of several.Sue
I think they added the "timeline" slider in SSMS 2014 or thereabouts.
As for the separate files, another way to look at it is, if you have one big backup file, and one lonely disk sector goes bad under that file, you've lost every backup in that file. If you have multiple files and the same sector goes bad, you only lose one of the files (and as long as it's not the full,) but you can still restore if need be.
October 20, 2017 at 5:29 pm
patreddy - Thursday, October 19, 2017 11:54 AMYeah, I can see that as an argument for multiple files - but also...the more files the better the chance one becomes corrupted. Considering we do a daily full and diffs in between we'e ok losing a file if it just means we need to roll back an entire day. But...I'll give your suggestion more thought. Might be the way to go. Thanks!
Just imagine what happens when the 1 file gets corrupted.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply