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

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

Group: General Forum Members
Last Login: Friday, April 11, 2014 5:47 AM
Points: 168, Visits: 1,829
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
Posted Wednesday, August 26, 2009 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:44 AM
Points: 3, Visits: 186
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
Posted Friday, September 04, 2009 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 10:05 AM
Points: 46, Visits: 72
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



Post #782770
Posted Friday, September 04, 2009 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 11:44 AM
Points: 11, Visits: 64
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
Posted Saturday, September 05, 2009 9:01 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, April 11, 2014 5:47 AM
Points: 168, Visits: 1,829
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
Posted Saturday, September 05, 2009 9:09 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, April 11, 2014 5:47 AM
Points: 168, Visits: 1,829
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
Posted Friday, November 13, 2009 1:23 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 27, Visits: 458
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
Post #818851
Posted Tuesday, August 17, 2010 5:57 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 547, Visits: 1,126
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
Posted Tuesday, August 17, 2010 10:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, April 11, 2014 5:47 AM
Points: 168, Visits: 1,829
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
Posted Tuesday, August 17, 2010 10:34 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, April 11, 2014 5:47 AM
Points: 168, Visits: 1,829
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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse