:SETVAR DB credit
:SETVAR DELAY 00:00:00
 
:ON ERROR EXIT
go

IF '$(DB)' IN ('master', 'model', 'msdb', 'ReportServer', 'distribution', 'tempdb')
BEGIN
	RAISERROR ('System Databases are NOT supported. Script Terminated.', 16, -1)
END
go

:CONNECT RoadRunner64 -- this is a SQL Server 2000 Server

BACKUP DATABASE $(DB)
	TO DISK = 'c:\SQLTemp\$(DB).bak'
--	TO DISK = '\\roadrunner64\e\SQLDemo\$(DB).bak'
-- 	TO DISK = '\\Server\d$\SQLDemo\$(DB).bak' -- could be a UNC name
	WITH INIT
PRINT ' '
go

:CONNECT RoadRunner64\SQLDev01 -- Yukon IDW11 (December CTP)

SET NOCOUNT ON

IF DATABASEPROPERTYEX('$(DB)', 'COLLATION') IS NOT NULL
	DROP DATABASE [$(DB)]

WAITFOR DELAY '$(DELAY)'	

DECLARE @InstanceName	sql_variant,
				@InstanceDir		sql_variant,
				@SQLDataRoot	nvarchar(512),
				@ExecStr				nvarchar(max)

SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')
EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 
            'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 
            @InstanceName, @InstanceDir	OUTPUT
SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread ''HKEY_LOCAL_MACHINE'', ''SOFTWARE\Microsoft\Microsoft SQL Server\' 
			+ convert(varchar, @InstanceDir) + '\Setup'', ''SQLDataRoot'', @SQLDataRoot	OUTPUT'
EXEC master.dbo.sp_executesql @ExecStr, N'@SQLDataRoot nvarchar(512) OUTPUT', @SQLDataRoot OUTPUT
IF @SQLDataRoot IS NULL
BEGIN
	RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END

CREATE TABLE #BackupFileList
( LogicalName	sysname	NULL
	, PhysicalName	sysname	NULL
	, [Type]	char(1)
	, FileGroupName	sysname NULL
	, Size	bigint
	, MaxSize	bigint
	, FileId	smallint
	, CreateLSN	numeric(25,0)
	, DropLSN	numeric(25,0)
	, UniqueId uniqueidentifier
	, ReadOnlyLSN	numeric(25,0)
	, ReadWriteLSN	numeric(25,0)
	, BackupSizeInBytes	bigint
	, SourceBlockSize	bigint
	, FileGroupId		smallint
	, LogGroupGUID	uniqueidentifier
	, DifferentialBaseLSN	numeric(25,0)
	, DifferentialBaseGUID	uniqueidentifier
	, IsReadOnly	bit
	, IsPresent	bit
)

INSERT #BackupFileList
	EXEC('LOAD FILELISTONLY FROM DISK = ''c:\SQLTemp\$(DB).bak''')

UPDATE #BackupFileList
	SET PhysicalName = @SQLDataRoot + N'\Data\' + REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

DECLARE @LogicalName	sysname
	, @PhysicalName	sysname

DECLARE FileListCursor CURSOR FAST_FORWARD FOR 
	SELECT LogicalName, PhysicalName
	FROM #BackupFileList

OPEN FileListCursor

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

SELECT @ExecStr = N'RESTORE DATABASE $(DB)' +
				   N' FROM DISK = ''c:\SQLTemp\$(DB).bak''' +
				   N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''
	
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
	SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + ''''
	FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

-- SELECT @ExecStr

EXEC (@ExecStr)

DEALLOCATE FileListCursor
go