Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Script to Backup SQL Server Log Files
Add to briefcase
13 posts, Page 1 of 2
12»»

Script to Backup SQL Server Log Files Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SQLCrazyCertified
Posted Wednesday, September 26, 2012 11:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 312, Visits: 1,865
Hi All,

Need assistance in creating a script for the below requirement.

Any assistance to satisfy this requirement will be greatly appreciated.

Due to the mirror being deployed, we need a script to backup database transaction log. This request is only for version SQL server 2008 and over

Key Items needed in script:

· Determine where the current transaction log resides and write trans log to this file directory.

· Script has to delete older transaction logs after backup transaction logs have been created

· If transaction log is greater than space available do not run backup, but send email to DBA “An attempt to create a transaction backup has failed due to lack of space.”

Let me know if you need more details or is this enough information to fulfill the above requirement.

Thanks,
SueTons
Post #1364860
opc.three
Posted Thursday, September 27, 2012 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 6,720, Visits: 11,756
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.

If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.

http://ola.hallengren.com/scripts/DatabaseBackup.sql

Database Backup Documentation

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1365240
ananda.murugesan
Posted Monday, October 01, 2012 6:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 822, Visits: 2,403
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.

If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.

http://ola.hallengren.com/scripts/DatabaseBackup.sql

Database Backup Documentation


What could be advantage hallengren database maintainance script instead of using builtin Management plan?
Post #1366518
SQLCrazyCertified
Posted Monday, October 01, 2012 6:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 312, Visits: 1,865

What could be advantage hallengren database maintainance script instead of using builtin Management plan?
[/quote]

You mean I can complete my requirement using SQL server maintenance plan?

Regards,
SueTons
Post #1366536
GilaMonster
Posted Monday, October 01, 2012 7:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977
SQLCrazyCertified (9/26/2012)

· Determine where the current transaction log resides and write trans log to this file directory.


Why do you want to write the log backups to the folder that contains the tran log? Along with potentially causing space issues, this means that if you ever lose the drive that has the tran logs on, you lose all your log backups too.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1366553
opc.three
Posted Monday, October 01, 2012 9:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 6,720, Visits: 11,756
ananda.murugesan (10/1/2012)
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.

If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.

http://ola.hallengren.com/scripts/DatabaseBackup.sql

Database Backup Documentation


What could be advantage hallengren database maintainance script instead of using builtin Management plan?

The built-in management plan will rebuild all indexes regardless of their fragmentation level. Ola's script inspects the level of fragmentation of each index and only rebuilds or reorganizes the indexes that require it per a fragmentation-threshold you set. The default of 30% for rebuild and 5% for reorganize is an acceptable place to start.

The bottom line is that Ola's script is more efficient in what it does and has far less impact on the transaction log after you have run it the initial time and allowed it to maintain any indexes that hadn't been maintained in a while. For most of the databases I have implemented his script on I run the script every night because at most I'll only see a handful of indexes be rebuilt or reorganized and running it every night keeps that number down so I do not leave a bunch of index work to do for the weekend. It also helps keep the indexes healthier by maintaining them as soon as they need it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1366615
SQLCrazyCertified
Posted Monday, October 01, 2012 3:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 312, Visits: 1,865
GilaMonster (10/1/2012)
SQLCrazyCertified (9/26/2012)

· Determine where the current transaction log resides and write trans log to this file directory.


Why do you want to write the log backups to the folder that contains the tran log? Along with potentially causing space issues, this means that if you ever lose the drive that has the tran logs on, you lose all your log backups too.


Hi Gail, I believe it should have said, "Determine where the transaction log backup resides and write the tlog backup to that directory"

I have to double check....I will get back to you.

Thanks,
SueTons.
Post #1366783
P Jones
Posted Tuesday, October 02, 2012 7:49 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: 2 days ago @ 7:35 AM
Points: 515, Visits: 1,016
I'll add another vote for Ola's method. Unless you know what you are doing and carefully configure your maintenance plans rather than just taking the defaults, you'll end up doing wasted work on all your indexes such as rebuilding (which effectively reorganises) after reorganising.

And Ola's method is far simpler to set up - run a script and then create a job or two. Once you've done it the first time you can script each job creation to a .sql file and then when you set up the next server just tweak the script for folders etc. and run it. It also helps to have a standard for server building - same drive letters (d:, l:, j:, b: etc.) used for data, trans logs, job logs and backups throughout your servers.

I also add a cmdexec jobstep in the agent job after the backup steps are complete to copy all backup files to another network location. This includes the in-day transaction log backups so we have full recovery if we lose a server.

My favourite thing about these methods is that they pick up any new databases and logs that have been created without your knowledge, for example by Sharepoint, and backs them up properly.
Post #1367044
SQLCrazyCertified
Posted Tuesday, October 02, 2012 1:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 312, Visits: 1,865
SQLCrazyCertified (10/1/2012)
GilaMonster (10/1/2012)
SQLCrazyCertified (9/26/2012)

· Determine where the current transaction log resides and write trans log to this file directory.


Why do you want to write the log backups to the folder that contains the tran log? Along with potentially causing space issues, this means that if you ever lose the drive that has the tran logs on, you lose all your log backups too.


Hi Gail, I believe it should have said, "Determine where the transaction log backup resides and write the tlog backup to that directory"

I have to double check....I will get back to you.

Thanks,
SueTons.



Gail,

I have verified that the statement should have said "Determine where the transaction log backup resides and write the tlog backup to that directory".

Thanks,
SueTons.
Post #1367274
SQLCrazyCertified
Posted Tuesday, October 02, 2012 2:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 312, Visits: 1,865
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.

If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.

http://ola.hallengren.com/scripts/DatabaseBackup.sql

Database Backup Documentation


I really cannot follow most of the things in this script. Can someone please explain what's the summary of this script is doing?.....I am not very good with scripting.

Also, can Ola's script suffice my requirement?....or is it little too much for my requirement.

Thanks,
SueTons.
Post #1367277
« Prev Topic | Next Topic »

Add to briefcase
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.