January 17, 2012 at 9:12 am
Is there any problem with using the same transaction log backup file continuously throughout the week? We’re planning to do weekly full backups and transaction log backups every four hours (we had daily differentials but Backup-Exec (3rd party software) fails when the transaction log backups are done outside of its agent). The transaction log backup file name does not include any datetime info – it’s just ‘DBNameLog.bak’. The file date changes and size increases every time the log backup is run. It seems simpler to do this rather than have a different file created each time the log backup is run with the datetime stamp in the file name. This way there would only be one log backup to restore in the event of failure. Thanks in advance.
January 17, 2012 at 9:16 am
ragresti-1141020 (1/17/2012)
This way there would only be one log backup to restore in the event of failure.
No, there won't be just one log backup to restore. You'll have just as many to restore as when they are in separate files. The only difference is the syntax of the restores:
RESTORE LOG <database name> FROM disk = <location> FILE = 1
RESTORE LOG <database name> FROM disk = <location> FILE = 2
RESTORE LOG <database name> FROM disk = <location> FILE = 3
...
instead of
RESTORE LOG <database name> FROM disk = <location 1>
RESTORE LOG <database name> FROM disk = <location 2>
RESTORE LOG <database name> FROM disk = <location 3>
Personally I prefer each backup into its own file. That way I can clearly see what backups I have without needing to run a RESTORE HEADERONLY on the backup file to get the backup sets within.
Also, if there's any damage to the header of the file, you've lost every single backup within it, rather than just one backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2012 at 9:23 am
GilaMonster (1/17/2012)
ragresti-1141020 (1/17/2012)
This way there would only be one log backup to restore in the event of failure.No, there won't be just one log backup to restore. You'll have just as many to restore as when they are in separate files. The only difference is the syntax of the restores:
RESTORE LOG <database name> FROM disk = <location> FILE = 1
RESTORE LOG <database name> FROM disk = <location> FILE = 2
RESTORE LOG <database name> FROM disk = <location> FILE = 3
...
instead of
RESTORE LOG <database name> FROM disk = <location 1>
RESTORE LOG <database name> FROM disk = <location 2>
RESTORE LOG <database name> FROM disk = <location 3>
Personally I prefer each backup into its own file. That way I can clearly see what backups I have without needing to run a RESTORE HEADERONLY on the backup file to get the backup sets within.
Also, if there's any damage to the header of the file, you've lost every single backup within it, rather than just one backup.
+1
this is by far the more common way of storing log backups. Much better for offsiting of the backups as well.
---------------------------------------------------------------------
January 17, 2012 at 10:33 am
The BIGGEST risk you are going to run into is invalidating that single file with any sort of glitch that may occur during the backup...
At the company I work for we run a mixed bag now of backup solutions...some third party, some single file (legacy) and most recently a custom multi-file backup...the legacy stuff runs to a single file backup (daily full with appended log files).
In the instance of a single file backup, if a network glitch for example) occurs, the header on the backup file can become and has become corrupted...which invalidates the entire backup file (screws you in the event of a disaster).
In the new world (for us), I took Ola Hallengren's (http://ola.hallengren.com/) solution for a multi-file backup, modified it to our needs and am running a totally custom backup process that outputs single file backups in the following format...name / date / time / type / file count / compressed / extension...this way they line up nice in the folder...and you can differentiate very easy.
msdb_01.02.2012_23.15.00_DIFF_01_compressed.diff
With it we run a weekly full, daily diffs, and hourly logs...
All which is extensively logged to tables for reporting and has policy management by landscape for on disc retention (i.e. production is 3 cycles)...
Sometime ago, Gail gave me the above example which opened my eyes and made me truly understand what the implications of running such a method were (single file all in one)...
That is when we started down the path to find a solution for a multi-file backup process and although Ola's process wasn't 100% what we needed the logic was perfect and we worked through it for the additional things that at the time were not available...(Extensive Logging, History, Reporting, Policy management).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply