Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    49

    Question Unanswered: Backing up and Truncating a transaction log in SQL

    I have a transaction log on on of my databases that is very big, and I need to backup the logs and truncate them to get some free disk space. The backup of the log needs to be placed on one of my remote backup servers. I would like to perform this task in SQL so I can put the code in a scheduled job. Thanks in advance for any help.

  2. #2
    Join Date
    Aug 2001
    Posts
    24
    Let me know if you mange to arrive at a defined process !!!
    I do have the same issue

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Backing up and Truncating a transaction log in SQL

    Do you care about what is in the log? What I mean is do you think you will have to recover from the log backup or not? If not, you can just dump it without creating a backup file. Then you can shrink the log file using DBCC Shrinkfile.

  4. #4
    Join Date
    Sep 2003
    Posts
    49
    My company likes to keep a backup of all logs no matter what for auditing purposes.

  5. #5
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    You need to set up a shared folder in the reomote server and run the following script,

    BACKUP LOG mydb TO DISK = '\\remoteservername\sharedfoledername\mydb_log_bac kup' WITH INIT, NOUNLOAD, NAME = 'mydb_log_backup', NOSKIP , STATS = 10, NOFORMAT

  6. #6
    Join Date
    Sep 2003
    Posts
    49
    Would that script also truncate the logs, or do I have to run BACKUP LOG DB WITH TRUNCATE_ONLY afterwards. Also what extension do I use for the backup log file. Example: DB_Logs_2003.bak. Is that OK? Thanks for all you help so far.

  7. #7
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    ??!! That's what I was asking you about. 'With Truncate only' will only dump the log for you without actually backing up the log for you. If you need keep the backup file then you should not use that option. The script DOES backup. Extension doesn't matter.

  8. #8
    Join Date
    Sep 2003
    Posts
    49

    Thumbs up

    Oh! I see what you are saying. Now I fully understand. Thanks for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •