Best Practice for full backup and transaction log truncation
-
I am new to SQL Server DB. Could someone please let me know what would be the best practice to do a full backup and truncate the transaction log.
If you could please let me know the strategy you are using in your company, that would give me some idea and help me learn from the documentation. I would also like to know if there is any white paper or article that explains with examples.
-
Best practice: Don't truncate your transaction log, except in unanticipated emergencies where you have run out of storage, and the cost of waiting to add storage exceeds to cost of losing transaction data.
Manage your storage & backup plan to support your recovery model and prevent unacceptable data loss.
What are your
- RPO (recovery point objective is the maximum acceptable amount of data loss measured in time)?
RTO (Recovery time objective is the timeframe within which applications and systems must be restored after an outage)?
What is your recovery model? Full? Simple? Bulk-logged?
What is the nature of your data? How often does it change?
- RPO (recovery point objective is the maximum acceptable amount of data loss measured in time)?
-
This was removed by the editor as SPAM
-
Thanks for your reply. We have a full recovery model and we are in RPO.
Let me explain more: we have Veeam which takes server backups. If the backups are being taken on the database side and they feel it takes additional work to take transaction log backups again in Veeam. So on a full recovery model, there is no possibility of truncating the log files? As you stated, I am asking my question unless there is an emergency situation.
I am trying to think if it is possible to make the full recovery model to simple recover during a maintenance window.
can we truncate the log files? so that we gain space on the OS
Once Truncation done, can we put back to full recover model and leave the transaction log to grow?
-
The only correct answer is "It Depends". I recommend that you lookup what the abbreviations RPO and RTO are for starters. Also, find and read up on the Full, Differential, and Transaction Log File backups and related restores.
Also understand that any given database is in a "pseudo-SIMPLE-Recovery-Model" until a FULL backup is taken and DO read up on Recovery Models in SQL Server.
As with many other endeavors, if you don't have a book on the subject, internet searches can help a lot.
https://duckduckgo.com/?q=recommendation+for+backups+in+sql+server&atb=v278-1&ia=web
--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) -
Post withdrawn because I don't actually know much specifically about VEEAM and didn't want to provide any misleading info.
--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) -
What I know is that we are taking the full database backup every evening and there are no differential backups being taken between each evening. In case the database needs to be recovered, we have to go back to the backup taken last evening.
The infrastructure team believes that if we took the backups to disk, including the transaction log, could we truncate the log? I assume we cannot truncate the log since we will lose the data once it happens after the full backup.
If the transaction logs are already backed up by database backups, they are considering whether they can avoid taking an image copy of the transaction log. Can you tell me how to answer them?
Also I have a question -? if the transaction log keep on growing for 3 or 4 years do we have any maintenance plan to truncate the transaction log at any time. If you are aware of such situation please let me know your views.
-
Taking full backups does not backup the logfile over time. If you're in the SIMPLE recovery model, then the log file will be self truncating whether you take full backups or not.
If you're in the BULK LOGGED or FULL Recovery Models and you take a FULL backup, that will start the logfile chain. You will need to start taking logfile backups to truncate the logfile.
If you're doing something that requires you to be in the full recovery model but you don't care about the log file chain, then do your full backups as normal and then do you log file backups to the NUL device to get your log files to truncate. This particular method is not recommended by most but ya gotta do what needs to be done. Personally, I'd find some place to backup the logfiles to but, again, ya gotta do what needs to be done sometimes, even if it bring tears to your eyes.
--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) -
Thanks for your kind reply, I got understanding about how it works now.
Do you have any information on how to create jobs for taking the full backup+ transaction backup and then truncating the transaction log
If you have any sample codes or the link to get such information which will be useful
- anbu678@gmail.com wrote:
we have Veeam which takes server backups.
If your databases are only being backed up to Veeam, I presume you are automating test restores from Veeam and running DBCC CHECKDB against them. ie A backup is useless unless it has been verified.
If you are also taking SQL Server backups, with differential backups, you also need to ensure the Veeam full backups are done with is_copy_only = 1 otherwise the differential base will be the Veeam full backup and not the SQL Server full backup.
-
Our Backups are getting stored on the SQL server G drive, additionally we have Veeam to take the backups.
Thanks for asking to check on is_copy_only=1 which I am not aware until now.
I would like to understand how to create auto job to take the full backup, transaction backup + truncating the log files(without making the database to simple recovery mode and truncating)
- anbu678@gmail.com wrote:
Thanks for your kind reply, I got understanding about how it works now.
Do you have any information on how to create jobs for taking the full backup+ transaction backup and then truncating the transaction log
If you have any sample codes or the link to get such information which will be useful
I'm not trying to be mean here. I have an honest concern for you and your data.
My feeling is that you're deeply in over your head on all of this and that puts your data at some serious risk. You need a professional/knowlegable DBA and I'm not talking about some casual help on a forum no matter how good someone may be. You need someone that will actually have access to your system(s) and to evaluate what you're doing and not doing.
--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) -
Changing the recovery model only for truncating the logs is not something I'd do.
You could configure a transaction log backup once a week or day, which automatically truncates the logs for you. It doesn't free up the space as you wanted. Instead, it will start reusing the inactive portion of the log file so it won't get gigantic enough to eat your disk. You could shrink the log file on a maintenance day.
Again, as Jeff specified, formulating the best strategy is possible only by knowing the details & resources of your database.
-
Why do you make backups with Veeam and with SQL Server? If Veeam B&R is configured correctly it truncats the logs for you. But I'd prefer SQL Server backups. With Veeam B&R taking your SQL Server backups you will always have data loss.
- anbu678@gmail.com wrote:
I would like to understand how to create auto job to take the full backup, transaction backup + truncating the log files(without making the database to simple recovery mode and truncating)
You can use SQL Maintenance Plans to create scheduled SQL Full, Diff, Trans Backups, and to delete old backup files older than 'X' days.
It's fairly easy using GUI, and you can find examples on-line.
As already mentioned, if your backups are set up correctly, you should not need to truncate transaction logs, except in special situations.
Is your VEEAM software doing SQL aware backups ? Can it do transaction log backups ? Whatever tool you use, you should practice restoring onto a test server to verify before an emergency happens.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply