Remove 'spaces' from DB backup filename

Question

Remove 'spaces' from DB backup filename

J2B

Grasshopper

Points: 14
More actions
August 26, 2016 at 5:46 am

#400634

I need some help to remove the spaces from my backup job...
Its outputting as:
"dbname_DD MMM YYYY.bak"
But I would like:
"dbname_DDMMMYYYY.bak"
Heres my T-SQL...
USE dbname
DECLARE @dbname varchar(200)
SELECT @dbname='filepath\dbname_' + REPLACE(convert(varchar(20),GetDate(),106),':','-') + '.bak'
BACKUP DATABASE [dbname] TO DISK=@dbname with compression
Thanks

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You have the wrong characters in your REPLACE function. Use the correct ones and you should get it done.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

REPLACE() - https://msdn.microsoft.com/en-us/library/ms186862.aspx

J2B Grasshopper Points: 14 More actions · Answer 3

Thanks both, I'm using "REPLACE(convert(varchar(20),GetDate(),106),' ','') + '.bak'" which has done the trick!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

J2B (8/26/2016)
I need some help to remove the spaces from my backup job...
Its outputting as:
"dbname_DD MMM YYYY.bak"
But I would like:
"dbname_DDMMMYYYY.bak"
Heres my T-SQL...
USE dbname
DECLARE @dbname varchar(200)
SELECT @dbname='filepath\dbname_' + REPLACE(convert(varchar(20),GetDate(),106),':','-') + '.bak'
BACKUP DATABASE [dbname] TO DISK=@dbname with compression
Thanks

Just as an "ease of maintenance" and "ease of discovery" for your backup file names, you should store the files with sortable names AND a time when the backup started in the form of dbName_YYYYMMDD_HHMISS. This is especially true when it comes to taking dozens of log file backups each day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)