February 24, 2008 at 5:21 pm
Comments posted to this topic are about the item SQL Server 2005 - Backup, Integrity Check and Index Optimization
April 21, 2008 at 3:54 pm
I was wondering if you would tell me why a function was written to execute a sql command instead of calling the exec sql command directly.
April 22, 2008 at 11:11 am
The background for the design decision to execute all commands through a CommandExecute stored procedure is that I would like to have a consistent error handling and logging. Information about all commands are logged with start time, command text, command output and end time. Here's an example of how a command is logged.
DateTime: 2008-03-05 21:58:59
Command: ALTER INDEX [IX_Address_StateProvinceID] ON [AdventureWorks].[Person].[Address] REORGANIZE
Comment: IndexType: 2, LOB: 0, PageCount: 1001, Fragmentation: 7.40741
DateTime: 2008-03-05 21:59:10
Ola Hallengren
June 6, 2008 at 11:33 am
I have started testing and implementing this process, and so far, I like it! The issue I currently am having is "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE". I know the issue is you cannot do log backups on DB's that are in SIMPLE recovery model, what is the best way to handle this?
-- Cory
June 6, 2008 at 12:47 pm
Thanks, Cory. You need to change one line of code in [DatabaseBackup] to have it dynamically skip databases that are in Simple recovery model, when doing log backups.
http://ola.hallengren.com/sql-server-backup.html
IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE' AND NOT (@BackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabase,'recovery') = 'SIMPLE')
This is also how it will work in the next version.
Another possibility is to exclude these databases by name.
@databases = 'USER_DATABASES,-Db1,-Db2'
Ola Hallengren
June 6, 2008 at 12:53 pm
Thanks for getting back to timely with the solution -
Since we are talking about next version things, I had to make one other modification to not backup snapshots:
in the DATABASESELECT, I had to add to the "insert into @database02..." the following:
INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT [name], 1
FROM sys.databases
WHERE database_id > 4
AND source_database_id IS NULL
I had thought about doing the "-DB" part, but I wanted this to be as "set it and forget it" as possible...if a DB was set to simple, it just doesnt do the backup, and vice versa. THANKS!
-- Cory
June 6, 2008 at 1:10 pm
Thanks. This bug fix has now been added to the new version. I didn't think about database snapshots, when I developed it.
Ola Hallengren
November 16, 2008 at 8:51 am
thanks for the script. It is beyond the best effort.
one question : I usually backup Log on hourly basis for production databases. If I use your script, every thing works fine except the log is not being truncated. Can we make any change to the script so I do not have to maintain TLog manually if I choose to use your script.
November 16, 2008 at 9:22 am
My script is using the normal BACKUP LOG command. When this command is performed the transaction log is truncated. (It is however not shrinked.)
Ola Hallengren
August 23, 2009 at 2:34 pm
I am wondering how your logging works. Where can I view the logs?
Thanks.
August 23, 2009 at 3:30 pm
I prefer to use SQL Server Agent output files. This way the output from the stored procedures are redirected to text files. I have an installation script that creates the objects and jobs with output files configured.
http://ola.hallengren.com/scripts/MaintenanceSolution.sql
Ola Hallengren
August 26, 2009 at 12:24 am
ola, just wanted to give you big thumbs up for the 3-in-1 DB maintenance script. within our sql environment it's working beyond our expectations.
keep up the good work!
September 4, 2009 at 2:24 am
Hello,
I was wondering why the publication date of the article is 0001/01/01
Anyway, I'm trying to watch the documentation on your website. It seems that it is no more available. Can you place it in the forum please ?
Thanks by advance,
Matthieu
September 4, 2009 at 8:58 am
I would like to use your script as it is far better than some thing I can write. Thanks for your contribution to DBA world. I would prefer to clean up backup files or tlog backups after 6 days. How could I accomplish this using your script ? Is it possible to add or update for this purpose with your next update?
thanks,
September 5, 2009 at 9:01 am
You can use the parameter @CleanupTime to delete old backup files. It's in hours so you have to set it to 144 (24 * 6) to keep backup files for 6 days. Please see this example for the transaction log backups.
EXECUTE dbo.DatabaseBackup
@databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@verify = 'Y',
@CleanupTime = 144
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy