November 15, 2022 at 11:14 am
Hi everyone,
I would appreciate your comments/suggestions/points on the following question. Please note I'm not a DBA, just a senior dev with some responsibilities on a Sql Server 2014 box.
Our database server is recording it's transactional backup logs from 6am to 8pm (every hour-on-the-hour), 6 days a week. The old logs are removed after 7 days. The big weekly full backup process runs every Sat at 6am.
The location of the transaction logs is on the server itself as is the transaction backup logs. The main backup is off the sever on an accessible fileshare (due to large filesize filling up the database server drive)
I have been asked to investigate the issue of saving the transactional logs off the server at the location of the main backup.
I'd like to take soundings on this and hence would greatly appreciate any comments/suggestions/best practices or just point that you feel I should be aware of.
Many thanks,
J
November 15, 2022 at 4:43 pm
Saving either the full backups or the log file backups to the same server as the database is one of the worst ideas for backups. There's no chance at restores if the server "burns in place". Of course, you know that already.
It's actually pretty easy to backup to a NAS (Network Attached Storage), which is simply another machine that's essentially a file server. Just give the SQL Server privs to write on the NAS (usually in the form of a "share") and change the backup commands to use the UNC for the file share.
The most difficult part is getting that to work with some good amount of performance. There are various methods to do such a thing but, since I've not been a "hardware" kind of person for more than a decade, I'll let someone more qualified than I to explain how to do that.
I'd also keep a rolling 2 weeks worth of backups and I'd likely do "conditional log backups" (do a backup only if there something new in the log) every 15 to 30 minutes all day every day. I'd also do nightly DIF backups to make restores to the end of the week a lot easier and faster.
Remember... what you really need is a "Restore Plan". Obviously, the methods you use for backups will affect that greatly.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2022 at 5:11 pm
1 week of backups isn't very long. Sometimes problems are discovered after a few weeks and it's helpful to restore the database to a different server temporarily to look at the data at that point in time. I usually keep a month.
Also, don't forget to actually test restoring with the backups you have.
November 15, 2022 at 5:16 pm
"...Our database server is recording it's transactional backup logs from 6am to 8pm (every hour-on-the-hour), 6 days a week. The old logs are removed after 7 days. The big weekly full backup process runs every Sat at 6am...."
I suggest a bit of overlap so your transaction logs go back further. If your most recent backup fails, then your oldest t-log gets deleted, you may have a gap in the log chain, and if you needed to recover, you could only restore to the week old full backup.
Might as well run t-log backups 7 days a week.
November 15, 2022 at 5:30 pm
"...The location of the transaction logs is on the server itself as is the transaction backup logs. The main backup is off the sever on an accessible fileshare (due to large filesize filling up the database server drive)..."
What method are you using to get the FULL backup to the fileshare? And is the fileshare backed up to "tape" so you have more than 1 copy of database backups ?
I don't know if what we used to do was good or not, but we backed up locally, then as part of the job, copied the backups to another server, so we had 2 copies. And both servers were also backed up as part of network backups. I think we did that to avoid any issues backing up across servers that might cause backups to fail.
November 15, 2022 at 6:02 pm
And don't forget to make "compression" the default... especially when using a NAS.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2022 at 7:09 am
And don't forget to make "compression" the default... especially when using a NAS.
That is SQLServer backup compression server level setting ( or at backup command level ) !!!
Do not compress windows volumes !
FWIW: we create backups to local storage and then xcopy them to safe zone file share ( next step in the sqlagent job )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 16, 2022 at 3:58 pm
Hi everyone,
Thank you all for your comments & practices you suggestions, many of which we are already doing. To answer you queries:
We are doing the weekly full back directly to a NAS usign a SQL server maintenance plan with the UNC path to said NAS hard-coded there.
It's working well and only takes about an hour. We do not have the room on the server iself to copy it over. (don't ask its political)
Yes, the nas itself is backed-up I am reliably informed.
Yes, we are compressing the backups at least using the "default server settings" and also encrypting...
We only remove the old t-log backup if the new t-log backup is successfully created; othrwise we get notified with suitable message.
I'm, not sure if we are doing "...a backup only if there something new in the log..." There are 3 backup types in the maintenance plan, which are full, differential and transactional. For the t-logs we are using type transactional. I will investigate what exactly is differential type as not completely familiar with it...
Rgds, J.
November 16, 2022 at 4:49 pm
So, what happens if someone needs a restore from the previous day 30 seconds after you drop the old backup?
It sounds to me like you need to keep both the current and the previous sets of backups.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2022 at 5:43 pm
...
We only remove the old t-log backup if the new t-log backup is successfully created; othrwise we get notified with suitable message.
#Fail !!!
You need to keep all transaction log backups at least until the new full backup has been produced.
We have a separate backup safezone for tansaction log backups ( timestamp in filename ) and copy all covering transaction log backup files with the new backup.
We have cover at least 6 months worth of full backup storage.
Log backup safe zone is being cleared ( folder / instance ) after xcopy to fullbackup safezone has successfully completed except for the last day worth of log backup files.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 16, 2022 at 8:18 pm
jellybean wrote:... We only remove the old t-log backup if the new t-log backup is successfully created; othrwise we get notified with suitable message.
#Fail !!!
You need to keep all transaction log backups at least until the new full backup has been produced.
We have a separate backup safezone for tansaction log backups ( timestamp in filename ) and copy all covering transaction log backup files with the new backup. We have cover at least 6 months worth of full backup storage.
Log backup safe zone is being cleared ( folder / instance ) after xcopy to fullbackup safezone has successfully completed except for the last day worth of log backup files.
Actually - you need to keep *all* transaction log backups from the known good backup up to the current point in time. If your current backup file becomes corrupted - and you have every log backup from the previous full to current you can still recover the system with no data loss.
If you don't have those available and have to resort to previous full backups you are guaranteed to lose data.
Since full backups are only performed once a week - then you really do need to implement differentials and transaction log backups. For the OP - differentials are not the same as file-based incremental backups and neither are transaction log backups. It is a common mistake to assume backing up SQL Server databases is the same as backing up the file system - but they are completely different and work differently.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 17, 2022 at 3:10 pm
Sincere thanks to all those who contributed. I have better understanding of the issues involved and the coverage required. Much appreciated, J.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply