Pages

Ruchi Tech

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 returnsSet @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



No comments:

Post a Comment