Post reply

Backup scripts

  • September 28, 2005 at 1:23 am

    #89170

    Hi All,

    Just want to seek opinions on my backup scripts. Is it totally wrong or is there a step I should add/remove?

    This is a job that I've created.

    Step 1: DBCC SHRINKFILE(DBHTM_LOG,1)

    Step 2: BACKUP LOG [DBHTM] TO [DBHTM Log Backup Device] WITH  INIT, NOUNLOAD ,  NAME = N'DBHTM Log backup',  NOSKIP ,  STATS = 10,  NOFORMAT

    Step 3: DBCC SHRINKFILE(DBHTM_Data)

    Step 4: BACKUP DATABASE [DBHTM] TO [DBHTM Backup Device] WITH  INIT ,  NOUNLOAD ,  NAME = N'DBHTM backup',  NOSKIP ,  STATS = 10,  NOFORMAT

    Step 5: DBCC SHRINKFILE(DBHTM_LOG,1) --shrink log again file again

    Step 6: DBCC UPDATEUSAGE(0)

    I'm thinking that I don't need Step2 'coz of Step 4

    Thanks heaps!

     

  • September 28, 2005 at 4:19 am

    #593592

    Do step 4 instead of step 2.

    I would question the need to shrink the log file unless freak activity has seen it grow dramatically. All that is going to happen is that you are going to suffer a performance deficit as the file grows back again.

    Same comment with the shrinking of the data file.

    Before and after this job I would backup the master database because the change in file sizes are held in the MASTER database.

  • September 28, 2005 at 6:26 pm

    #593883

    Hi David,

    Thanks for responding.

    I'm using DBCC SHRINKFILE only because the LOG file has never been backed up. Initially, I plan to shrink the LOG file once a month before backing it up. I got the idea of shrinking, backing up and then shriking the LOG file again from this article.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;q272318

    So I thought I'd apply the same to the DATA file (once a month shrinking, then backup.

    On a daily basis, I would use BACKUP LOG (mid day) and BACKUP LOG and  BACKUP DATABASE at night.

    Thanks again.

  • September 29, 2005 at 1:38 am

    #593942

    It is worth mentioning that although there is one LDF file this file also contains virtual files.

    If you run DBCC LOGINFO you will see precisely how many files there are. Microsoft recommend that there are no more than 32 and the way to get rid of them is to shrink the log as you are suggesting.

    If the log doesn't shrink by as much as you would expect then kick everyone off the system and try again.

    Once you have shrunk the log use the ALTER DATABASE statement to expand the log back up to a reasonable size in a single contigious chunk rather than let the log file grow of its own accord. This keeps the virtual files to a minimum,

  • September 29, 2005 at 5:47 pm

    #594225

    great, thanks, I'll edit my scripts.

Viewing 5 posts - 1 through 4 (of 4 total)

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