Backup Error using T-SQL command

Question

Post reply

Backup Error using T-SQL command

Gurjit Hunjan

SSC Journeyman

Points: 77
More actions
September 15, 2017 at 12:08 am

#332577

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @pathwithname VARCHAR(500)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(4)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
SET @path = 'F:\Daily Backup\'
SELECT @time = GETDATE()
SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day = (SELECT CONVERT(VARCHAR(4), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))
SELECT @name ='kitty' + '_' + @year + '-'+ @month+ '-' + @day + '_'+ @hour+ ':' + @minute+ ':' + @second
SET @pathwithname = @path + @name + '.BAK'
BACKUP DATABASE [Kitty] TO DISK = @pathwithname WITH FORMAT, COMPRESSION, INIT, SKIP, REWIND, NOUNLOAD, STATS = 10 ;
Error
Log        SQL Server (Current - 15-09-2017 10:50:00)
Source        spid72
Message
Error: 18204, Severity: 16, State: 1
Date        15-09-2017 10:46:05
Log        SQL Server (Current - 15-09-2017 10:50:00)
Source        spid72
Message
BackupDiskFile::CreateMedia: Backup device 'F:\Daily Backup\kitty_2017-09-15_10:46:05.BAK' failed to create. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

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

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

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

does the sql server service account have write access to the given path?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gurjit Hunjan SSC Journeyman Points: 77 More actions · Answer 2

Dear Johan

YES
The SQL Server Services full access for the directory .
when i just remove some separator from the backup file the it will done..
(i.e.)
SELECT @name ='kitty' + '_' + @year + @month+ @day + @hour + @minute+ @second
it will work fine and no error ...

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Gurjit Hunjan - Friday, September 15, 2017 1:39 AM
Dear Johan
YES
The SQL Server Services full access for the directory .
when i just remove some separator from the backup file the it will done..
(i.e.)
SELECT @name ='kitty' + '_' + @year + @month+ @day + @hour + @minute+ @second
it will work fine and no error ...

You cant have : (colon) as a character in a file name its not allowed in Windows.

So you need to put in a different valid delimiter in the time if you want to delimit the time.

Generally there is no need to delimit if you use a standard format throughout eg YYYYMMDD_HHMMSS

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 4

anthony.green - Friday, September 15, 2017 1:54 AM
Gurjit Hunjan - Friday, September 15, 2017 1:39 AM
Dear Johan
YES
The SQL Server Services full access for the directory .
when i just remove some separator from the backup file the it will done..
(i.e.)
SELECT @name ='kitty' + '_' + @year + @month+ @day + @hour + @minute+ @second
it will work fine and no error ...
You cant have : (colon) as a character in a file name its not allowed in Windows.
So you need to put in a different valid delimiter in the time if you want to delimit the time.
Generally there is no need to delimit if you use a standard format throughout eg YYYYMMDD_HHMMSS

spot on, Anthony !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

CARMELO Newbie Points: 1 More actions · Answer 5

SI FUNCIONA SOLO NECESITAS REVISAR BIEN EL CODIGO...

CREATE PROCEDURE [dbo].[COPIASDESEGURIDAD]

AS

DECLARE @path VARCHAR(500)

DECLARE @name VARCHAR(500)

DECLARE @pathwithname VARCHAR(500)

DECLARE @time DATETIME

DECLARE @year VARCHAR(4)

DECLARE @month VARCHAR(2)

DECLARE @day VARCHAR(2)

DECLARE @hour VARCHAR(2)

DECLARE @minute VARCHAR(2)

DECLARE @second VARCHAR(2)

--------------------------

SET @path = 'D:\BACKUPS SQL SERVER\'

------------------------------

SELECT @time = GETDATE()

SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))

SELECT @month = (SELECT CONVERT(VARCHAR(2), DATEPART(mm,@time)))

SELECT @day = (SELECT CONVERT(VARCHAR(2), DATEPART(dd,@time)))

SELECT @hour = (SELECT CONVERT(VARCHAR(2), DATEPART(hh,@time)))

SELECT @minute = (SELECT CONVERT(VARCHAR(2), DATEPART(mi,@time)))

SELECT @second = (SELECT CONVERT(VARCHAR(2), DATEPART(ss,@time)))

---------------

SELECT @name ='RM' + '_' + @year +'_' + @month + '_'+ @day +'H_'+ @hour + 'M_'+ @minute + 'S_'+ @second

SET @pathwithname = @path + @namE + '.bak'

Captura