asked on

Naming a .bak appropriately

I have a destination on the network where I like to keep all of my sql backups. Now for some reason when I go to do a backup to this location the name of the .bak always wants to be the same name. What I would like to do is give the .bak file an appropriate name that corresponds to the database name.

So on the "Back Up Database" Menu, I remove the default back up location, I click the "Add" button and choose my back up device, then create the back up. Problem is I don't know how to make sure that the created BAK is appropriately named. Any direction or insight would be greatly appreciated

Jason

tigin44

construct your backup command dynamically and add the date to the end of backup file name.

chapmandew

there is code I wrote here that you can use as a base:
http://sqlservernation.com/blogs/tipweek/archive/2009/02/23/automated-sql-server-express-backups.aspx

dbidba

-- ============================================================================
-- Example script to do a differential database backup.
-- Place this in a SQL Agent job and schedule as desired.
-- ============================================================================
-- ----------------------------------------------------------------------------
-- Declare Variables.
-- ----------------------------------------------------------------------------
declare
   @Cmd      nvarchar(4000)   -- Temporary Command holder.
  ,@Msg      varchar(8000)    -- Temporary Messate holder.
  ,@DbNa     sysname          -- Name of Database to backup.
  ,@BkuPthNa varchar(1000)    -- Backup Path Name.
  ,@BkuFilNa varchar(1000)    -- Fully Qualified Backup File Name.

-- ----------------------------------------------------------------------------
-- Assign Constants.
-- ----------------------------------------------------------------------------
select
   @BkuPthNa = 'g:\SqlBackup\LS009\'
  ,@DbNa = 'Dave'

-- ----------------------------------------------------------------------------
-- Build the backup file name.
-- ----------------------------------------------------------------------------
select
  @BkuFilNa = @BkuPthNa
  + @DbNa
  + '\'
  + @DbNa
  + '-Diff-'
  + convert(varchar(20), getdate(),112)
  +'-'
  + replace(convert(varchar(20), getdate(),108),':','')
  + '.bak'

-- ----------------------------------------------------------------------------
-- Create the base backup command.
-- ----------------------------------------------------------------------------
select @Cmd = 'backup database @DbNa to disk = @BkuFilNa with differential'

-- ----------------------------------------------------------------------------
-- Print the command to be executed.
-- ----------------------------------------------------------------------------
select @Msg = replace(@Cmd,'@DbNa',@DbNa)
select @Msg = replace(@Msg,'@BkuFilNa',''''+@BkuFilNa+'''')
select @Msg = 'Command = ' + @Msg
print @Msg

-- ----------------------------------------------------------------------------
-- Execute the command using the runtime values.
-- ----------------------------------------------------------------------------
exec sp_executesql
   @Cmd
  ,N'@DbNa sysname,@BkuFilNa varchar(1000)'
  ,@DbNa
  ,@BkuFilNa

Open in new window

ASKER CERTIFIED SOLUTION

dbidba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial