/****** Object:  StoredProcedure [dbo].[AUTO_GEN_RESTORE_SCRIPT]    Script Date: 11/01/2010 15:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON 
GO
SET ANSI_WARNINGS ON
GO
-- =============================================
-- Author:		Jason Wong
-- Create date: 10-28-2010, 1-5-2011
-- Description:	DBA script, add DBTarget parameter
-- EXEC DBASP_AUTO_GEN_RESTORE_SCRIPT '[SQL001]', 'Workflow'
-- EXEC AUTO_GEN_RESTORE_SCRIPT '[SQL003\DW]
-- =============================================
ALTER PROCEDURE [dbo].[DBASP_AUTO_GEN_RESTORE_SCRIPT]
   @P1HostName sysname, @DBTarget sysname = NULL
WITH Encryption 
AS
BEGIN
SET NOCOUNT ON 
DECLARE @databaseName sysname, @rc int
DECLARE @sqlcommand nvarchar(4000)
DECLARE @backupStartDate datetime  
DECLARE @backup_set_id_start INT  
DECLARE @backup_set_id_diff INT  
DECLARE @backup_set_id_end INT 

DECLARE @ParmDefinition nvarchar(500)
DECLARE @backupsetid_start int
DECLARE @backupsetid_diff int
DECLARE @backupsetid_end int

CREATE TABLE #TmpCommands 
(ID INT IDENTITY(1,1), 
Cmd VARCHAR(8000) ) 
CREATE TABLE #db 
( name NVARCHAR(128) )

IF len(@DBTarget) > 0
BEGIN
 SELECT @rc = 1, @databaseName = @DBTarget
END
ELSE
BEGIN
BEGIN try
 SET @sqlcommand = 'INSERT into #db(name) SELECT name FROM '+ @P1HostName +'.master.sys.databases WHERE name NOT IN ( ''tempdb'', ''pubs'',''northwind'') 
 AND (state) = 0'
 exec(@sqlcommand)
END try
Begin Catch
  SET @sqlcommand = 'INSERT into #db(name) SELECT name FROM '+ @P1HostName +'.master.dbo.sysdatabases WHERE name NOT IN ( ''tempdb'', ''pubs'',''northwind'') 
   AND (status & 32) =0 -- Do not include loading 
   AND (status & 64) =0 -- Do not include loading 
   AND (status & 128) =0 -- Do not include recovering 
   AND (status & 256) =0 -- Do not include not recovered 
   AND (status & 512) =0 -- Do not include offline 
   AND (status & 32768) =0 -- Do not include emergency 
   AND (status & 1073741824) =0 -- Do not include cleanly shutdown '
  exec(@sqlcommand)
END catch
  
 SELECT @rc = 1, @databaseName = MIN(name) FROM #db
 --SET @databaseName = 'Dev_Acorn_Staging'
END

WHILE @rc <> 0
BEGIN

INSERT INTO #TmpCommands(Cmd) 
VALUES ('----------------Script to Restore the ' + @databaseName + ' Database--------------') 

SET @sqlcommand = 'SELECT @backup_set_id_start=MAX(backup_set_id) FROM '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupset WHERE database_name = @databaseName AND type = ''D'''
SET @ParmDefinition = N'@P1HostName varchar(100), @databaseName sysname, @backup_set_id_start int OUTPUT';

EXEC sp_executesql @sqlcommand, @ParmDefinition, @P1HostName=@P1HostName, @databaseName=@databaseName, @backup_set_id_start=@backupsetid_start out
SET @backup_set_id_start = @backupsetid_start
--SET @sqlcommand = ''

SET @sqlcommand = 'SELECT @backup_set_id_diff=MAX(backup_set_id) FROM '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupset WHERE database_name = @databaseName AND type = ''I'''
SET @sqlcommand = @sqlcommand  + ' AND backup_set_id > @backup_set_id_start'
SET @ParmDefinition = N'@P1HostName varchar(100), @databaseName sysname, @backup_set_id_start int, @backup_set_id_diff int OUTPUT';

--exec( @sqlcommand)
EXEC sp_executesql @sqlcommand, @ParmDefinition, @P1HostName=@P1HostName, @databaseName=@databaseName, @backup_set_id_start=@backup_set_id_start, @backup_set_id_diff=@backupsetid_diff out
SET @backup_set_id_diff = @backupsetid_diff


IF @backup_set_id_diff IS NULL SET @backup_set_id_diff = @backup_set_id_start

SET @sqlcommand = 'SELECT @backup_set_id_end = MIN(backup_set_id) FROM '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupset WHERE database_name = @databaseName AND type = ''D'''
SET @sqlcommand = @sqlcommand  + ' AND backup_set_id > @backup_set_id_start'
SET @ParmDefinition = N'@P1HostName varchar(100), @databaseName sysname, @backup_set_id_start int, @backup_set_id_end int OUTPUT';

--exec( @sqlcommand)
EXEC sp_executesql @sqlcommand, @ParmDefinition, @P1HostName=@P1HostName, @databaseName=@databaseName, @backup_set_id_start=@backup_set_id_start, @backup_set_id_end=@backupsetid_end out
SET @backup_set_id_end = @backupsetid_end

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 2147483647 

SET @sqlcommand = 
'
INSERT INTO #TmpCommands(Cmd) 
SELECT ''RESTORE DATABASE '' + @databaseName + '' FROM DISK = '''''' + mf.physical_device_name + '''''' WITH NORECOVERY, REPLACE '' 
FROM '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupset b, '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupmediafamily mf  '
SET @sqlcommand = @sqlcommand  + 'WHERE b.media_set_id = mf.media_set_id  
 AND b.database_name = @databaseName  
 AND b.backup_set_id = @backup_set_id_start 
'
SET @ParmDefinition = N'@P1HostName varchar(100), @databaseName sysname, @backup_set_id_start int';
EXEC sp_executesql @sqlcommand, @ParmDefinition, @P1HostName=@P1HostName, @databaseName=@databaseName, @backup_set_id_start=@backup_set_id_start

if (@backup_set_id_diff <> @backup_set_id_start)
BEGIN
SET @sqlcommand = 
'
INSERT INTO #TmpCommands(Cmd) 
SELECT ''RESTORE DATABASE '' + @databaseName + '' FROM DISK = '''''' + mf.physical_device_name + '''''' WITH NORECOVERY '' 
FROM '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupset b,  '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupmediafamily mf  '
SET @sqlcommand = @sqlcommand  + ' WHERE b.media_set_id = mf.media_set_id  
 AND b.database_name = @databaseName  
 AND b.backup_set_id = @backup_set_id_diff  
'
SET @ParmDefinition = N'@P1HostName varchar(100), @databaseName sysname, @backup_set_id_diff int';
EXEC sp_executesql @sqlcommand, @ParmDefinition, @P1HostName=@P1HostName, @databaseName=@databaseName, @backup_set_id_diff=@backup_set_id_diff
END

SET @sqlcommand = 
'
INSERT INTO #TmpCommands(Cmd)
SELECT ''RESTORE LOG '' + @databaseName + '' FROM DISK = '''''' + mf.physical_device_name + '''''' WITH FILE = '' + CAST(position AS VARCHAR(10)) + '', NORECOVERY ''
FROM '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupset b,  '
SET @sqlcommand = @sqlcommand  + @P1HostName
SET @sqlcommand = @sqlcommand  + '.msdb.dbo.backupmediafamily mf  '
SET @sqlcommand = @sqlcommand  + ' WHERE b.media_set_id = mf.media_set_id  
 AND b.database_name = @databaseName  
 AND b.backup_set_id >= @backup_set_id_diff AND b.backup_set_id < @backup_set_id_end  
 AND b.type = ''L''  
'
SET @ParmDefinition = N'@P1HostName varchar(100), @databaseName sysname, @backup_set_id_diff int, @backup_set_id_end int';
EXEC sp_executesql @sqlcommand, @ParmDefinition, @P1HostName=@P1HostName, @databaseName=@databaseName, @backup_set_id_diff=@backup_set_id_diff, @backup_set_id_end=@backup_set_id_end

SET @sqlcommand = 
'
INSERT INTO #TmpCommands(Cmd)
SELECT ''RESTORE DATABASE '' + @databaseName + '' WITH RECOVERY ''
'

SET @ParmDefinition = N'@databaseName sysname';
EXEC sp_executesql @sqlcommand, @ParmDefinition, @databaseName=@databaseName

    SELECT TOP 1 @databaseName = name FROM #db WHERE name > @databaseName
    ORDER BY name

    SET @rc = @@ROWCOUNT
END 


DECLARE @PrintCommand VARCHAR(8000) 

DECLARE Print_cursor CURSOR 
FOR  
 SELECT Cmd FROM #TmpCommands 
 ORDER BY ID 
  
 OPEN Print_cursor 
  
 FETCH NEXT FROM Print_cursor INTO @PrintCommand 
 WHILE (@@FETCH_STATUS <> -1) 
 BEGIN 
 IF (@@FETCH_STATUS <> -2) 
 BEGIN  
 PRINT @PrintCommand + '--'
 END  
 FETCH NEXT FROM Print_cursor INTO @PrintCommand  
 END 
 CLOSE Print_cursor 
 DEALLOCATE Print_cursor 
  
DROP TABLE #TmpCommands 

END