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 2 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, August 23, 2009 3:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:22 PM
Points: 167, Visits: 1,764

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

Post #775729

cppp

Posted Wednesday, August 26, 2009 12:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 6:01 AM
Points: 3, Visits: 177

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!

Post #777293

MatthieuQ

Posted Friday, September 04, 2009 2:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 5:14 AM
Points: 46, Visits: 71

Hello,
I was wondering why the publication date of the article is 0001/01/01 Smile

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

Post #782770

junkymail999

Posted Friday, September 04, 2009 8:58 AM

Grasshopper

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

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,

Post #782993

Ola Hallengren

Posted Saturday, September 05, 2009 9:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:22 PM
Points: 167, Visits: 1,764

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

Post #783335

Ola Hallengren

Posted Saturday, September 05, 2009 9:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:22 PM
Points: 167, Visits: 1,764

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

Post #783336

jswong05

Posted Friday, November 13, 2009 1:23 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:54 AM
Points: 27, Visits: 421

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
Tongue

Post #818851

P Jones

Posted Tuesday, August 17, 2010 5:57 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:35 AM
Points: 515, Visits: 1,016

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.

Post #970291

Ola Hallengren

Posted Tuesday, August 17, 2010 10:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:22 PM
Points: 167, Visits: 1,764

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

Post #970592

Ola Hallengren

Posted Tuesday, August 17, 2010 10:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:22 PM
Points: 167, Visits: 1,764

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.

Post #970597

« Prev Topic | Next Topic »

32 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions