Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: Differential Backup with RETAINDAYS

    I am looking into saving differential backup files for X number of days:


    I used the wizzard with RETAINDAYS here is the syntax:

    BACKUP DATABASE [Northwind] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\N' WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL , NAME = N'n', SKIP , STATS = 10, FORMAT

    When I run it overrides the first file.


    My question is should I rename the differential backup file and save it in a different folder. If so how do I do that?

    Or do you know of a script that will retain a differential backup for x number of days.

    Or do you know of a better solution.

    Thanks in advance

    Anu Ahuja

  2. #2
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Hi Anu,

    FYI: The Database Maintenance Wizard does the same for you for Full and Transaction Backups. But for Differential Backup , you have to write a script.

    The script below does the backup of the currentdatabase by adding dateand time along with databasename. So this will backup the database with new name everytime.

    Example: NorthwindAug231040.bak

    /* Script to Backup the database with Date and Time */
    /* Owner : Varad01 */
    /* Try this script first with Pubs database */

    Declare
    @CurrentDateTime varchar(20),
    @dbname varchar(20),
    @dbbackupname varchar(40)
    Begin

    Select @dbname=db_name()

    Select @CurrentDateTime =
    substring(DATENAME(month, getdate()),1,3) +
    cast(DATEPART(day, GETDATE()) as varchar(2))+
    cast(DATEPART(hh, GETDATE()) as varchar(2)) +
    cast(DATEPART(mi, GETDATE()) as varchar(2))

    Select @dbbackupname = @dbname+@CurrentDateTime

    select @dbbackupname ='C:\' + @dbbackupname + '.bak'

    backup database @dbname to disk=@dbbackupname WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS = 10, FORMAT

    End


    Hope this Helps.

    Have Fun

    Varad01
    MCDBA,MCSE

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    Thank you very much!!!


    Anu

  4. #4
    Join Date
    Aug 2002
    Posts
    21

    RetainDays not working

    Hello again,

    The above script is working great, I am able to create a unique name for the differential backup. BUT ........I would like the backup to be retained for 3 days and that is not working.

    Do I add a delete?

    Thanks in advance.

    Anu

  5. #5
    Join Date
    Aug 2002
    Location
    Manila
    Posts
    10
    Hi!

    Just a suggestion... why don't you create three separate folders for your three-day retention backup files. With this, you can safely specify different destination paths for each execution. Use the script below:

    /* Script using different destination folders per day*/
    /* Created by Boysie Jocson, Manila, Phils. */

    declare @day_week int,
    @directory char(80)
    set @day_week = datepart(dw,getdate())
    if @day_week = 1
    begin
    set @directory = '\\servername\day1\db_1stdiff.bak'
    end
    else if @day_week = 2
    begin
    set @directory = '\\servername\day2\db_2nddiff.bak'
    end
    else if @day_week = 3
    begin
    set @directory = '\\servername\day3\db_3rddiff.bak'
    end

    BACKUP DATABASE [dbname] TO DISK = @directory WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'db_diff', SKIP , STATS = 10, DESCRIPTION = N'db differential backup', NOFORMAT

    ... hope this helps.

  6. #6
    Join Date
    Aug 2002
    Posts
    21
    Thank you!! This is working.

    Anu

  7. #7
    Join Date
    Aug 2002
    Posts
    21
    Thank you!! This is working.

    Anu

  8. #8
    Join Date
    Aug 2002
    Location
    Manila
    Posts
    10
    Originally posted by anu
    Thank you!! This is working.

    Anu
    I'm happy to hear that. You're welcome!

Posting Permissions

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