September 15, 2017 at 12:08 am
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.).
September 15, 2017 at 1:00 am
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
September 15, 2017 at 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 ...
September 15, 2017 at 1:54 am
Gurjit Hunjan - Friday, September 15, 2017 1:39 AMDear JohanYES
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
September 15, 2017 at 3:18 am
anthony.green - Friday, September 15, 2017 1:54 AMGurjit Hunjan - Friday, September 15, 2017 1:39 AMDear JohanYES
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
December 7, 2021 at 7:49 pm
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'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply