|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, November 20, 2009 10:40 AM
Points: 5,
Visits: 16
|
|
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
scheney@coritech.com
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 4,758,
Visits: 3,537
|
|
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
scheney@coritech.com
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.
 Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere 
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, November 20, 2009 10:40 AM
Points: 5,
Visits: 16
|
|
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."
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 4,758,
Visits: 3,537
|
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 272,
Visits: 345
|
|
use datepart function to get the time part of the date convert it to varchar and concatenate to you date string
thanks
Ramu
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 343,
Visits: 300
|
|
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)
|
|
|
|
