October 12, 2006 at 12:23 am
Hello Friends..
I have a sql command that backs up my CollSoftware2 database as follows
BACKUP DATABASE [CollSoftware2]
TO DISK = N'C:\Auto_Backup__CollSoftware2.bck' WITH INIT , NOUNLOAD , Name = N'CollSoftware2', NOSKIP , STATS = 10, NOFORMAT
How can I modify this SQL command as Month_day_Year_ ... as follows:
BACKUP DATABASE [CollSoftware2]
TO DISK = N'C:\Month_day_Year__CollSoftware2.bck' WITH INIT , NOUNLOAD , Name = N'CollSoftware2', NOSKIP , STATS = 10, NOFORMAT
Please Help
October 12, 2006 at 3:41 am
Try this:
DECLARE @filename varchar(255)
DECLARE @date varchar(10)
SET @date = REPLACE(CONVERT(varchar(10),GETDATE(),101),'/','_')
SET @filename = 'C:\'+@date+'__CollSoftware2.bck'
BACKUP DATABASE [CollSoftware2]
TO DISK = @filename WITH INIT , NOUNLOAD , Name = N'CollSoftware2', NOSKIP , STATS = 10, NOFORMAT
Of course the @date variable is not necessary, you can compose @filename directly - I included it just to make more understandable what is being done.
PS.: I'm not sure whether VARCHAR works in this situation, you may need to use NVARCHAR. I can't test the SQL at the moment.
October 12, 2006 at 4:12 pm
You'll be happier with the results if you use a date format that orders the date YYYY-MM-DD. The files will sort properly by date, even from one month or year to the next.
I use the 112 (ISO) formatting most of the time for date stamping.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply