Post reply

differential backups

  • January 21, 2008 at 12:29 am

    #154074

    Hi Guys,

    I'm trying to implement a differential back up plan but I was thinking that it would be a good idea to add a date stamp to the files it backs up, like the Maintenance plans do.

    Otherwise it will overwrite my file every day.

    backup_diff_210108.bak

    backup_diff_220108.bak

    backup_diff_230108.bak

    Is this a good idea? If so how do you go about adding the datestamp? Something like the line below which doesn't parse.

    BACKUP DATABASE [Northwind] TO DISK = N'W:\backups\backup_diff'+getdate()+'.bak' WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

    thanks for your time.

  • January 21, 2008 at 4:47 am

    #770089

    My backup procedure has the following structure

    DECLARE @DatabaseName varchar(1000)

    DECLARE @BackupLocation varchar(1000)

    DECLARE @DayExtension varchar(1000)

    /*adding day + time*/

    SET @DayExtension =REPLACE(REPLACE(REPLACE(convert(varchar(30),CURRENT_TIMESTAMP,120),'-','_'),':','_'),' ','_')

    SET @Backuplocation= '...defaultlocation...'

    /*defaultlocation*/

    SET @BackupLocation=@Backuplocation+'\'+@DatabaseName+'_'+@DayExtension +'_DIFF.BAK'

    BACKUP DATABASE @DatabaseName

    TO DISK = @BackupLocation

    WITH INIT , NOUNLOAD, NAME = @BackupName, NOSKIP , NOFORMAT, DIFFERENTIAL,STATS=10

    It sorts nicely in windows explorer.

    The advantage to add the type at the back of the filename, it that you can easily follow the chronical order of the backups in windows explorer (full, diff, log).

  • January 21, 2008 at 10:31 pm

    #770333

    Thanks for that..it's very helpful.

    Can you tell me how you set this up as there are 2 variables -@BackupName and @DatabaseName which you don't assign values to in the script. Are these put in somewhere else? Is the whole script put inside a scheduled job?

    Is your full back up called from a similar script inside a scheduled job?

    thanks for your help.

  • January 22, 2008 at 6:48 am

    #770444

    Hello,

    I've currently 3 different stored procedures with the same generic layout

    EXECUTE master.dbo.USP_BACKUP_DATABASE @DatabaseNaam='MYDB'

    EXECUTE master.dbo.USP_BACKUP_DATABASE_DIFF @DatabaseNaam='MYDB'

    EXECUTE master.dbo.USP_BACKUP_DATABASE_LOG @DatabaseNaam='MYDB'

    Currently redesigning it for controlled restores.

    Someone posted an artikel about custom logshipping about a month ago.

    Generic Layout:

    CREATE PROCEDURE dbo.USP_BACKUP_DATABASE

    (

    @DatabaseNaam sysname

    ,@BackupLocatie nvarchar(255)=NULL /*NULL=default location*/

    )

    AS

    SET NOCOUNT ON

    DECLARE @DagExtensie varchar(30)

    DECLARE @BackupNaam nvarchar(150)

    DECLARE @ParameterDirectoryWissen nvarchar(500)

    DECLARE @ParameterDirectoryMaken nvarchar(500)

    --DECLARE @BackupLocatie varchar(100)

    /*generate timestamp suffix for easy sorting*/

    SET @DagExtensie=REPLACE(REPLACE(REPLACE(convert(varchar(30),CURRENT_TIMESTAMP,120),'-','_'),':','_'),' ','_')

    SET @BackupNaam=@DatabaseNaam+' backup'

    IF @BackupLocatie IS NULL

    --SET @BackupLocatie='G:\'+@DatabaseNaam

    SET @BackupLocatie='\\192.168.0.20\MySERVER'--+@DatabaseNaam

    /*Kill old backups, obsolete Oude backups wissen */

    --SET @ParameterDirectoryWissen='rmdir /S /Q '+@BackupLocatie

    --SET @ParameterDirectoryMaken='mkdir '+@BackupLocatie

    --EXECUTE master.dbo.xp_cmdshell @ParameterDirectoryWissen,no_output

    --EXECUTE master.dbo.xp_cmdshell @ParameterDirectoryMaken,no_output

    /*Add extension FULL to indicate a FULL BACKUP*/

    SET @BackupLocatie=@Backuplocatie+'\'+@DatabaseNaam+'_'+@DagExtensie +'_FULL.BAK'

    BACKUP DATABASE @DatabaseNaam

    TO DISK = @BackupLocatie

    WITH INIT , NOUNLOAD, NAME = @BackupNaam, NOSKIP , NOFORMAT

    SET QUOTED_IDENTIFIER OFF

    SET ANSI_NULLS ON

    GRANT EXECUTE ON [dbo].[USP_BACKUP_DATABASE] TO MYUSER

    GO

  • January 22, 2008 at 9:20 am

    #770505

    There are lots of scripts in the scripts section of this site to do this. Put this in a stored procedure and call that from the job.

  • January 22, 2008 at 11:51 am

    #770596

    There are tons of scripts out there, make sure you pick out one that accepts parameters like dbname, or all databases, or user/system databases, and the cleanup task (file retention) period, which totally mimicks maint plan,..makes life easier. But then yeah, you can always script out looping thru your db names, and doing the rest yourself.

    _____________
    Donn Policarpio

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

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