Automatic Scheduled database Backup

Necessicity is the mother of invention.......thought i am not inventor,
some time back i was requested to give some utility to the clients that they will schedule the backup database and in certains Drive i.e in C:\Dump folder all of my backup should go....

It is done by creating simple procedure which is shown below:
But before doing so, one must register their devices , by just executing following command
1. EXEC sp_addumpdevice 'disk','mydiskdump','C:\Dump'
after doing this, one will run the procedure and put it in scheduler, your dump will go to C:\drive
USE [master]
GO
/****** Object: StoredProcedure [dbo].[custom_proc_backupdatabase] Script Date: 07/04/2008 10:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[custom_proc_backupdatabase] as
-- THIS PROCEDURE IS FOR MAINTANANCE PURPOSE FOR DATABASE
--CREATED BY PARTH ON 5TH MAY 2008
declare
@db varchar(300),
@dbname varchar(300),
@sql nvarchar(1000),
@backupdate varchar(50)
declare c1 cursor for
select name from master..sysdatabases where name not in ('master','tempdb','model','msdb','SharePoint_AdminContent_2edfaf57-7f7a-43d9-9171-e63b1d726f91')
order by name
select @backupdate={fn curdate()}
--Before running this procedure please register your type of backup here e.g DISK,TAPE,NETWORKDISK
-- for that you just have to run following command here :
-- USE master
-- EXEC sp_addumpdevice 'disk','mydiskdump','C:\Dump'
begin
open c1
fetch c1 into @db
while (@@fetch_status=0)
begin
-- FOLLOWING IS THE USE OF DYNAMIC SQL WHERE WE ARE GETTING DATABASE_NAME AND LOGFILE NAME
set @sql='select @dbname=name from '+@db+'.dbo.sysfiles where fileid=1'
execute sp_executesql @sql,N'@dbname varchar(300) OUT,@db varchar(300)',@dbname OUT,@db
select * from master.dbo.sysfiles
--######################################################################################################
--NOW BY FOLLOWING CODE WE ARE EXECUTING DATABASE COMMAND DYNAMICALLY
-- BY JUST PROVIDING DB NAME
-- Following command will truncate the Log file as well
set @sql='USE '+@db+';'
set @sql=@sql+'BACKUP DATABASE '+@db+''
set @sql=@sql+' TO DISK=''C:\DUMP\'+@db+'_'+@backupdate+''' WITH FORMAT ;'
PRINT @sql
--######################################################################################################
execute sp_executesql @sql,N'@dbname varchar(300)',@dbname
fetch c1 into @db
end
end
close c1
deallocate c1

now.................lol
you are done doing............Cheers

Parth

PS: I am new to SQL, please send me your feedback for better of improvement of this procedures

Truncating logfile Dynamically using SP

Hi,
some days back, it happens to 1 of my clients datacenter that suddenly they were unable to insert the data in the database, we tried so many thing like checking for database connectivity, or any other process is going on....etc etc...

suddenly we found out that there is tremendous amout of data is there in LOG file so, for temporary purpose we have take a back ( of course we first stop the process and instances.....as it is recommended) then we create a new Log file for that database

Now the problem was the data flow was so much that we have to do something that will automatically delete the log file so we try building a procedure which will run as per the schedule and deletes the log file and getting back to it's normal size.......

Below is the code for that:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[proc_trunclogfiles] Script Date: 03/09/2008 13:55:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[proc_trunclogfiles] as
-- THIS PROCEDURE IS FOR MAINTANANCE PURPOSE FOR TRUNCATIONG LOG FILES
--CREATED BY PARTH ON 3RD MARCH 2008
declare
@db varchar(300),
@logname varchar(300),
@sql nvarchar(1000)
declare c1 cursor for
select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')
order by name
begin
open c1
fetch c1 into @db
while (@@fetch_status=0)
begin
-- FOLLOWING IS THE USE OF DYNAMIC SQL WHERE WE ARE GETTING DATABASE_NAME AND LOGFILE NAME
set @sql='select @logname=name from '+@db+'.dbo.sysfiles where fileid=2'
execute sp_executesql @sql,N'@logname varchar(300) OUT,@db varchar(300)',@logname OUT,@db
--######################################################################################################
--NOW BY FOLLOWING CODE WE ARE EXECUTING THE TRUNCATE LOG COMMAND DYNAMICALLY
-- BY JUST PROVIDING DB NAME AND LOGNAME
-- Following command will truncate the Log file as well
--set @sql='USE '+@db+';'
--set @sql=@sql+'BACKUP LOG '+@db+' with truncate_only ;'
--set @sql=@sql+' DBCC SHRINKFILE('+@logname+');'
--######################################################################################################
-- FOLLOWING IS THE ANOTHER METHOD TO DO IT...!
set @sql='USE '+@db+';'
set @sql=@sql+' DUMP TRANSACTION '+@db+' WITH NO_LOG;'

execute sp_executesql @sql,N'@logname varchar(300)',@logname
fetch c1 into @db
end
end
close c1
deallocate c1

Hope this will help some one.

Thanks
Parth
PS: as i am new to SQL, please let me know if there is any problem with code or i have done something wrong

Function for calculating Working hours

Hi,
If you want to calculate simple working hours, i think this might help some,
Below function will return the working hour for the days between

CREATE FUNCTION [dbo].[WorkHours] ( @str_start DATETIME, @str_end DATETIME) RETURNS INT
AS
BEGIN
RETURN
(SELECT ((total_days / 7) * 5 + total_days % 7 -
CASE WHEN 6 BETWEEN start_weekday AND end_weekday
THEN 1 ELSE 0 END -
CASE WHEN 7 BETWEEN start_weekday AND end_weekday
THEN 1 ELSE 0 END) * 8 FROM (SELECT total_days, start_weekday,
start_weekday + total_days % 7 - 1
FROM (SELECT DATEDIFF(day, @start_date, @str_end) + 1,
DATEPART(WEEKDAY, @str_start + @@DATEFIRST - 1) ) AS T
(total_days, start_weekday) ) AS D
(total_days, start_weekday, end_weekday) );
END

Thought this might help someone who is struggling with the calculating the working hours, yes this may not serve the best result in customized situation given by clients but it happens some time that you just need to calculate something very straight.

Thanks
Parth

DB Mails to Lotus Notes

some days back 1 of our client requested us to setup a functionality where in their organization when ever person will assigned to task, he /she will get the task here the twist was they need the functionality in Lotus notes and they need it in simple table where each and every details should appear like what is his/her task name ,start date,end date ,% complete etc....

so we decided to use database for this assignment, as our task is to sent it to lotus notes first we found out that lotus Note is accepting the HTML or not and we found out that it is taking HTML format and hence we were able to do it....that way..............
otherwise we can also use XML and transform it using XSLT but here it is of no need....

Hope this will help to someone
Thanks
Parth
PS: I am new to SQL, please let me know if i have done any mistake in it.