November 9, 2007 at 9:22 am
Hello All,
I wonder if somebody could help me.
I'd like to keep a database back up set for a week and backup set should expire after a week. meaning on day 8, day one is deleted so you always have 7 weeks database backup stamped with the date it was backed up.
What I have done I wrote a T-SQL and it does the job but what I'd like more to do is to leave this job to SQL server 2005. Basicallly, SQL Srever 2005 does part of this job for you but it does not stamp the data with a particualr date and does not delete the previous backups.
Thus, It will be appriciat if somebody has done this before using SQL server instead of writing the copd himself/herself. does the Maintenace Plan does this job.
Looking forward to your valued responses guys,
Awaiting....
November 9, 2007 at 11:34 am
We are using this to create a nice name for our backups, but I'm looking for code to delete files after a certain day too. Maintenance plans always seem to mess this up for us.
DECLARE @DBNAME SYSNAME
DECLARE @sql VARCHAR (250)
DECLARE DB CURSOR FOR
SELECT NAME FROM SYS.DATABASES WHERE NAME NOT IN ('TEMPDB')
OPEN DB
FETCH NEXT FROM DB INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'BACKUP DATABASE [' +
@DBNAME +
'] TO DISK = ''G:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' +
@DBNAME + '\' +
@DBNAME + '_FULL_' + convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(3),getdate(),108),':','') +
'.BAK'''
EXEC (@SQL)
FETCH NEXT FROM DB INTO @DBNAME
END
CLOSE DB
DEALLOCATE DB
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 14, 2007 at 3:57 am
:hehe: I was more interested in knowing how can this job be done rather thatn carry on others problems :D. So proper helps will be appreciate.
November 14, 2007 at 6:31 am
Yes. Maintenance Plan does this job.
November 14, 2007 at 7:04 am
How mate a short clue would help?
Thanks
November 14, 2007 at 7:18 am
Use "Maintenance Cleanup Task" to delete older .bak files.
Maintenance Plan automatically adds date time stamp to the filename.
e.g. master_backup_200711132245.bak
November 14, 2007 at 7:29 am
Thank you indeed Suresh.
It worked perfectly mate.
Cheers
November 14, 2007 at 7:31 am
Welcome.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply