Create and restore database using backup files from the network

I had a project where I had to create and restore the database from the backup files in a network.
I achieved it by following these steps.

• I moved all the backup files from the network to the local server in a backup  folder.
• Created a table BackupFileList to store the current database name and the backup file names using the command shell script
• Executed a dynamic SQL script to create all the databases.
• Executed the dynamic script to restore all the databases I made use of the created table to build a dynamic query to find the database name and the backup file.

( Note I had to use the MOVE command in the restore script since the physical path of the data file and the log file were different in the local server than the other server from where the backup was done.)


First of all need to make sure that the 'xp_cmdshell' is enabled.


EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

I ran  the following query to see the  physical path of the back up sets that was moved over from the network.

restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 1
go

[In SQL Server 2008 and later, you need to have CREATE DATABASE permission to get this information

 

 
If there are multiple backup sets on the backup device, specify the backup set number using the “WITH FILE = n” option.

 To specify the second backup set on the device run this code:

restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 2
go ]


Script 1 :
Important!!!!
BEFORE RUNNING MAKE SURE THAT THE SERVICE ACCOUNT HAS PERMISSION IN THE PATH DIR.

--To create a backupfiles list and populate a table in the work db

--delete from HCFS_WORKDB..DBBackUpFilesList

CREATE TABLE #DirectoryList (
  line  VARCHAR(512))
DECLARE @f  table (Fname varchar(100))
DECLARE @Path varchar(256) = 'dir V:\SQL\Backup\Daily\'
DECLARE @Command varchar(1024) =  @Path+  '/b /a-d  /s *.bak ' --' /s /b /o:gn' --/a:d /s /b | sort'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL

delete from #DirectoryList where ( line not like ('%2015_06_24%')) --keep the recent backup file

delete from #DirectoryList where line like '%system%'
delete from #DirectoryList where line like '%\Diffferntial%'
delete from #DirectoryList where line not like '%\client_%'
--select line from #DirectoryList
insert into  @f
select SUBSTRING(line,charindex('\client_',line,1)+1,512 ) from #DirectoryList
--capture the database name and the filename in the table
insert into DB_WORKDB..DBBackUpFilesList (DBname,BackupfileName)
select rtrim(left(SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ),charindex('_backup',SUBSTRING(fname ,charindex('\client_',fname,1)+2,100 ),1))),SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ) from @f
--select substring(SUBSTRING(line,charindex('\client_',line,1)+1,512 ),charindex('\',line,1)+1,512)  from #DirectoryList
DROP TABLE #DirectoryList
GO

 


-------------------------------------------------------------------------------

Script 2 : Create multiple databases using a cursor


declare @curClientCode as cursor ,@db varchar(100),
@sqlCreateDb nvarchar(max),
@dbName varchar(100)
SET @curClientCode = CURSOR FOR
--crete db for arm clients too
select name from sys.databases
 where name like 'client_%'

OPEN   @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = cast(@db as varchar)
SET @sqlCreateDb = 'USE  master' + CHAR(13) +
--'DROP DATABASE  ' +  @dbName + CHAR(13) +
' CREATE DATABASE ' + @dbName +  CHAR(13) +
' ON (NAME = ' + @dbName + '_data, FILENAME = ''E:\SQL\Data\' + @dbName +'.mdf'', Size = 100MB,MAXSIZE=unlimited, FILEGROWTH = 10)' + char(13) +
' LOG ON (NAME = ' + @dbName +  '_log, FILENAME = ''L:\SQL\Data\' + @dbName +'.ldf'', Size = 5MB,MAXSIZE=500MB, FILEGROWTH = 10); '
   

print @sqlCreateDb
exec sp_executesql @sqlCreateDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode


---------------------------------------------------------
Script 3 : Restore the database with MOVE command from the file
The backup files name were found from the table in the DB_workdb that was used earlier to capture the database name and the backup files


 use master
Go
declare @curClientCode as cursor ,@db varchar(10),
@sqlRestoreDb nvarchar(max),
@dbName varchar(20),
@backupFile varchar(100)
SET @curClientCode = CURSOR FOR
select name from sys.databases
 where name like 'client_%'
OPEN   @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = @db
SELECT  @backupFile = BackupFileName from HCFS_WORKDB..dbBackUpFilesList where dbname = @dbName
SET @sqlRestoreDb = 'ALTER DATABASE ' +  @dbName + char(13) +
' SET SINGLE_USER WITH  ROLLBACK IMMEDIATE ' + char(13)  + char(13)
SET @sqlRestoreDb  = @sqlRestoreDb + ' RESTORE DATABASE ' +  @dbName + char(13) +
' FROM DISK = ''V:\SQL\Backup\Daily\' + @dbName + '\'+  + @backupFile  + ''''+ char(13)+
 ' WITH  MOVE N''' + @dbName + ''' TO  N''E:\SQL\Data\' + @dbName + '.mdf'','  + char(13)+
 ' MOVE N''' + @dbName + '_log'' TO  N''L:\SQL\Data\' + @dbName + '.ldf'','  + char(13)+
 ' REPLACE, STATS = 10 ' +  char(13)
 
SET @sqlRestoreDb  = @sqlRestoreDb  + 'ALTER DATABASE ' +  @dbName + char(13) +
' SET MULTI_USER WITH  ROLLBACK IMMEDIATE ' + char(13)
 

print @sqlRestoreDb
exec sp_executesql @sqlRestoreDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode

GO
      

Dynamic sql : Getting result of dynamic SQL into a variable

DECLARE @sqlCommand nvarchar(1000)
DECLARE @city varchar(75)
declare @counts int
SET @city = 'New York'
SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75),@cnt int OUTPUT', @city = @city, @cnt=@counts OUTPUT select @counts as Counts

Dynamic sql: executing stored procedure using a sp_executesql

I had a situation where the logic on finding the duplicate records( based on the account number match) was different on each client. I built a logic where each client had it's own stored procedure called proc_check_dup_logic and the stored procedure name used by each client was stored in a table in a central admin database. I had a wrapper stored procedure in the admin database that would find the duplicate logic stored procedure name from the table and execute that using sp_executesql.

I try to stay away from dynamic sql but this was pretty fun writing and I unerstood the parameters scope as well.



@procname is a storedrocedure variable where the name is assigned and it's a storeprocedure with an OUTPUT value.

SELECT @procname = procedurename from table1 WHERE clientId = 10

BEGIN
--call special dup check logic

DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

--the follwing are the parameters in this scope

DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1),@paramINPtno int
DECLARE @parmRET1 int
--assigning the values to be passed to the storeprocedure on sp_executesql call
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @paramINPtno = @intPtno

SET @sqlStr = N'Exec ' + @procName + ' @intPtno,@strAccount,@intHospital,@patType, @outDupCheck OUTPUT'

--the following parameters name and datatype should be exactly the same as the parameters in the called storedprocedure (e.g. @procname in this case)

SET @ParmDefinition=N'@IntPtno int, @strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';

EXECUTE sp_executesql @sqlStr, @ParmDefinition, @IntPtno = @paramINPtno, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1 OUTPUT
Select @outDupCheck = @parmRET1

END

Fix : truncation of dynamic query result

declare @script nvarchar(max) = ''
SELECT @script += 'grant execute on [' + r.ROUTINE_NAME + '] to [GRP_eApps_allUsers]' + char(13) + char(10)
from INFORMATION_SCHEMA.ROUTINES as r
where specific_name like 'proc_eapps%'
order by specific_name
--print @script
DECLARE @stmp varchar(max)
SET @stmp = ISNULL(@script,'')
WHILE @stmp <> ''
BEGIN
PRINT LEFT(@stmp, 8000)
SET @stmp = substring(@stmp, 8001, len(@stmp))
END

Executing stored procedure with input parameters using sp_executesql

Stored procedure name proc1
Parameters are @param1, @param2, @param3
Values are 1,2,3

declare @SqlString nvarchar(2000)
declare @ParamDef nvarchar(2000)
set @SqlString = N'exec proc1 @param1, @param2, @param3'
set @ParamDef = N'@param1 bit, @param2 bit, @param3 bit'

EXECUTE sp_executesql @SqlString ,@ParamDef, @param1 = 0, @param2 = 1, @param3 = 1

Giving execute permission to all stored procedures through a databaserole

I had a situation where I had to give a user( AD group) execution permission to all the sps.

- Created a role called db_executor data user role

USE pubs
GO

exec sp_addrole  'db_executor'

GO

--added memebers to the role

USE pubs
GO

exec sp_addrolemember 'db_executor', 'GRP_ProdSupport'

GO

--gave permission to execute the stored procedures to the role.

USE pubs
GO

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
        -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO

(code source : http://www.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/ )