/*============================================================================
  File:     Backup2000Restore2005.sql

  Summary:  This script connects to a SQL 2000 server, backups up the database
			defined by the DB parameter and then connects to the 2005 server
			to restore. On the restore it looks to see what the correct
			directory structure is and then changes the file names appropriately.

  Date:     March 15, 2005

  SQL Server Version: 9.00.1090 (IDW13)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

--:SETVAR DB pubs
--:SETVAR SourceSERVER RoadRunner64
--:SETVAR DestSERVER RoadRunner64\SQLDev01
: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 $(SourceSERVER) -- SQL Server 2000 Instance

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

:CONNECT $(DestSERVER) -- IDW13 (February 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