Script to Backup SQL Server Log Files

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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

    Regards,
    SQLisAwe5oMe.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • SQL Galaxy

    One Orange Chip

    Points: 29228

    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?

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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

    Regards,
    SQLisAwe5oMe.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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, MVP, M.Sc (Comp Sci)
    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
  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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.

    Regards,
    SQLisAwe5oMe.

  • P Jones

    SSChampion

    Points: 12323

    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.

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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.

    Regards,
    SQLisAwe5oMe.

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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.

    Regards,
    SQLisAwe5oMe.

  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    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?

    · Determine where the current transaction log backup files resides and write trans log to this file directory........I can I achieve this using Maintenance Plan

    · Script has to delete older transaction logs after backup transaction logs have been created......I can I achieve this using Maintenance Plan

    · 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.”....

    Can someone please advise if this also can he achieved using maintenance plans as well......I know we can set up alerts.....but is there a script to check the available space before running the backup and if there is not enough space then send email......is this possible?....someone please advise.

    Thanks,

    SueTons

    Regards,
    SQLisAwe5oMe.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Honestly, you shouldn't be using your backup plans to monitor for drive space. There should be existing alerts and the DBA should have known long before a log backup would fail that the drive is low on space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • SQLisAwe5oMe

    SSChampion

    Points: 13851

    SQLCrazyCertified (9/26/2012)


    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 backup 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

    This what I found/put together.....I want to create a job with 3 steps and use the codes below. Can someone review these code and advise if this will work for my requirement above?

    ------------------------------------------------------------------------------------------

    --// Transaction Log Backup User Databases where recovery model = Full/Bulk\\--

    ------------------------------------------------------------------------------------------

    DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = '\\ServerName\DriveName\FolderName\'

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    + '_'

    + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

    DECLARE db_cursor CURSOR FOR

    SELECT name

    FROM master.dbo.sysdatabases

    WHERE name NOT IN ('master','model','msdb','tempdb')

    AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @fileName = @path + @name + '_' + @fileDate + '.TRN'

    BACKUP LOG @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    ------------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------------

    --// Script to check the free space on the drive and send alert when free space drops

    below particular threshold setting for the drive \\--

    ------------------------------------------------------------------------------------------

    declare @MB_Free int

    create table #FreeSpace(

    Drive char(1),

    MB_Free int)

    insert into #FreeSpace exec xp_fixeddrives

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'

    -- Free Space on C drive Less than Threshold

    if @MB_Free < 1024

    exec master.dbo.xp_sendmail

    @recipients ='youremailaddress.com',

    @subject ='ServerName - Free Space Issue on C Drive',

    @message = 'Free space on C Drive

    has dropped below 1 gb'

    ------------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------------

    --// Maintenance Cleanup Task (Delete backup Files, Search Folder(Folder Path)

    and delete files based on an extention(trn), Delete files older than 2 weeks.\\--

    ------------------------------------------------------------------------------------------

    EXECUTE master.dbo.xp_delete_file 0

    ,N'D:\MSSQL\BACKUP'--Folder path to be deleted.

    ,N'trn' --Delete file names start with trn extention

    ,N'2012-09-20T10:13:42' --Delete trn files older than 2 weeks.

    ------------------------------------------------------------------------------------------

    Regards,
    SQLisAwe5oMe.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply