SQL Server Performance Forum – Threads Archive

I need Script to backup dbs and delete old backups

Hi!
Does somebody have a script similar to Data maintanence plan to backup and maintain database and delete old backup files.

---

Look for my script under this thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1269. You can modify the @backupname as per your requirement. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

thank you

---

Actually after satya’s help I put together some script:
But do not know how to redirect output messages write tolog.
DECLARE @BackupName varchar(50)
DECLARE @SourceFile varchar(255), @TargetFile varchar(255), @Action varchar(255)
Declare @db_name varchar(50) DECLARE list_dbs Cursor
FOR
select name from master..sysdatabases
where name not in (‘master’,’tempdb’,’msdb’) AND CONVERT(sysname, DatabasePropertyEx(name,
‘Status’))<>’OFFLINE’ OPEN list_dbs FETCH NEXT FROM list_dbs into @db_name WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @BackupName = ‘c:\test’+rtrim(@db_name +’_db_’+ rtrim(ltrim(datepart(yyyy,getdate()))) +
rtrim(ltrim( datepart(MM,getdate()))) +
rtrim(ltrim( datepart(DD,getdate())))+
rtrim(ltrim( datepart(hh,getdate())))+
rtrim(ltrim( datepart(mi,getdate()))) )
–BACKUP DATABASE @DBName TO @BackupName WITH RETAINDAYS=1 ,INIT BACKUP DATABASE @DB_Name TO [email protected] WITH RETAINDAYS=1,INIT
select @backupname
–******code**********************
FETCH NEXT FROM list_dbs into @db_name END
CLOSE list_dbs
DEALLOCATE list_dbs

---

Redirect the output to a output file by running the script using osql. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

By the way, you can get the date string like this: select replace(replace(replace(convert(varchar(16), getdate(), 120), ‘-‘, ”), ‘ ‘,”),’:’,”) Bambola.

---

By default messages about BACKUP database and log will be written to SQL error log. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>