Sql Server 2000 backup filename issue

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 Administration (2000)

Sql Server 2000 backup filename issue

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-22 : 08:31:14

Rob writes "Hi. I'm trying to do some simple backups, and want to include a timestamp of sorts in the filename of the backup *.bak file.

BACKUP DATABASE [DYNAMICS] TO DISK = N'D:\DYNAMICS backup.BAK' WITH INIT , NOUNLOAD , NAME = N'DYNAMICS backup', NOSKIP , STATS = 10, NOFORMAT

Is the command I'm running in the job, but I was wondering if there was a system variable or something of that sort that could be utilized to add a date string.

Thanks,
Rob"

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2003-12-22 : 09:48:24

You can use SQL Server maintenace plan in order to schedule your db and t-log backups and of course end up in having the date string you are looking for.
The name would be something like this:
YOUR_DB_NAME_db_YYYYMMDDHHMM.BAK or
YOUR_DB_NAME_tlog_YYYYMMDDHHMM.TRN
Kind regards.

Franco

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-12-22 : 10:31:35

Or you could build and execute a string. Build the backup name using the CONVERT function to get the yyyymmdd format.

Something like this

DECLARE @CMD VARCHAR(2000)
SET @cmd = 'BACKUP DATABASE [DYNAMICS] TO DISK = N''C:\DYNAMICS backup ' + CONVERT(varchar(8), GETDATE(), 112) + '.BAK'' WITH INIT , NOUNLOAD , NAME = N''DYNAMICS backup'', NOSKIP , STATS = 10, NOFORMAT'
EXEC (@cmd)

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources