SQL Server 2005 - Backup, Integrity Check and Index Optimization

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ola Hallengren » SQL Server 2005 - Backup, Integrity Check and...

32 posts, Page 1 of 4

1 2 3 4 »»»

SQL Server 2005 - Backup, Integrity Check and Index Optimization

Rate Topic

Display Mode

Topic Options

Author

Message

Ola Hallengren

Posted Sunday, February 24, 2008 5:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 167, Visits: 1,759

Comments posted to this topic are about the item SQL Server 2005 - Backup, Integrity Check and Index Optimization

Post #459547

idoncov

Posted Monday, April 21, 2008 3:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 11:16 AM
Points: 1, Visits: 23

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.

Post #488271

Ola Hallengren

Posted Tuesday, April 22, 2008 11:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 167, Visits: 1,759

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
http://ola.hallengren.com

Post #488777

Cory E.

Posted Friday, June 06, 2008 11:33 AM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845

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

Post #513106

Ola Hallengren

Posted Friday, June 06, 2008 12:47 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 167, Visits: 1,759

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
http://ola.hallengren.com

Post #513151

Cory E.

Posted Friday, June 06, 2008 12:53 PM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845

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

Post #513160

Ola Hallengren

Posted Friday, June 06, 2008 1:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 167, Visits: 1,759

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
http://ola.hallengren.com

Post #513169

junkymail999

Posted Sunday, November 16, 2008 8:51 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 20, 2011 8:38 PM
Points: 11, Visits: 63

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.

Post #603339

Ola Hallengren

Posted Sunday, November 16, 2008 9:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 167, Visits: 1,759

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
http://ola.hallengren.com

Post #603345

cxyzhang

Posted Sunday, August 23, 2009 2:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 27, 2012 11:43 AM
Points: 162, Visits: 25

I am wondering how your logging works. Where can I view the logs?

Thanks.

Post #775722

« Prev Topic | Next Topic »

32 posts, Page 1 of 4

1 2 3 4 »»»

Permissions