Sql 2005 trigger for taking backup of particular t

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

General SQL Server Forums

New to SQL Server Programming

Sql 2005 trigger for taking backup of particular t

Author

Topic

meenus
Starting Member

10 Posts

Posted - 2012-01-16 : 10:53:28

Dears,Advance thanks to all.

I need a help in SQL2005

i have a db named DB_BC,and this db contains 5 tables.One of this table named TB_BC.In this table daily nearly 500 rows is inserting through website.This table fields are Slno,Datetime_dat,Name,Hrd

Now my requirement is

All the data til 3 month back should delete in the first day of Everymonth .Before deleting table back up should create in one folder with these 3 month back data.

For eg:

In TB_BC i have jan,feb,mar,apr,may,jun,jul,aug datetime values in Datetime_dat

MAY 1 ,one trigger should execute to take backup of jan month data from TB_BC with the backupname JAN/2012

MAY 1 , one trigger should execute to delete all jan month data from TB_BC

June 1 ,one trigger should execute to take backup of Feb month data from TB_BC with the backupname FEB/2012

june 1 , one trigger should execute to delete all FEB month data from TB_BC

How can i do this?Is it possible to do this by using a trigger? I dont have any idea how to do this.Please help me and shre me your ideas.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:02:45

no need of trigger for this. what you need is to have stored procedure which does this and you need to call it from sql server agent job and schedule to execute it on month start.

one question here is do you need to store backup in another table or as .bak file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meenus
Starting Member

10 Posts

Posted - 2012-01-17 : 07:18:05

i need to keep .bak file in one particular folder.Not in another table.Any link is available for writing this type of stored procedure?

quote:
Originally posted by visakh16

no need of trigger for this. what you need is to have stored procedure which does this and you need to call it from sql server agent job and schedule to execute it on month start.

one question here is do you need to store backup in another table or as .bak file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meenus
Starting Member

10 Posts

Posted - 2012-01-21 : 01:59:03

Dear friend,based on your suggestion i created a stored procedure for taking a back up.Now i want to know how can shedule this first day of everymonth .I am new to create sql server agent jobs.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[Backup_Monthly_Scedul]
AS
--This procedure
SET NOCOUNT ON

declare @st as varchar(800)
declare @st1 as varchar(300)
--set @st='BCK' & GETDATE() & 'Del' & DATEADD(month, -2, GETDATE())
--set @st1=DATEADD(month, -2,dateadd(dd,0, datediff(dd,0, getDate())))
--set @st= dateadd(dd,0, datediff(dd,0, getDate()))
set @st=GETDATE()
set @st1=DATEADD(month, -2, GETDATE())

set @st= 'BCKDT'+@st +'Deleted-Lessthan'+ @st1
set @st= replace(@st,':','.')
set @st='C:\SqlMonthlyDB\'+@st
--select @st
BACKUP DATABASE aaabackup TO DISK = @st
----jan ,feb, mar,apr,may,jun,jul,aug,sep,oct,nov,dec
----Erase structure
----jan1 erase oct
----feb1 erase nov
----mar1 erase dec
----apr1 erase jan
----may1 erase feb
----jun1 erase mar
----juL1 erase apr
----aug1 erase may
----sep1 erase june
----oct1 erase july
----nov1 erase aug
----dec1 erase sep
DELETE FROM [aaabackup].[dbo].[Positions_iTrac]
where datum<DATEADD(month, -2, GETDATE())

meenus
Starting Member

10 Posts

Posted - 2012-01-21 : 02:42:30

i success in doing to set sql server job to call the procedure.Now i have one probolem.back up is taking continuous but i am not getting notification .I entered my email address in notification section.Why mails are not sending???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-24 : 19:24:47

quote:
Originally posted by meenus

i success in doing to set sql server job to call the procedure.Now i have one probolem.back up is taking continuous but i am not getting notification .I entered my email address in notification section.Why mails are not sending???

what have you set as condition for notification? is it on success or on failure of the job?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources