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
      

query sql error log

Grab the information of sql error log in a table and query


--gather all the error in a temp table and query the temp table to find the specific error by text search or date range

CREATE TABLE #SQLErrorLog



(
 LogDate DATETIME,

ProcessInfo VARCHAR(50),

Text VARCHAR(1500)



)
 GO
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 0



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 1



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 2



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 3



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 4



go
 INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 5



go


 SELECT * FROM #SQLErrorLog

WHERE LogDate >= CONVERT(datetime,'06/08/2015') AND LogDate < CONVERT(datetime,'06/20/2015')

and Text LIKE '%failed%' OR Text LIKE 'Error:%'

ORDER BY LogDate DESC

DROP TABLE #SQLErrorLog

script: How to find logical and physical file names of the database

SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf

Error: Could not continue scan with NOLOCK due to SQL Server data movement

Ran  the following

DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY
I got table error something like below

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown),
page ID (1:219356) contains an incorrect page ID in its page header.
The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 144115188075921408

So I ran the following

USE [master]
GO
ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DATABASE]
GO
DBCC CHECKTABLE(MY-Badtable, 'REPAIR_REBUILD')
GO
ALTER DATABASE [MY_DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Then  ran the following again
DBCC CHECKDB(MY_DATABASE)


This worked, no errors!

Find database lock

IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID('Model'))

PRINT 'Model Database being used by some other session'

ELSE

PRINT 'Model Database not used by other session'


---to see what is locking up the database
SELECT request_session_id FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID('Model')

Logging the performance counter

The following script creates a table PerfMondata in a SQLMonitor database and logs the counter in the interval of 30 seconds



use [SQLMonitor]



GO
 SET NOCOUNT ON ;

DECLARE @PerfCounters TABLE (

[Counter] nvarchar(770),

[CounterType] int,

[FirstValue] decimal (38,2),

[FirstDateTime] datetime,

[SecondValue] decimal(38,2),

[SecondDateTime] datetime ,

[ValueDiff] AS ([SecondValue] - [FirstValue]) ,

[timeDiff] AS (DateDiff(ss,[FirstDateTime],[SecondDateTime])),

[CounterValue] decimal(38,2)

) ;




 INSERT INTO @PerfCounters (

[Counter],

[CounterType],

[FirstValue],


[FirstDateTime]
)
 SELECT RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name]),[cntr_type],[cntr_value],GETDATE()

from sys.dm_os_performance_counters

WHERE [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',

'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',

'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',

'Target Server Memory (KB)', 'Latch Waits/sec' ,'Buffer cache hit ratio'



)
 order by [OBJECT_NAME] + N' : ' + [counter_name]+ N':' +RTRIM([instance_name])



  Waitfor delay '00:00:30';


Update @PerfCounters

SET [SecondValue]= [cntr_value],

[SecondDateTime] = GETDATE()

from sys.dm_os_performance_counters

where [Counter] = RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name])

AND [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',

'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',

'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',

'Target Server Memory (KB)', 'Latch Waits/sec','Buffer cache hit ratio' )


 
--for per sec counters

Update @PerfCounters

SET CounterValue = ([SecondValue]-[FirstValue])/(DateDiff(ss,[FirstDateTime],[SecondDateTime]))

where [counterType] = 272696576;


--for non per sec counters

 Update @PerfCounters

SET CounterValue = [SecondValue]

where [counterType] <> 272696576;

select * from @PerfCounters



INSERT INTO PerfMondata (perfCounter,Value,CAptureDate)

SELECT [counter],[CounterValue],[SecondDateTime] from @PerfCounters



--select * from PerfMondata




--following stored procedure takes in a counter as the parameter and displays the report on a specific counter

use [SQLMonitor]



GO
 

if OBJECTPROPERTY(OBJECT_ID('proc_sqlAdmin_PerfMonReport'),'IsProcedure')= 1

DROP PROCEDURE proc_sqlAdmin_PerfMonReport

GO





CREATE PROCEDURE dbo.proc_sqlAdmin_PerfMonReport

(



@Counter nvarchar(128)= '%'

)



AS



BEGIN ;

SELECT [Perfcounter],value,captureDate

from perfMondata

WHERE [Perfcounter] like @Counter

ORDER BY [Perfcounter],[captureDate]



END ;



/*



exec dbo.proc_sqlAdmin_PerfMonReport '%Page life expectancy%'

exec dbo.proc_sqlAdmin_PerfMonReport '%Batch Requests/sec%'

exec dbo.proc_sqlAdmin_PerfMonReport '%Page Writes/sec%'

*/