• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Db full backup name changing

I have the following procedure running automatically to execute fll backups of a MS-SQL 2005 DB.

DECLARE      @return_value int

EXEC      @return_value = [dbo].[BackupDb] @DB = N'mydbname',
            @ImagePath = N'd:\dbbackups\',
            @ImageFileName = N'mydbnamefull.bak',
            @IsDifferential = 0

SELECT      'Return Value' = @return_value

GO

Is there any way this procedure can change the name of the .bak file to a different name everytime it runs. Let's say adding the date to the name?
Currently, everytime the procedure runs, it replaces the file with the new one. I'd like to keep the old files all the time.

Another option could be adding "something" to the procedure to create a new folder (with new name) everytime and store the .bak file there.

Any way to run this procedure without replacing the previous .bk file?


0
sweetbuttercup
Asked:
sweetbuttercup
  • 3
  • 3
  • 2
1 Solution
 
Lee SavidgeCommented:
Hi,

When you assign the name, replace

@ImageFileName = N'mydbnamefull.bak',

with

@ImageFileName = N'mydbnamefull-' + convert(varchar(255), getdate(), 112) + right('00' + cast(datepart(hh, getdate()) as varchar(2)), 2) + right('00' + cast(datepart(mm, getdate()) as varchar(2)), 2) + right('00' + cast(datepart(ss, getdate()) as varchar(2)), 2)+ '.bak'


This will give you a file name that is unique, in the form

mydbnamefull-yyyymmddhhmmss.bak

which should be pretty unique

Hope this helps.

Regards,

Lee
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sweetbuttercup,
> @ImageFileName = N'mydbnamefull.bak'
@ImageFileName = N'mydbnamefull'+REPLACE(REPLACE(CONVERT(varchar,getdate(),113),':',''),' ','')+'.bak'
0
 
Lee SavidgeCommented:
Hi,

On reflection there is always

@ImageFileName = N'mydbnamefull' + replace(replace(replace(replace(convert(varchar,getdate(),121), '-', ''), ' ', ''), ':', ''), '.', '') + '.bak'

Does the same as what I did earlier, but the date format is always numerical which makes it easier searching on a directory listing for the latest as it will always be listed at the bottom if you list things by name. This is because the resultant number is an ever incrementing number.

Regards,

Lee
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sweetbuttercupAuthor Commented:
I'm getting an error (Incorrect syntax near '+'.) when I run  ANY of the solutions stated above.
Is it because I have the following defined in the store procedure?  I have very basic knowledge of this.

--Stored Proc Parameters
@ImageFileName NVARCHAR(100),
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
All codes are working fine ..

declare @ImageFileName nvarchar(100)
SET @ImageFileName = N'mydbnamefull'+REPLACE(REPLACE(CONVERT(varchar,getdate(),113),':',''),' ','')+'.bak'
SELECT @ImageFileName
SELECT @ImageFileName = N'mydbnamefull' + replace(replace(replace(replace(convert(varchar,getdate(),121), '-', ''), ' ', ''), ':', ''), '.', '') + '.bak'
SELECT @ImageFileName


can you post the entire procedure
0
 
sweetbuttercupAuthor Commented:
STORE PROCEDURE:
Create PROCEDURE [dbo].[BackupDb]
      --Stored Proc Parameters
      
  @DB nvarchar(30), -- Database Name
  @ImagePath NVARCHAR(1024), -- Path to where the backup image will be stored
  @ImageFileName NVARCHAR(100), -- Name of the actual backup file
  @IsDifferential bit -- Value defining if it is a Differential or full backup 1 = incremental, 0 = full
 
AS
      
      DECLARE @FullFilePath NVARCHAR(1075)
      DECLARE @with nvarchar(300)
      
      if  substring(@ImagePath, len(@ImagePath), 1) <> '\'
            set @ImagePath = @ImagePath + '\'

      SET @FullFilePath = @ImagePath + @ImageFileName
      if @IsDifferential = 1
            backup database @db to disk = @FullFilePath with init, differential
      else
            backup database @db to disk = @FullFilePath with init

GO
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Is it still giving any error ?
0
 
sweetbuttercupAuthor Commented:
It works perfectly!!!!!! Thank you so much!

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now