Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SQL Server 2005 - Backup, Integrity Check and Index Optimization Expand / Collapse
Author
Message
Posted Sunday, February 24, 2008 5:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:56 PM
Points: 170, Visits: 1,879
Comments posted to this topic are about the item SQL Server 2005 - Backup, Integrity Check and Index Optimization
Post #459547
Posted Monday, April 21, 2008 3:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 22, 2008 11:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:56 PM
Points: 170, Visits: 1,879
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
Posted Friday, June 6, 2008 11:33 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay 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
Posted Friday, June 6, 2008 12:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:56 PM
Points: 170, Visits: 1,879
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
Posted Friday, June 6, 2008 12:53 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay 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
Posted Friday, June 6, 2008 1:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:56 PM
Points: 170, Visits: 1,879
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
Posted Sunday, November 16, 2008 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 11:44 AM
Points: 11, Visits: 64
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
Posted Sunday, November 16, 2008 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:56 PM
Points: 170, Visits: 1,879
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
Posted Sunday, August 23, 2009 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:07 PM
Points: 162, Visits: 37
I am wondering how your logging works. Where can I view the logs?

Thanks.
Post #775722
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse