Backup script for all stored procedures for a particular Database

Hi everyone,

I was not able to post anything in the last week as I did not got time.This post is regarding

a very useful script which will give you the backup of all the stored procedures,views and functions

at the specific path on your local system.Often we forget to take backup of these objects in our database

and this script can even become very handy if you put this in a job and schedule it accordingly.

Following is the script which will create an SP and when executed by passing the destination path

and DB name it will take backup of all these objects.By default the destination path is kept as

‘C:\Backup\StoredProcedure\’.

 

GO
IF OBJECT_ID(‘USP_BackupAllStoredProcedures’) IS NOT NULL
DROP PROC USP_BackupAllStoredProcedures
GO
 
/*==========================================================================================   
Name:  Export all stored procedures for all user databases to particular location   
Author:  Mohammad Shahed   
Parameters:  
@ExportDataPath specifies location to where backup of sp needs to store.  eg. ”C:\Backup\StoredProcedure\”  
Returns:     
Description: It creates main folder in @ExportDataPath which contains current
   date and time, in that folder it creates  folder for the database and  
creates stored procedure related to database.  
==========================================================================================*/   
   
CREATE PROCEDURE [dbo].[USP_BackupAllStoredProcedures]
    (
      @ExportDataPath NVARCHAR(1000) ,
      @DBName    varchar(max)
    )
AS
    BEGIN   
        SET QUOTED_IDENTIFIER OFF 
        SET NOCOUNT ON 
        BEGIN TRY 

            DECLARE @ExportPath AS NVARCHAR(1000) 
            SET @ExportPath = @ExportDataPath 
            IF ( ISNULL(@ExportPath, ”) = ”)
                BEGIN 
                    SET @ExportPath = ‘C:\Backup\StoredProcedure\’ 
                END 
            SET @ExportPath += ( SELECT CONVERT(VARCHAR(100), GETDATE(), 102)+ ‘_’+ REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108),’:’, ‘.’)) + ‘\’ 
            — variables for first while loop 
            DECLARE @DatabaseName AS NVARCHAR(1000) 
            — variables for second while loop 
            DECLARE @ExportFilePath NVARCHAR(1000)       
            DECLARE @ServerName NVARCHAR(100)       
            SELECT  @ServerName = CONVERT(SYSNAME, SERVERPROPERTY(N’servername’))    
            DECLARE @GetProcedureNames NVARCHAR(MAX) 
      
          
            –IF OBJECT_ID(‘tempdb..#Databases’) IS NOT NULL
            —    DROP TABLE #Databases  
            –SELECT  name ,
            —        ROW_NUMBER() OVER ( ORDER BY name ) AS RowNum
            –INTO    #Databases
            –FROM    sys.databases
            –WHERE   database_id > 4 
            –DECLARE @DatabaseCurrentPosition INT = 1 
            –WHILE @DatabaseCurrentPosition <= ( SELECT  COUNT(1)
            —                                    FROM    #Databases
            —                                  )
            —    BEGIN 
                    SELECT  @DatabaseName = @DBName
                    
                    SET @ExportFilePath = @ExportPath + @DatabaseName      
                    EXECUTE master.dbo.xp_create_subdir @ExportFilePath  
                   
                    IF OBJECT_ID(‘tempdb..#Procedures’) IS NOT NULL
                        DROP TABLE #Procedures  
                    CREATE TABLE #Procedures
                        (
                          RoutineName NVARCHAR(MAX) ,
                          RowNum INT ,
                          ObjectID INT
                        ) 
                   
                      
                     SET @GetProcedureNames = ‘ INSERT INTO #Procedures
                         SELECT QUOTENAME(s.[name]) + ”.” + QUOTENAME(o.[name]) AS RoutineName 
                     ,ROW_NUMBER() OVER ( ORDER BY s.[name],o.[name]) AS RowNum,sm.object_id as ObjectID
                     FROM ‘
                        + @DatabaseName + ‘.sys.objects AS o  INNER JOIN ‘
                        + @DatabaseName
                        + ‘.sys.schemas AS s ON s.[schema_id] = o.[schema_id] INNER JOIN ‘
                        + @DatabaseName
                        + ‘.sys.sql_modules sm ON o.[object_id]=sm.[object_id]           
                        WHERE type IN (”p”,”v”,”fn”) AND o.is_ms_shipped = 0′ 
                       
                   
                    EXEC(@GetProcedureNames)
                
                    IF ( ( SELECT   COUNT(1) FROM     #Procedures ) > =1 )
                      
                        BEGIN
                  
                            DECLARE @ProcedureCurrentPosition INT = 1 
                            WHILE @ProcedureCurrentPosition <= ( SELECT
                                                              COUNT(1)
                                                              FROM
                                                              #Procedures
                                                              )
                                BEGIN 
                                    DECLARE @ProcedureContent NVARCHAR(MAX)    
                                    DECLARE @ProcedureName NVARCHAR(MAX)  
                                    DECLARE @ObjectID INT
                                   
                                    Select  @ProcedureName = RoutineName ,
                                            @ObjectID = ObjectID
                                    FROM    #Procedures
                                    WHERE   RowNum = @ProcedureCurrentPosition
                                    SET @ExportFilePath = @ExportPath + @DatabaseName + ‘\’ + @ProcedureName+ ‘.sql’ 
                                    DECLARE @Que NVARCHAR(MAX)= ‘Select Definition from ‘
                                        + @dataBaseName
                                        + ‘.sys.sql_modules sm where sm.[object_id]=’
                                        + CAST (@objectID AS NVARCHAR)
                         
                                    DECLARE @sql NVARCHAR(4000)       
                                    SELECT  @sql = ‘bcp ‘ + ‘”‘ + @Que +'”‘
                                            + ‘ queryout ‘ + ‘”‘+@ExportFilePath+'”‘
                                            + ‘ -c -t -T -S ‘
                                            + @ServerName
                                            print @sql
                                    EXEC xp_cmdshell @sql  
                                    SET @ProcedureCurrentPosition = @ProcedureCurrentPosition+ 1 
                                END   
                        END     
                    –SET @DatabaseCurrentPosition = @DatabaseCurrentPosition
                    —    + 1 
           —     END    
        END TRY       
        BEGIN CATCH       
   — Raise an error with the details of the exception  
            DECLARE @ErrMsg NVARCHAR(4000) ,
                @ErrSeverity INT       
            SELECT  @ErrMsg = ERROR_MESSAGE() ,
                    @ErrSeverity = ERROR_SEVERITY()       
            RAISERROR(@ErrMsg, @ErrSeverity,1)       
            RETURN       
        END CATCH ;   
    END

    

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Advertisements

T-SQL Function: Add a space between all characters

“For any input string, return a string that has an additional space between each two characters, even when one of the input characters is itself a space”

DECLARE @string VARCHAR(100)
DECLARE @result VARCHAR(100)
 
SET @String = ‘hello world’;
 
WITH n AS (
SELECT TOP (LEN(@string)) ROW_NUMBER() OVER(ORDER BY [object_id]) x
FROM sys.objects)
 
SELECT @result = CAST(
(SELECT SUBSTRING(@string, x, 1) [text()], ‘ ‘
FROM n
FOR XML PATH(”), TYPE) AS varchar)
 
SELECT @result

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

DML Triggers with example-Part-2

Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example.

If you have read my previous blog on DML triggers then you must have all the pre-requisites required,

1. Instead of Insert Trigger

— Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
INSTEAD OF Insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
SET @audit_action=’Inserted Record — Instead Of Insert Trigger.’;
BEGIN
BEGIN TRAN
SET NOCOUNT ON
if(@emp_sal<=1000)
begin
RAISERROR(‘Cannot Insert where salary < = 1000’,16,1);
ROLLBACK;
end
else
begin
Insert into Employee_Demo (Emp_Name,Emp_Sal)
values (@emp_name,@emp_sal);
Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
COMMIT;
PRINT ‘Record Inserted — Instead Of Insert Trigger.’
end
END

–Now try to insert data in Employee_Demo table
insert into Employee_Demo values (‘Shailu’,1300)
insert into Employee_Demo values (‘Shailu’,900) — It will raise error since we are checking salary >=1000
–Outputs will be

 

image

–now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
–Output will be

 

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace a insert activity on a table.

2.Instead of Update Trigger

— Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
INSTEAD OF Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
set @audit_action = ‘Record Updated — Instead Of Update Trigger.’
BEGIN
BEGIN TRAN
if(@emp_sal<=1000)
begin
RAISERROR(‘Cannot Update where salary < = 1000’,16,1);
ROLLBACK;
end
else
begin
update Employee_Demo set Emp_Sal = @emp_sal where emp_id = @emp_id
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
COMMIT;
end
PRINT ‘Record Updated — Instead Of Update Trigger.’;
END

 

–Now try to upadte data in Employee_Demo table
update Employee_Demo set Emp_Sal = ‘1400’ where emp_id = 6
update Employee_Demo set Emp_Sal = ‘900’ where emp_id = 7
–Output will be

 

image

 

–now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
–Output will be

image.

image

Trigger have inserted the updated record to Employee_Demo_Audit table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.

3. Instead of Delete Trigger

— Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgInsteadDelete ON dbo.Employee_Demo
INSTEAD OF DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
BEGIN TRAN
if(@empsal>1200)
begin
RAISERROR(‘Cannot delete where salary > 1200’,16,1);
ROLLBACK;
end
else
begin
delete from Employee_Demo where Emp_ID=@empid;
COMMIT;
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,’Deleted — Instead Of Delete Trigger.’,getdate());
PRINT ‘Record Deleted — Instead Of Delete Trigger.’
end

–Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where emp_id = 1
DELETE FROM Employee_Demo where emp_id = 4
–Output will be

image

–now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
–Output will be

image

image

Trigger have inserted the deleted record to Employee_Demo_Audit table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.

In this article I try to explain the Instead of Trigger with example. I hope after reading this article your Instead of Trigger concepts will be strong.

I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

DML Triggers with example-Part-1

DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers.

Types of DML Triggers

  1. After Trigger (using FOR/AFTER CLAUSE)

    This trigger fires after SQL Server completes the execution of the action successfully that fired it.

    Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

  2. Instead of Trigger (using INSTEAD OF CLAUSE)

    This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

    Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

 

Example

  1. — First create table Employee_Demo
  2. CREATE TABLE Employee_Demo
  3. (
  4. Emp_ID int identity,
  5. Emp_Name varchar(55),
  6. Emp_Sal decimal (10,2)
  7. )
  8. — Now Insert records
  9. Insert into Employee_Demo values (‘Amit’,1000);
  10. Insert into Employee_Demo values (‘Mohan’,1200);
  11. Insert into Employee_Demo values (‘Avin’,1100);
  12. Insert into Employee_Demo values (‘Manoj’,1300);
  13. Insert into Employee_Demo values (‘Riyaz’,1400);
  14. –Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo
  15. create table Employee_Demo_Audit
  16. (
  17. Emp_ID int,
  18. Emp_Name varchar(55),
  19. Emp_Sal decimal(10,2),
  20. Audit_Action varchar(100),
  21. Audit_Timestamp datetime

)

Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

1. After Insert Trigger

— Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action=’Inserted Record — After Insert Trigger.’;
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT ‘AFTER INSERT trigger fired.’

–Now try to insert data in Employee_Demo table
   insert into Employee_Demo(Emp_Name,Emp_Sal)values (‘Shailesh’,1000);
   –Output will be

  1. –now select data from both the tables to see trigger action
  2. select * from Employee_Demo
  3. select * from Employee_Demo_Audit
  4. –Output will be

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.

2. After Update Trigger

— Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
FOR UPDATE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @audit_action=’Update Record — After Update Trigger.’;
if update (Emp_Sal)
set @audit_action=’Update Record — After Update Trigger.’;
insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT ‘AFTER UPDATE trigger fired.’

–Now try to upadte data in Employee_Demo table
    update Employee_Demo set Emp_Name=’Pawan’ Where Emp_ID =6;
    –Output will be

–now select data from both the tables to see trigger action
    select * from Employee_Demo
    select * from Employee_Demo_Audit
    –Output will be

 

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.

3. After Delete Trigger

— Create trigger on table Employee_Demo for Delete statement
  CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
  FOR DELETE
  AS
  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
  select @empid=d.Emp_ID FROM deleted d;
  select @empname=d.Emp_Name from deleted d;
  select @empsal=d.Emp_Sal from deleted d;
  select @audit_action=’Deleted — After Delete Trigger.’;
  insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
  values (@empid,@empname,@empsal,@audit_action,getdate());
  PRINT ‘AFTER DELETE TRIGGER fired.’
 

    –Now try to delete data in Employee_Demo table
    DELETE FROM Employee_Demo where emp_id = 5
    –Output will be

 

–now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
–Output will be

image

image

Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.

I hope after reading this article your DML triggers concepts will be strong,I will explain the use of Instead of Trigger using Insert, Update, Delete in my next post.

Please post your feedback, question, or comments about this article.