SQL

 

Script for Bulk DataBase BackUp in SQL

If you want backup of multiple databases in bulk then It can be done by using the script:

DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0 

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1 

        -- Not master, tempdb or model
    and db_name(s_mf.database_id) not in ('Master','tempdb','model')
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)    
    set @DBFileName =replace(replace(@DBName,':','_'),'\','_')+'.bak'

    exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''
C:\BulkBackUpFolder\DB_Backups\' + @DBFileName + ''' WITH NOFORMAT, 
INIT,  NAME = N''' + @DBName + '-Full Database Backup'', SKIP, 
NOREWIND, NOUNLOAD,  STATS = 100')
    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Insert Data From One Database Table to Another Database Table – INSERT INTO SELECT 

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table.

INSERT INTO dbName.ScemaName.TableName (column1, column2....)
SELECT column1, column2...
FROM dbName.ScemaName.TableName

Note: Both table column name should be same.

Merging two Stored Procedure into one Stored Procedure

 

I have two stored procedure named "First_Proc" and "Second_Proc" and both procedure use same parameters. Basically, i want to join these 2 stored procedures in another stored procedure and return the result.The code, i use is:

CREATE PROCEDURE new_merge_proc
(
  // Parameters
)

AS
Set NOCOUNT ON
declare @sqlquery nvarchar(4000)    
BEGIN
     Create table [#temptable] (// list all fields your Proc returns;  
Set @sqlquery= '
     INSERT INTO #temptable EXEC First_Proc (// parameters)';
     print @sqlquery
     Exec (@sqlquery)  
set @sqlquery='    
     INSERT INTO #temptable EXEC Second_Proc (// parameters)';
     print @sqlquery
     Exec (@sqlquery)  
SELECT * FROM #tempTable1
     Drop table #tempTable1
   
END
GO 

Restore Database Backup using SQL Script

 

Firstly, take a full backup of your database and it can be restored by using following script:

USE Master

ALTER DATABASE your_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE your_DB FROM DISK = 'D:\your_DB.bak'

WITH REPLACE
ALTER DATABASE your_DB SET MULTI_USER
GO