March 9, 2005 at 3:08 pm
I am backing up my DB to an Optical Jukebox so I can archive the database on UDO Write Once Media. I am doing this with one job that I want to run at least once a month. The problem is I can't overwrite the file so I need to be able to rename it with each backup. I would like to append the date of the backup to the file name if I could. Is there any way to do this within EM or with an SP? Thanks in advance!
March 9, 2005 at 11:49 pm
You may create a Full Database Backup Job using Database Maintainence Planner using SQL EM.
Do not specify any name for the backup file. It will automatically create a new backup file with every job and also have the datetime timestamp as part of the backup file.
--Kishore
March 10, 2005 at 10:06 am
Its not difficult to do this with a stored proc. You'll need to build the backup command within the proc with the "TO DISK =" clause. You'll build the filename within the proc as well by appending the date and/or time to the end of the filename. Then execute the backup command from the proc. Here's an example of building the filename and then executing the backup. You'll see that I'm replacing ":" with '-' and spaces with '_' after creating the filename...
select @BKPfilename = @DB + '_Log_' + convert(varchar(26),getdate(),20) + '.TRN'
select @BKPfilename = REPLACE(@BKPfilename,':','-')
select @BKPfilename = REPLACE(@BKPfilename,' ','_')
select @BKPfilename = @BKP_Dir + @BKPfilename
SELECT @command = 'BACKUP LOG ' + @DB + ' TO DISK = ''' + @BKPfilename + ''' WITH INIT, STATS = 25'
PRINT '---> ' + @command + ' <---'
EXEC (@command)
-- Steve
March 10, 2005 at 10:25 am
As always thanks for the response. We are a really small network and this jukebox is serving primarily as a paperless system but I have wanted to get my DB backups onto some kind of faster, less delicate media than DLT,the UDO disks hold 30GB (soon 60 and then 120). Have you ever really seen the need to archive a database to WORM (had a few extras) media once/twice a month as I am doing? (aside from HIPAA or Sarbanes Compliant Companies) or is there really no need and I am just wasting space?
March 10, 2005 at 10:58 am
Obviously, if your data is of any importance at all, some sort of external storage is a must. What you use for that storage is a matter of what is available, what you can afford, etc. I don't know the costs associated with UDO vs DLT, but its just something that you'll have to weigh out based on the importance and volatility of the data.
Steve
March 10, 2005 at 12:52 pm
We burn our Accounting databases to CD/DVD. I keep a snapshot of the database at calendar year end and another after they close the prior year end. This is for IRS and our own Auditing/Acctg firm.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply