Comments posted to this topic are about the item SQL Server 2005 - Backup, Integrity Check and Index Optimization
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.
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
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?
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.
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'
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
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!
Thanks. This bug fix has now been added to the new version. I didn't think about database snapshots, when I developed it.
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.
My script is using the normal BACKUP LOG command. When this command is performed the transaction log is truncated. (It is however not shrinked.)
I am wondering how your logging works. Where can I view the logs?
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.
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!
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,
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?
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.
@databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 144
Viewing 15 posts - 1 through 15 (of 40 total)