September 5, 2009 at 9:09 am
I was experiencing downtime on my website yesterday, but it's working again now. I'm sorry for the inconvenience.
The script was first published on SQL Server Central 23 Feb 2008.
Ola Hallengren
November 13, 2009 at 1:23 pm
Awesome. Two thumbs up for you, Ola. Finally someone post it. I have been using three scripts on agentjob I wrote to do these three things. Better late than never for those who are still missing the boat. I have not run yours yet personally. Anybody find a kink, please post for discussion.
Jason
http://dbace.us
😛
August 17, 2010 at 5:57 am
I've been using the backup script for some time and it works a treat. It is especially good for Sharepoint database servers where databases can suddenly appear as if by magic as the content grows. No need to work out names or set up jobs or keep checking for new databases, they just get included in the backup by default.
I've added a couple of extra parameters to the databasebackup proc. and added the code for them.
@Init is Y or N as to whether to backup WITH INIT or WITH NOINIT (for log files)
and @DAYFILES is Y or N for whether to use a dated file name or just the same file name each time - server space is the criteria here as backups are copied off to tape daily. I found the original dated files ate up disk space too quickly.
August 17, 2010 at 10:28 am
Some input on this. The advantage with backing up to new files with date and time in the file name and the way DatabaseBackup works, is that you're guaranteed to always have the latest full, the latest differential and all transaction log backups since the latest full or differential backup on disk.
Ola Hallengren
August 17, 2010 at 10:34 am
You might also want to try out the option @ChangeBackupType = 'Y'. Then the log backup job will see that there is a new database and perform a full backup instead for that database. The next time the log backup runs it can perform a log backup. This way new a database gets into the backup procedure very fast.
August 18, 2010 at 1:34 am
Dated files are fine when you want everything in seperate files, each log backup etc. If you want the log backups appended into one file or device with no init then it all goes wrong at midnight when the date changes!
You also have to ensure that there are cleanup tasks as dated files won't automatically overwrite each day. I don't have the disk space for much more than a day's backups on each server - we copy and tape backup to keep older copies so there is no justification for having the large quantities of disk space required for keeping more than 24 hours worth on line.
November 5, 2010 at 7:29 am
Great set of scripts! But...I am having one issue and I want to see if anyone else is having the same problem.
The problem I am having is with the IndexOptmize Procedure. For my FragmenationMedium_LOB and Medium_NonLob (between 10%-30%) I run the index reorganize. But for some reason my ldf file is growing out of control. My understanding of the reorganize feature is it doesn't have that kind of an impact on your log file. Before anyone hammers me, it is not the transaction log that is growing. Yes, after this runs I do have a large transaction log file, but the one right after that one is ok. It is just the ldf that is growing.
Database mdf file is around 25 gig
Database ldf file will start at 1.5 gig and grow to almost 40 gig in 4 or 5 days.
I am growing the file in 500MB inc.
RecoveryModel: Full
Also I am logging the output of this script and it has never reached a rebuild point over 30% its always a reorganize.
Here are my options set in the IndexOptimize Script.
@databases nvarchar(max),
@FragmentationHigh_LOB nvarchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB nvarchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB nvarchar(max) = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationMedium_NonLOB nvarchar(max) = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationLow_LOB nvarchar(max) = 'NOTHING',
@FragmentationLow_NonLOB nvarchar(max) = 'NOTHING',
@FragmentationLevel1 int = 10,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 1000,
@SortInTempdb nvarchar(max) = 'N',
@MaxDOP int = NULL,
@FillFactor int = NULL,
@LOBCompaction nvarchar(max) = 'N',
@StatisticsSample int = NULL,
@PartitionLevel nvarchar(max) = 'N',
@TimeLimit int = NULL,
@Execute nvarchar(max) = 'Y'
January 27, 2011 at 3:53 am
just tested this wonderful scripts and definately one of the must in job schedules.
One thing I would like to know that as soon the script completed it created the job. But in job the step comes as :-
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d ...................
and this will fail with incorrect servername and the only option I found is replace $(ESCAPE_SQUOTE(SRVR)) with real sql server name.
Am I taking this script incorrectly or do I need to modify it somewhere to reflect the correct servername while running the main script to create these jobs.
edit :- Sorry just checked that this blog is quiet old. But one thing cant understand that this script is on Ola blog from thu 20jan 2011, and the discussion seems to be started here on this website from 2/25/2008 12:21:14 AM.
----------
Ashish
March 15, 2011 at 2:09 pm
Can someone please let me know if this is a bug or if I am just dreaming...
I started looking at this process and running through it...and tried it out. Given the following scenario let me know if this is valid or not.
you have a DB in full recovery mode and are using this process to do your backups...
The parameters I had set were as follows...
SET@databases= 'DB_UTILS'
SET@Directory= 'E:\Backup'
SET@BackupType= 'LOG'
SET@verify= 'Y'
SET@checksum= 'Y'
SET@NumberOfFiles= 1
SET@CopyOnly = 'N'
SET@ChangeBackupType = 'N'
SET@checksum = 'N'
SET@NumberOfFiles = 1
SET@Execute = 'Y'
I set it to a situation where you have a full backup plan running on a DB...the DB is in full recovery mode and has a full backup already and you are just motoring along with log backups.
SOMEONE or SOMETHING switches the db to simple mode for what ever reason.
Now your log backup job comes along and fires off with basic inputs...listed above...and I didn't have the 'ChangeBackupType' enabled since on a VLDB it would be bad to fire a full during the day due to impact to the system...I instead would want to be notified to check something out and then make a decision.
Again, unless I am missing something there is a little fault in the logic...
IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE'
AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 0
AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL))
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabase = 'master')
This segment will eval that it is now a log backup but the recovery is simple and totally pass over the backup steps and then jumps to this block...without so much as a peep...
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID
looking at my directory no file is written and I am none the wiser that it didn't run.
I again might be taking things out the intended context but this kind of occurrence while rare does happen and I just wanted to comment and see if someone can verify this for me.
Lee
March 15, 2011 at 2:19 pm
I don't know if this answers your question or not but I run 3 different jobs.
The databases I want to be part of my transaction log shipping is set to Full. Everything else simple.
Basic Logic
Sunday(Midnight) - run full backups during a weekly maintenance.
--Every hour TLog.
Monday - Saturday (Midnight)- Diff Backups
--Every hour TLog.
1st job - Full Backups (Sunday Only)
2nd Job - Diff Backups (Mon-Fri)
3rd Job - TLog/shipping (At the top of the hour - after maintenance windows)
March 15, 2011 at 3:34 pm
mcliffordDBA (3/15/2011)
I don't know if this answers your question or not but I run 3 different jobs.The databases I want to be part of my transaction log shipping is set to Full. Everything else simple.
Basic Logic
Sunday(Midnight) - run full backups during a weekly maintenance.
--Every hour TLog.
Monday - Saturday (Midnight)- Diff Backups
--Every hour TLog.
1st job - Full Backups (Sunday Only)
2nd Job - Diff Backups (Mon-Fri)
3rd Job - TLog/shipping (At the top of the hour - after maintenance windows)
Hate the session expired deal 🙂 had a nice reply and it was lost.
Anyways here is a quick summary of what I wrote.
I had a test db in full recovery...ran a full backup with the stored procedure...then ran several log backups...
I switched the database to simple recovery mode to simulate some 'event' that would break the log chain...
then attempted to run the log backup again...it ran with no error and listed the following...
DateTime: 2011-03-15 16:09:53
Database: [DB_UTILS]
Status: ONLINE
Mirroring role: None
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
Differential base LSN: 137000000672800036
Last log backup LSN: NULL
DateTime: 2011-03-15 16:09:53
This segment of code gets skipped because it the DB is in SIMPLE mode
IF @ChangeBackupType = 'Y'
BEGIN
IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') <> 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabase <> 'master'
BEGIN
SET @CurrentBackupType = 'DIFF'
END
IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL AND @CurrentDatabase <> 'master'
BEGIN
SET @CurrentBackupType = 'FULL'
END
END
and also here because it is it is a LOG backup but the database is in SIMPLE mode
IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE'
AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 0
AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL))
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabase = 'master')
the whole backup process is skipped and you loop through.
No backup occurs...
again I am wondering if I am the one doing something wrong here.
I am running the debugger on a SQL 2008 R2 server for testing.
March 15, 2011 at 3:43 pm
Yes, setting your database to simple will break the chain. You can only take log backups when you database is in Full or Bulk Logged.
You will need to set the database(s) back to Full or Bull Logged, take your full backup and start the transaction Log backup process.
I would have to say the script is functioning as expected
March 16, 2011 at 7:58 am
Any luck with your testing?
March 16, 2011 at 8:05 am
mcliffordDBA (3/16/2011)
Any luck with your testing?
mcliffordDBA (3/15/2011)
Yes, setting your database to simple will break the chain. You can only take log backups when you database is in Full or Bulk Logged.You will need to set the database(s) back to Full or Bull Logged, take your full backup and start the transaction Log backup process.
I would have to say the script is functioning as expected
Hi there...I wasn't able to reply last night (no internet connection). I do understand the concepts of backups and log chain and that is what I was trying to point out in my question.
I know the normal process...full backup...diff / log. I wanted to continue testing to make sure what I think is happening is actually correct before I attempt to carry this one. Ola contacted me and I am attempting at the moment to create the scenario for him to have a discussion.
The point I was trying to make was if you had your db set up in full recovery mode...had a full backup...and then subsequent differentials and / or log backups. What happens when the log chain is broken by putting the database into SIMPLE recovery mode.
My event was supposed to highlight the change happens without your knowledge (i.e. you aren't the one doing it).
So you would think the database backups are continuing on the hour and at least in my intitial test of walking through the debugger it appeared for some reason it wasn't catching and it would totally skip over the database...which is something I wouldn't personally want.
I had taken some of the code out that related to the multiple backup software solutions that were coded in the script since I am not using any of them.
I re-deployed the original script from Ola and am about to work up a test case to see if I screwed something up.
I plan on sending it to Ola when I am done so he can see if it is correct or not.
Thanks for checking up 🙂
March 16, 2011 at 8:48 am
Interesting, Never thought about testing that. Thanks for the info.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply