Team,
Need help please.
My full backup is at 1225am and differential backups are at 12noon. Transaction logs every 30 mins.
Database corrupts at morning 8am today.
We restored full backup that was early morning and last TRN at 730am however we get an error
Restore failed for Server '
System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 123716000000011200001, which is too recent to apply to the database. An earlier log backup that includes LSN 123545000000034900001 can be restored. (Microsoft.SqlServer.Smo)
Can you please tell us if there is an issue with the backup scripts
Hi,
For restoring, you will need to restore the last Full backup (at 12.25am), then all TLog backup from that point onwards, meaning 12.30am, 1am, 1.20am and so on until you get to 7.30am. I assume you are running at FULL recovery model, if you are sure that the corrupts is at 8am, you can even restore the 8am TLOG and tell it to stop at 7.59am, so that you only lose 1 min of data.
Thanks Jason & Dennisc.
I restored to 1225am backup and TLOG as i got one file at the .TRN file.
Received below error..
The log or differential backup cannot be restored because no files are ready to rollforward.
My Backup statements are -
Full - runs at 1225am every day
BACKUP DATABASE XYZ TO DISK = 'FULL_XYZ.BAK' WITH FORMAT;'
Diff - runs at 1225noon every day
BACKUP DATABASE XYZ TO DISK = 'DIFF_XYZ.BAK' WITH DIFFERENTIAL, INIT;';'
TRN - runs every 30 mins everyday
BACKUP DATABASE XYZ TO DISK = 'TRN_XYZ.BAK' WITH INIT, FORMAT, SKIP, NOREWIND, NOUNLOAD, STATS = 10;'
Assuming that is an unchanging filename? (i.e. it does include Date / Time as well, which would make the filename unique for each TLog backup) then, sadly, you are overwriting the backup file each time - and INIT will tell SQL to initialise the backup file so, in effect, all previous backups will have been lost.
If anything else will have copied that file somewhere else (some sort of incremental backup, e.g. to the cloud) and you can, separately, recover each file THEN you could restore them sequentially
In future you could have SQL use APPEND to that file, but personally I think that's a bad idea - something may cause the APPEND to corrupt the file - leaving you with nothing, rather than "all earlier TLog backups".
We uniquely name all backup files, including the Date/Time in the filename.
Important to test the backups by making a restore periodically - to prove that you can.
Unless you have all individual TLog backups, since Midnight, then your only restore option is the last FULL backup - or to repair the database (provided that either a) you have not started a Restore over the top of it or B) you took a Full Backup before you started any restore command attempts.
Thank you for your time.
I removed the INIT command in my Log file backups and I found approx. 2 files when i choose to restore from the TRN Log file. I choose both files after my FULL backup restore was done.
Got the below error..
The log or differential backup cannot be restored because no files are ready to rollforward.
Choose only the second file from the .TRN file and it worked.
But in this case, is the diff backup still needed?
And does the TRN LOG files resets after full backups every day?
Thanks much.
You can actually restore the 8am file without using STOPAT as the transaction log backups do not contain the database corruption. The corruption would be in full and diff backups but not in log backups.
A differential can be used to skip having to restore a lot of log backups. There is no requirement to use them though. If you are restoring to a time before the diff ran, you would not restore it at all.
Diffs are handy if you are backing up the logs frequently such as every minute.
You can check the date/time that the Backup file was made using RESTORE HEADERONLY
RESTORE HEADERONLY FROM DISK = 'x:\Path\TRN_XYZ.BAK'
The date/time is in [BackupFinishDate] and the LSN numbers each backup contains. When restoring Log backups the next file to be restored should have a [FirstLSN] that matches the [LastLSN] of the previous file that was restored.
You have to restore the individual TLog backups in chronological order, and with no gaps between LSN numbers.
Many Thanks guys for your time.
I deleted the DIFFERENTIAL BACKUP JOB as not suited per my requirement.
My plan now looks like -
FULL BACKUP at 1225am every day.
LOG BACKUP starting from at 1245am every day intervals 30 mins.
Hope the above is fine from disaster management point of view.
I understand the LOG files will be approx 48 files every day and it reset after full backup. Is that correct?
I have removed the INIT command from the backup log statement to get multiple files(with LSN) when I do a restore.
RESET means - deletion of log files automatically when full backup is performed.
Please correct my understanding if wrong. Many thanks.
If you removed INIT from your statement, then it's going to append the backups into one file. That's not the recommendation as it makes it harder to restore. Instead use WITH INIT and specify a unique name.
We keep ours for "a long time" - at least a month. The last few days are on the server (quicker to restore if I need to), older files are on another server, on a remote site (for redundancy). Separately from that all the files, for the last month, are on our backups - used to be Tapes but now we backup to the Cloud.
Deleting TLog backups when you make the next successful Full backup is a really bad idea. What if the Full Backup is making a backup of a corrupted database? With a complete history of TLog backups you could restore an earlier ("Known-Good") Full backup and ALL the TLog backups since
Thanks Tara for the maintenance solutions. Read about ola.hallengren scripts they work good. However, are minionware scripts tried and tested to be executed tension free at prod servers.
I agree, I think that Ola Hallengren scripts cover all the complex options of backups, but are somewhat complex to set up, whereas Minion is more straight forward to set up and once installed "just works safely" (and then possible to fiddle with the Options if necessary) so I think Minion is a better choice unless the complex options are needed.
Ola provides a sql file that set everything up for you. You just have to add a job schedule and change your backup location. Then anything else you want to change from his defaults. Very simple to setup.
Hopefully this point would be worth discussing, in that vein!! :
I haven't installed Ola's scripts, so I'm speaking out of turn. But the reason I haven't done it is that at-a-glance I could not see what I would have to do, what would be up to me to CONFIG and what would just happen "out of the box". i.e. it appeared to me to be a "steep learning curve".
I'm basing that on having a quick look at the script and the DOC pages
The script has this:
SET @CreateJobs = 'Y' -- Specify whether jobs should be created.
SET @BackupDirectory = N'C:\Backup' -- Specify the backup root directory.
SET @CleanupTime = NULL -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
SET @OutputFileDirectory = NULL -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable = 'Y' -- Log commands to a table.
This is right at the top of a monster script. There is no comment that says "Just change these values, no need to examine anything else". So I'm not confident as to what it will do. I'm also not thrilled that all this stuff will be put in MASTER (by default, but if I change that [its not a parameter, but rather just a USE statement at the top of the script] might I need to change anything else? No indication in the code / DOCs - hence my view that this is better suited as a DBA solution rather than a naive user solution)
Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, and ALL_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).
Value Description
SYSTEM_DATABASES All system databases (master, msdb, and model)
USER_DATABASES All user databases
ALL_DATABASES All databases
Db1 The database Db1
Db1, Db2 The databases Db1 and Db2
USER_DATABASES, -Db1 All user databases, except Db1
%Db% All databases that have “Db” in the name
%Db%, -Db1 All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db% All databases that do not have “Db” in the name
Good, and very useful, parameters for which databases to backup (or not), but it seems to me that I have to do something [proactively]. Perhaps I have to schedule an SProc Exec? I had a look at the Sprocs and there is one called [DatabaseBackup] - fair enough. But it takes 34 parameters (which look like they cover everything) but I think that is seriously daunting for a naive user ... for a DBA I can well imagine that it is a godsend.
I couldn't find much in the way of worked examples and START HERE info.
starts with a tutorial video and well presented DOCs (PDF). There is a similar [to Ola] script to run, and it too has a very similar "Database backup location" parameter. There are very good comments in the script that explain what needs to be changed (although I think it would be easier if they used some very simple @Variables at the top of the script ... rather than embedding them in a SELECT ... INTO ... statement)
SELECT
'MinionDefault' AS DBName, --Default row. DO NOT CHANGE!
0 AS IsMirror, --Default row. DO NOT CHANGE!
'All' AS BackupType, --Default row. DO NOT CHANGE!
'Local' AS BackupLocType, --BackupLocType. This is the location type. Local, NAS, Remote, URL. This setting is used for your benefit, so put whatever will remind you what type it is. It only matters if it's 'URL', but that feature isn't live yet so you shouldn't worry about this.
'C:\\' AS BackupDrive, --BackupDrive. Drive letter or base UNC path like '\\MyNAS\'.
'SQLBackups\\' AS BackupPath, -- BackupPath. The folder structure you want under the base path. So in this case the backups will go to C:\SQLBackups.
NULL AS ServerLabel, --ServerLabel. Unless you have a reason to change this, you can leave it NULL. The vid discusses this setting.
NULL AS BackupDate, --BackupDate. Leave this NULL. I don't think I'll use it in the long run.
24 AS RetHrs, -- How long do you want to keep the backup files on disk?
0 AS PathOrder, --PathOrder. Unless you want to specify which drive in a multi drive scenario gets written to first, just leave this as is.
1 AS IsActive, --Default row. DO NOT CHANGE!
NULL AS AzureCredential, --AzureCredential. This feature isn't currently active.
'Minion default. DO NOT REMOVE OR CHANGE.' AS Comment --Default row. DO NOT CHANGE!
INTO #BackupSettingsPathInstaller
(I've had to put some extra "\" in there because otherwise the forum screwed up the formatting)
So the script config is much-of-a-muchness - choose Path for the backup files, and how long you want to retain them (although Ola default is "forever" which is a problem-in-waiting)
One important difference is that Minion creates a "Maintenance Config Database" - so any CONFIG data is rows-in-a-table, rather than parameters-to-a-scheduled-SProc. It also means that Minion's code is in that database, rather than MASTER.
With Minion after running the script you get this - no further action is required of the user, unless they want to change something:
Full system backups are scheduled daily at 10:00pm.
Full user backups are scheduled on Saturdays at 11:00pm.
Differential backups for user databases are scheduled daily except Saturdays (weekdays and on Sunday) at 11:00pm.
Log backups for user databases run daily as often as the MinionBackup-AUTO job runs (every 30 minutes).
which is the part that IMHO I think better suits a naive user, and differentiates Ola's script in a way that I describe as "complex".
But, as I said, I'm not very familiar with Ola's scripts, and I would appreciate being corrected if my assumptions are wrong.
All you have to do is run his MaintenanceSolution.sql script, and it'll setup everything for you. Very, very little that you'll have to change in the jobs.
We've had no issues with our clients that have very little experience with SQL Server setting up and configuring the Ola stuff. We give them recommended changes, and that's it. And it's as simple as modifying the backup jobs for the backup path and retention and index optimize job for the thresholds.