Modify Backup Log SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Modify Backup Log SQL query

Author

Topic

robman
Starting Member

6 Posts

Posted - 2005-08-18 : 11:51:34

Hi

I am using MS SQL 2000 database.
My transactoin log file is getting full so i am creating a
job in ms sql 2000 which backup the transaction log file.

I want to modify the query
BACKUP LOG PRD TO DISK = 'S:\BackupLogs\PRDbklog.BAK'
to include the timestamp at the filename. for example
BACKUP LOG PRD TO DISK = 'S:\BackupLogs\PRDbklog<timestamp>.BAK

So how could i append the timestamp to the filename?

Thanks

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 12:13:59

We do something like this


DECLARE @BackupFile varchar(8000)
SELECT @BackupFile = 'S:\BackupLogs\PRDbklog' 
    + '_'
    + CONVERT(varchar(8), GetDate(), 112)	-- yyyymmdd
    + '_'
    + REPLACE(LEFT(CONVERT(varchar(8), GetDate(), 108), 5), ':', '')	-- hh:mm:ss
    + '.BAK'

BACKUP DATABASE MyDatabaseName
	TO DISK = @BackupFile
WITH	STATS = 10

Kristen

robman
Starting Member

6 Posts

Posted - 2005-08-18 : 14:06:13

Hi Kristen
Thanks a lot.It is working when i run the
query in the SQL QUERY ANALYZER tool.
I am trying to create a job from Enterprise Manager
-> <Server> -> SQL Server Agent -> Job
When i copied the same query in the job step and tried to
run it gets fail.
How could i make the query to run from job?

Again Thanks a lot

Regards
Rob

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 15:25:10

I would build a Stored Procedure, and call the SProc from the job

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 01:44:15

Or

Paste that query in the Command section of Job steps

Madhivanan

Failing to plan is Planning to fail

robman
Starting Member

6 Posts

Posted - 2005-08-19 : 15:55:43

Hi
I have created the stored prodecure and call the
procedure from the job and it is working fine now.

Thanks Kristen for your help

Bye