Help finding Fullback up file upon which differential backup is based
-
New to SQL and SQL server and faced with making sure our company is properly backing up all our DBs.
Currently, under the maintenance plan folder, there is maintenance plan that has 3 subplans that I can see. Each plan has two tasks; a backup database task followed by a maintenance cleanup task.The Subplans
1. Differential backup of all user DBs. Scheduled every day, every hour 8 am to 6 pm. Has maintenance task to Cleanup DB files older than 7 days
2. Full back up of System DBs. Scheduled every day. Has maintenance task to cleanup DB files older than 2 days
3. Full back of all DBs. Scheduled weekly. Has maintenance task to cleanup DB files older than 8 days.When I look in the location of where the backups are saved, I am confused by the differential backups (subplan 1).
There is a backup file for each scheduled hour that gets bigger in size with each hour. Makes sense – differences compared to last full backup are increasing and thus writing more data. However, at the start of a new day the backup file has decreased in size, almost as if the differential is now using or referencing a different full backup (different Base file?).
Why is this backup file, which should be differential, decreasing in size at the start of a new day? If it is using a new base, is there anyway to find which full backup it is referencing? It doesn’t add up because the only other full backup of the user DBs are done once a week (subplan 3) as far as I can tell.If the differential backup decreased in size once a week RIGHT after the full back up of all DBs, this scenario would make sense in my mind – keeping in mind that I have very limited comprehension in these matters.
Ultimately, I am concerned that I wouldn’t be able to find the full backup file to use if needing to restore from the latest differential backup. I’m going to change the current subplans and save backups to multiple locations (local network device and rotating external hard drives) once I learn how, but before making any serious changes, I wanted to understand what was happening with these differential backups.
First time ever posting to a forum. Apologies in advance for the length. Also, if this is a scenario where I should be posting this to a different forum more appropriate for newbies, please be so kind to direct me there. I’m in over my head at the moment.
-
You'll need to talk to SQL Server, not just look at the files.
Basically, there is no way to give you any straight answers based on the information posted above - or in fact anything the file system could possible tell you.
The good news is that SQL Server always knows which backups have been made, where they've been placed and how they relate to each other, and it will flat out tell you if you just know how to ask.Let's start by getting a quick overview of what databases are currently on your server and list the current recovery model, status and last known good backups for each one.
SELECT name as 'Database',
recovery_model_desc as 'Recovery',
state_desc as 'Status',
d AS 'Last Full Backup' ,
i AS 'Last Differential Backup' ,
l AS 'Last Log Backup'
FROM ( SELECT db.name,
db.state_desc,
db.recovery_model_desc,
type ,
backup_finish_date
FROM master.sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name where db.name !='tempdb'
) AS Sourcetable
PIVOT
( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackupNote: If "Recovery" is listed as "FULL" or "BULK-LOGGED", you DON'T want to see "NULL" values in the "Last Log Backup" column.
If, on the other hand, you have Recovery = "SIMPLE", then "NULL" is the only possible value here.Log backups allow you to perform "point-in-time restores", meaning you'll be able to restore your database to a specific point in time regardless of when your backup jobs actually ran, so unless you are 100% comfortable with the idea of losing up to 1 hour's worth of production data, I'd say it's something to think about.
Next, to look at individual backups and the correlation between them, let's try this script:
SELECT a.database_name,
a.user_name ,
a.backup_start_date,
a.backup_finish_date,
a.type,
a.backup_size,
a.recovery_model,
b.physical_device_name,
a.first_lsn,
a.last_lsn,
a.database_backup_lsn,
a.differential_base_lsn
FROM msdb.dbo.backupset a JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.backup_start_date > DateAdd (day, -7, GETDATE ())
ORDER BY a.database_name, a.backup_start_date DESCHere, we get a list of all recorded backups made within the past 7 days (ref. the WHERE clause in line 2 from the bottom).
It's sorted DESCENDING on the backup_start_date column, so the most recent backup will be at the top. If you don't like this, simply replace "DESC" with "ASC" at the end to get a list that starts with the oldest backup.
Anyway, what you'll get here is one line for each recorded successful backup, starting with the database name, the name of the user performing the backup and the start and finish time of the backup operation. Next, you will get a single letter indicating the type of backup, where D is a Full database backup, I ("Incremental") is a differential backup and L indicates a log backup. Next, you'll see the backup size in bytes, followed by the database's recovery model at the time of this backup. Then comes the physical path to the actual backup file, followed by a series of LSN numbers which will actually tell you the relationship between the backups.LSN = Log Serial Number and refers to the transaction log for the specific database in question, which basically keeps track of every change that is ever made to your database and assigns a unique serial number to each event.
So, first_lsn and last_lsn columns indicate which log records are actually included in this backup.
The database_backup_lsn points back to the last "official" Full backup and differential_base_LSN is basically the same thing: For a differential backup, this field indicates which full backup is required to restore it (ony differential backups will actually have a differential_base_lsn value).
Also, if someone just finds a bak file lying around and nobody knows what the hoot is on it, you can learn a pretty insane awful lot about it by having SQL Server check the file headers for you and tell you what it sees.Here's the SQL for that too:
RESTORE HEADERONLY FROM DISK = 'D:\INSERT ACTUAL FILEPATH HERE' ;Hope this helps. 🙂
Kind regards,Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)It is better to light a candle than to curse the darkness. (Chinese proverb) -
Vegard,
I can't thank you enough for your help and the time you took to respond with such a thorough response. These scripts are amazing and will definitely be great tools for me to use going forward. Unfortunately, I am the lost puppy that won't stop following you. Do you or anyone else have any explanation/idea about this bizarre looking file path (Line 174, 185)?Differential backups (scheduled hourly) are there, with normal looking physical save path. Snapshot also captures the moment the weekly full backup is done (line 173) - again, with our desired save path. However, at the end of each day there is a full backup done that goes to a place that I don't understand. It seems like some sort of automated process - I have no idea how it has scheduled to run this daily full backup or how it is choosing the place to save it (whatever that code is indicating).
Lastly, is it possible for sequential, differential backups to get smaller between full backups? See line 182 and 181.
Thank you again for all of your help! Your information has been invaluable in getting me pointed in the right direction.
- Red14 - Thursday, August 16, 2018 9:42 AMVegard,
I can't thank you enough for your help and the time you took to respond with such a thorough response. These scripts are amazing and will definitely be great tools for me to use going forward. Unfortunately, I am the lost puppy that won't stop following you. Do you or anyone else have any explanation/idea about this bizarre looking file path (Line 174, 185)?Differential backups (scheduled hourly) are there, with normal looking physical save path. Snapshot also captures the moment the weekly full backup is done (line 173) - again, with our desired save path. However, at the end of each day there is a full backup done that goes to a place that I don't understand. It seems like some sort of automated process - I have no idea how it has scheduled to run this daily full backup or how it is choosing the place to save it (whatever that code is indicating).
Lastly, is it possible for sequential, differential backups to get smaller between full backups? See line 182 and 181.
Thank you again for all of your help! Your information has been invaluable in getting me pointed in the right direction.
That file is likely from a third part backup tool, such as Commvault. It can also be from an internal backup from something like SharePoint or SCCM. They perform their own backups.
I recommend taking a look at:
Ola Hallengrens maintenance solution https://ola.hallengren.com/
Or
MinionWare http://www.minionware.netBoth are far more flexible and reliable than the built in maintenance plans
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
It appears that you have another backup process running daily. This could be a SAN based backup using VSS as it is being recorded in the backup tables. This is why your diferentials are getting smaller each day as they have a new full backup upon which they are based, not the one you run weekly. This is going to cause you problems with restores if you don't know how the daily full backups are being accomplished.
-
Yes, it appears you do have some sort of third party tool making full backups you have not been aware of, so your number 1 priority should be to get this under control. Your differential backups will be useless if they're based on a full backup you're not able to restore, so if you're not in control of this "mystical backup" then the only time you actually have a good backup is the period between your weekly full backup and the first "mystical" full backup after that.
As for the size of the diff backups, yes, I believe we have our answer to that too, but since you're also asking if it's possible for the size of a diff to actually decrease between full backups: Yes, it is possible for one diff to be smaller than the last one if there is less database activity at run time. Each full or differential backup has to include transaction log records for any and all transactions that where actually "in progress" while the backup itself was running. So, if you have a lot of concurrent user activity or some old, heavy transactions running, the size of the differential backup can appear to be "inflated" by the sheer volume of log records that are attached to it. And thus, if there is less concurrent activity one hour later, then the next differential backup can appear to be smaller even if there's not been any full backup in the meantime.
Kind regards,Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)It is better to light a candle than to curse the darkness. (Chinese proverb) -
Thank you again for all of your help. The Ola Hallengrens maintenance and MinionWare look very useful. Once I research them a bit more and can feel confident that I won't break anything by using one of them, it will definitely be something I try to implement. User error is very real.
Indeed, you were all correct. It took me quite a while to find, but the 3rd party app doing the full backup was "idrive backup." Seems like a bit of silly plan to have a daily fullback up using a 3rd party app and then all the other backup plans going to a local computer. Doing a restore using a fullback up from the "idrive" app and then a differential from a local computer sounds like a bad recipe, or at least a restore that wouldn't be as easy to do if both fullback up and differential backup were from the same process and in the same general location.
The plan I am contemplating is as follows
Daily fullback up off all DBs - Clean up older than 7 days - this maps to a different computer on the network - Computer A
Hourly (7 am to 6 pm) differential backup of all DBs - clean up older than 7 days - this maps to Computer A as wellDaily fullback up off all DBs - Clean up older than 2 days - this maps to the local server/computer running the DBs
Hourly (7 am to 6 pm) differential backup of all DBs - this maps to the local server/computer running the DBs
Idea behind this is to have a quicker restore available that doesn't have to go across the network in addition to having some redundancy. (This idea could be off base, so correct me as needed)Essentially the two plans are the same except for the clean up schedule and location. Should I be able to create such a plan using Ola Hallengrens or MinionWare? Sounds like the built in maintenance plans would limit me.
Two other things not being done currently that seem like a good idea to implement - compression of the backups as well as backup of logs. Any disadvantage or reason to not do these two things? I didn't find any huge red flags from my research.
Finally, from "Computer A" I plan to run a backup service (backup 4 all) that simply takes a daily copy of the backup DB files and saves to a rotating external hard drive.
How good or ridiculous does this backup plan sound?
I suppose the answer depends on many different variables that one might need to know about the DBs themselves. I'm not sure which variables to describe, but I can say that no one is really going into the DBs and managing them in anyway. We are a small to medium sized company - only changes that come about to the DBs are done on the front end where users are in the software. In my mind, there aren't very many DBs and they aren't enormous.I seriously can't thank all of you enough. I feel bad asking more questions - especially ones that are probably less than the 101 level of DBA work. Your help has been invaluable and believe it or not, I am doing my best to research this and do it on my own and not just use you as crutch to do my work for me. Very fascinating stuff that I'm eager to learn more about.
- Red14 - Friday, August 17, 2018 11:04 AM
NO. The differential backups are not needed in this plan. You need to start doing LOG backups.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John - Friday, August 17, 2018 11:12 AM
I would agree, but we don't know what recovery model is being used on the database(s).
-
Looks like a mix -
System DBs
master - SIMPLE
model - FULL
msdb - SIMPLEUser DBs - 10 out of 12 are SIMPLE. 2 are FULL
Seems I will need to change to full recovery model if I am to rely on log backups in place of the differentials?
-
Yup. Log backups are not supported in SIMPLE recovery mode because that involves simply overwriting the log entries as soon as you’re done handling transactions and writing the data back to disk. FULL recovery involves NEVER overwriting ANY log records unless they’ve been picked up and preserved by a log backup first. You’ll need a bit more disk space for your log files and the log backups might actually get bigger than your diffs, but the real strength of a log backup is that you can restore it up to ANY point in time within the time period it covers, whereas a differential backup can only be restored up to the exact moment the backup itself was made.
Kind regards,Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)It is better to light a candle than to curse the darkness. (Chinese proverb) -
The real decision needs to be made by the business. They are the ones that make the decision regarding how much data they are willing to lose in the event of a failure (hardware, software, environmental, etc.) and how long it takes to recover. This is the RPO and RTO times that you frequently hear about.
- Lynn Pettis - Friday, August 17, 2018 3:16 PM
Heh... I always tell them they have no choice in the matter because they don't really have a clue... especially if they've already allowed for backups using only FULL and DIFF. And, unless you have it in some seriously written and verifiable paperwork somewhere, if you DON'T have it backed up and restore-able to some really good numbers, it'll be your butt in the sling when it happens and they try to suddenly change the RPO and RTO at that minute.
Protect the company because they may not know what they're doing and protect yourself because you do. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
+1 to Jeff's comments. The only time I'd use Simple recovery mode is if the database is entirely disposable (if f.e. it's just used for post-processing data collected elsewhere in a repeatable fashion) because it can then be a bit quicker and save some storage. Otherwise you really want the Full recovery model with regular log backups.
I'd also advise against trying to run multiple schedules of backups on the database. Remember that transaction log backups and differentials will be based on the last full backup taken (excluding Copy-Only backups) and so running multiple schedules just ends up with them trampling all over each other. Instead have one process that backs up the database and logs on suitable schedules (not to the same physical disk as your database), then take regular copies of those backup files to your alternate location instead. This keeps a single backup chain which is a lot easier to manage and understand when you're trying to restore (and you don't want to be having to think hard when you're trying to rescue a dead system)
At the very minimum though, even if you stick with maintenance plans, do yourself another favour and make sure all the different backup types use sensible file extensions. I use .bak for full backups, .dbk for differentials and .trn for transaction logs. It makes life a lot easier when you're trying to see at-a-glance which backup file is which.
- Jeff Moden - Friday, August 17, 2018 6:00 PM
I got into an argument with the network services people at a previous employer because they got a new new tape library system and were going to remove tapes once a week instead of daily. Kept telling them that wouldn't work. They were telling me I had to go tell the CFO that if there was a fire in the server room that they could lose up to a full week of data instead of just one day.
That turned out to be what I needed to get a file server to store backups off site which actually reduced to the exposure of data loss as files were transferred as soon as the backups completed.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply