November 5, 2009 at 12:51 pm
Hi there,
From what I've seen this question gets asked quite a bit, but I can't get a solution that works for me.
I am running a database using SQL Server Management Studio Express. There is no backup wizard. My HMI program can run short SQL scripts on a schedule. I want to take a daily backup of the database, say at midnite. And then I want to take transaction log backups every few hours.
I want each transaction log backup to have date AND timestamp included in the filename, but this does not seem easy. I can use this script:
DECLARE @fullpath nvarchar(255)
set @fullpath = 'c:\sql\logbak' + str(year(getdate())) +
str(month(getdate())) + str(day(getdate())) + '.trn'
BACKUP log cimplicity TO DISK = @fullpath WITH FORMAT, STATS
And it works, but it only has a datestamp. I need time included. It seems that many time data types and procedures aren't included or something on this instance of SQL.
Does anyone know how to get a timestamp included with filename? I just don't want to keep overwriting a file or keep growing a single file.
Thanks,
Scott Cheney
November 5, 2009 at 1:05 pm
scheney-1152259 (11/5/2009)
Hi there,From what I've seen this question gets asked quite a bit, but I can't get a solution that works for me.
I am running a database using SQL Server Management Studio Express. There is no backup wizard. My HMI program can run short SQL scripts on a schedule. I want to take a daily backup of the database, say at midnite. And then I want to take transaction log backups every few hours.
I want each transaction log backup to have date AND timestamp included in the filename, but this does not seem easy. I can use this script:
DECLARE @fullpath nvarchar(255)
set @fullpath = 'c:\sql\logbak' + str(year(getdate())) +
str(month(getdate())) + str(day(getdate())) + '.trn'
BACKUP log cimplicity TO DISK = @fullpath WITH FORMAT, STATS
And it works, but it only has a datestamp. I need time included. It seems that many time data types and procedures aren't included or something on this instance of SQL.
Does anyone know how to get a timestamp included with filename? I just don't want to keep overwriting a file or keep growing a single file.
Thanks,
Scott Cheney
1) you can take incremental backups using NOINIT. This will not overwrite the "old" backup file, but will add at the end. Because you are allready using a date in your filename, that would come to a log file per physical date, containing multiple log backups. You can restore them using the file=n parameter in your backup statement.
2) in your script you only added the date part. Have a look at the CONVERT function and the datetime formats it can convert to. Pick the one you need.
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
November 5, 2009 at 1:11 pm
Thanks for your fast reply.
That's the thing. I've tried to use/include: CONVERT (time, GETDATE()) and such, but SQL reports back that " 'time' is not a valid datatype."
November 5, 2009 at 2:12 pm
have a look at Books Online ( highlight your "convert" keyword and hit Shift+F1 if your query pane )
That will open your local copy of books online and show info on the selected keyword.
The online version is :
http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx
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
November 10, 2009 at 2:19 am
use datepart function to get the time part of the date convert it to varchar and concatenate to you date string
thanks
Ramu
November 11, 2009 at 12:33 pm
The code below will give you a time and date stamp down to the second. It's not pretty but you could make a function out of it.
An example of the output is: 20091111193024
Hope this helps.
select left(replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ':', ''), ' ', ''), 14)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply