June 8, 2011 at 5:57 am
Am i missing something glaringly obvious here, i thought it would be good practice to play around with some date functions and add these to any ad hoc backups i take.
The problem is when i use the following code to declare and set the file name and run the backup the filename is missing the last line i.e colon minutes.bak
DECLARE @filename NVARCHAR (156)
SET @filename =
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\vpmserBak_Closed'
+ '_' + replace (str (datepart (mm, getdate ()), 2), ' ', '0')
+ '_' + datename (month, getdate ())
+ '_' + datename (year, getdate ())
+ '_' + datename (hour, getdate ())
+ ':' + datename (minute, getdate ()) +'.bak'
The file appears as vpmserBak_Closed_06_June_2011_12
and not as vpmserBak_Closed_06_June_2011_12:36.bak for example as should be expected
print 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\vpmserBak_Closed'
+ '_' + replace (str (datepart (mm, getdate ()), 2), ' ', '0')
+ '_' + datename (month, getdate ())
+ '_' + datename (year, getdate ())
+ '_' + datename (hour, getdate ())
+ ':' + datename (minute, getdate ()) +'.bak'
If i run the above just to check it should display ok everything is fine on screen: Connection C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\vpmserBak_Closed_06_June_2011_12:49.bak
I thought this was maybe down to the filename decalaration being too small but that wasn't the case, i've tried using escape characters couldn't get passed the Parser, i've looked in BOL and through various pages on the net as there has to be an issue with the colon but i just don't know what it is!?!?
Any help would be much appreciated.
Thanks in advance
Karl
June 8, 2011 at 6:04 am
Donut, file names can't contain a colon, ok ok back off!! 😀
March 30, 2012 at 8:45 am
Filenames can contain a colon on the NTFS file system. It signifies an Alternate Data Stream and is perfectly valid. If you backup to a file that has a colon, it will work, and the filename will appear truncated to where the colon was and the file will appear to be 0 bytes in size.
What happens is that all the backup database was put into an alternate data stream identified with everything after the colon. In the SSMS Restore Database GUI, you will not be able to restore that backup, but using TSQL you can.
Example: BACKUP DATABASE TEST TO DISK='TEST:123.BAK'
backup will succeed and you will see a file TEST with a size of 0 bytes.
In SSMS, RESTORE DATABASE, you will see TEST, but if you select it restore will fail.
If you type in the TSQL "RESTORE DATABASE TEST FROM DISK='TEST:123.BAK', it will work.
If you don't know the identifier of the ADS, you can use a tool like STREAMS to see it. You could also extract the backup data from the ADS and pipe it into a new file, using something like NOTEPAD TEST:123.bak or the Windows port of the Unix "cat" command.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply