Restore 2000 DB to 2005

-- RestoreDB_8_9_Single script 9/13/2007
-- 
-- Restore the SQL 2000 (8) database backup to SQL 2005 (9)
--rename single database
--optional append _MoDay of backup to database name
--rename logical & physical files
--change paths for physical files
--set CONCAT_NULL_YIELDS_NULL ON
--change database options for 9
--fix orphaned User accounts
--create missing Login accounts
--fix duplicate spid issue
--replace Login (8) to Schema (9) conversion with dbo
--remove Login Schema
--
-- Search for %%% to find areas that need customization
--
USE master
GO

PRINT 'Restore Single database, Fixing Logins and User accounts at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))
PRINT ''
GO

DECLARE @DB_Name varchar(128)
, @DB_BAK varchar(128)
, @UseMoDay bit
, @Password varchar(128)
, @DB_Path varchar(260)
, @Blob_Path varchar(260)
, @Img_Path varchar(260)
, @Idx_Path varchar(260)
, @Log_Path varchar(260)

SELECT @DB_Name = 'x_Config4'-- %%% database name to restore backup as  
, @DB_BAK = '\\DC1\SQL1\hd_RW3_Config4_Full' -- %%% path for the backup file
, @UseMoDay = 1 -- %%% Whether to _MoDay of backup to DB_Name
, @Password = 'password' -- %%% default password for new logins
-- %%% default paths for the new database files - FileGroup
--Search and replace these file group names
, @DB_Path = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' -- 'PRIMARY'
, @Idx_Path = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' -- 'INDICES'
, @Blob_Path = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' -- 'TEXTIMAGE'
, @Img_Path = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' -- 'IMGVAULT'
, @Log_Path = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' -- no file group name for log

SET NOCOUNT ON
DECLARE @NewDB varchar(128)
, @LogicalName varchar(128)
, @NewLogicalName varchar(128)
, @PhysicalName varchar(260)
, @DatabaseName varchar(128)
, @Ext varchar(20)
, @FileType char(1)
, @MoDay varchar(4)
, @FileGroup varchar(128)
, @ExecStr nvarchar(4000)
, @LoginName varchar(128)
, @Role varchar(128)
, @Type char(1)
, @Schema varchar(128)
, @Cnt int
, @Err int

IF (SELECT OBJECT_ID('tempdb..#Media')) > 0 
EXEC ('DROP TABLE #Media')
CREATE TABLE #Media (
LogicalName nvarchar(128),
PhysicalName nvarchar(260), 
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID  uniqueidentifier,
ReadOnlyLSN numeric(25,0),
 ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit);

IF (SELECT OBJECT_ID('tempdb..#Header')) > 0 
EXEC ('DROP TABLE #Header')
CREATE TABLE #Header (
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime, 
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier);

SET @ExecStr = 'RESTORE HEADERONLY FROM DISK = '''+@DB_BAK+''''
INSERT INTO #Header 
EXEC(@ExecStr)
SELECT @MoDay = REPLACE(CONVERT(varchar(5),BackupStartDate,1),'/','')
, @DatabaseName = DatabaseName  
FROM #Header
IF (SELECT OBJECT_ID('tempdb..#Header')) > 0 
EXEC ('DROP TABLE #Header')

SET @NewDB = @DB_Name+CASE WHEN @UseMoDay = CAST(1 as bit) THEN '_'+@MoDay ELSE '' END
PRINT 'Restoring '+@DatabaseName+' from '+@DB_BAK+' to '+@NewDB
PRINT ''

IF (SELECT DB_ID(@NewDB)) > 0 
BEGIN
PRINT 'Dropping existing database '+@NewDB
SET @ExecStr = 'DROP DATABASE ['+@NewDB+'] '
EXEC (@ExecStr)
PRINT ''
END

SET @ExecStr = 'RESTORE FILELISTONLY FROM DISK = '''+@DB_BAK+''''
INSERT INTO #Media 
EXEC(@ExecStr)

DECLARE MediaCursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT LogicalName, PhysicalName, Type, FileGroupName 
FROM #Media
ORDER BY Type, LogicalName

OPEN MediaCursor
-- Get the 1st record
FETCH NEXT FROM MediaCursor INTO @LogicalName, @PhysicalName, @FileType, @FileGroup
SET @ExecStr = 'RESTORE DATABASE ['+@NewDB+'] FROM DISK = '''+@DB_BAK+''' WITH '
-- Process the recordset
WHILE @@fetch_status = 0
BEGIN
SET @Ext = RIGHT(@PhysicalName,CHARINDEX('_',REVERSE(@PhysicalName)))
IF @FileType = 'L'
SET @ExecStr = @ExecStr + 'MOVE '''+@LogicalName+''' TO '''+@Log_Path+@NewDB+@Ext+''', '
ELSE
BEGIN
IF @FileGroup = 'PRIMARY'
SET @ExecStr = @ExecStr + 'MOVE '''+@LogicalName+''' TO '''+@DB_Path+@NewDB+@Ext+''', '
IF @FileGroup = 'INDICES'
SET @ExecStr = @ExecStr + 'MOVE '''+@LogicalName+''' TO '''+@Idx_Path+@NewDB+@Ext+''', '
IF @FileGroup = 'TEXTIMAGE'
SET @ExecStr = @ExecStr + 'MOVE '''+@LogicalName+''' TO '''+@Blob_Path+@NewDB+@Ext+''', '
IF @FileGroup = 'IMGVAULT'
SET @ExecStr = @ExecStr + 'MOVE '''+@LogicalName+''' TO '''+@Img_Path+@NewDB+@Ext+''', '
END
-- get the next record
FETCH NEXT FROM MediaCursor INTO @LogicalName, @PhysicalName, @FileType, @FileGroup
END -- Media loop
-- Clean up
CLOSE MediaCursor
DEALLOCATE MediaCursor
IF (SELECT OBJECT_ID('tempdb..#Media')) > 0 
EXEC ('DROP TABLE #Media')
SET @ExecStr = LEFT(RTRIM(@ExecStr),LEN(RTRIM(@ExecStr))-1)
PRINT 'Restoring database '''+@DatabaseName+''' from backup at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))
PRINT ''
EXEC(@ExecStr)
SET @Err = @@ERROR
IF @Err = 0
BEGIN
DECLARE LogiFileCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name, physical_name 
FROM sys.master_files 
WHERE database_id = DB_ID(@NewDB)
ORDER BY file_id

OPEN LogiFileCursor
-- Get the 1st record
FETCH NEXT FROM LogiFileCursor INTO @LogicalName, @PhysicalName
-- Process the recordset
IF @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT 'Renaming logical database file names'
PRINT ''
END
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NewLogicalName = RIGHT(@PhysicalName,CHARINDEX('\',REVERSE(@PhysicalName))-1)
SET @NewLogicalName = LEFT(@NewLogicalName,CHARINDEX('.',@NewLogicalName)-1)
IF @LogicalName <> @NewLogicalName
BEGIN
SET @ExecStr = 'ALTER DATABASE ['+@NewDB+'] MODIFY FILE (NAME = '''+@LogicalName
SET @ExecStr = @ExecStr+''', NEWNAME = '''+@NewLogicalName+''') '
EXEC(@ExecStr)
END
-- get the next record
FETCH NEXT FROM LogiFileCursor INTO @LogicalName, @PhysicalName
END -- Logical file loop
-- Clean up
CLOSE LogiFileCursor
DEALLOCATE LogiFileCursor

PRINT ''
PRINT 'Finished Restoring database '''+@DatabaseName+''' from backup at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))
PRINT ''

PRINT 'Fix database options for SQL 2000 default'
PRINT CHAR(9)+'Change '+@NewDB+' CONCAT_NULL_YIELDS_NULL to ON'
SET @ExecStr = 'ALTER DATABASE ['+@NewDB+'] SET CONCAT_NULL_YIELDS_NULL ON ;'
EXEC(@ExecStr)
PRINT ''

PRINT 'Fix database options for SQL 2005 default'
PRINT CHAR(9)+'Change '+@NewDB+' Page Verify to CHECKSUM'
SET @ExecStr = 'ALTER DATABASE ['+@NewDB+'] SET PAGE_VERIFY CHECKSUM ; '
-- %%% Comment next line to leave PAGE_VERIFY TORN_PAGE_DETECTION
EXEC(@ExecStr)
PRINT CHAR(9)+'Change '+@NewDB+' Compatibility level to 90'
-- %%% Comment next line to leave Compatibility level at 80
EXEC sp_dbcmptlevel @NewDB, 90
PRINT ''

IF (SELECT OBJECT_ID('tempdb..#Users')) > 0 
EXEC ('DROP TABLE #Users')
CREATE TABLE #Users (LoginName varchar(128), RoleName varchar(128), Type char(1), SchemaName varchar(128));
SET @ExecStr = 'SELECT u.name, r.name, u.type, u.default_schema_name '
+ 'FROM '+@NewDB+'.sys.database_principals AS u '
+ 'LEFT JOIN '+@NewDB+'.sys.database_role_members AS m ON u.principal_id = m.member_principal_id '
+ 'LEFT JOIN '+@NewDB+'.sys.database_principals AS r ON m.role_principal_id = r.principal_id AND r.type =''R'' '
+ 'WHERE u.type <> ''R'' '
+ 'AND u.name NOT IN (''sa'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') '
INSERT INTO #Users 
EXEC(@ExecStr)

-- Create the recordset for existing users
DECLARE UserCursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT LoginName, RoleName, Type, SchemaName
FROM #Users
ORDER BY LoginName

OPEN UserCursor
-- Get the 1st record
FETCH NEXT FROM UserCursor INTO @LoginName, @Role, @Type, @Schema
-- Process the recordset
IF @@FETCH_STATUS = 0
BEGIN
PRINT 'Fixing Logins and User accounts at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))
PRINT ''
END
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT char(9) + 'Login: ''' + @LoginName+ISNULL(''' Role: ''' + @Role,'')+ISNULL(''' Schema: '''+@Schema,'')+''''
-- See if the Login exists
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE type ='S' AND name = @LoginName)
BEGIN
-- Create the Login, using the default password
-- Requires ALTER ANY LOGIN
SET @ExecStr = 'CREATE LOGIN ['+@LoginName+'] WITH PASSWORD = '''+@Password+''', CHECK_POLICY = OFF '
EXEC(@ExecStr)
PRINT char(9) + char(9) + 'Created Login for ' + @LoginName
END
-- See if the User account is orphaned
SET @ExecStr = 'SELECT @Rows = COUNT(u.name) FROM '+@NewDB+'.sys.database_principals AS u LEFT JOIN sys.server_principals AS l ON u.sid = l.sid WHERE l.sid IS NULL AND u.name = '''+@LoginName+''' '
EXEC sp_executesql @ExecStr, N'@Rows int out', @Cnt out
IF @Cnt > 0
BEGIN
SET @ExecStr = 'EXEC '+@NewDB+'.dbo.sp_change_users_login ''Auto_Fix'', '''+@LoginName+''' '
EXEC(@ExecStr)
PRINT char(9) + char(9) + 'Fixed User account for ' + @LoginName
END
-- Test again to fix for 'duplicate SID'
SET @ExecStr = 'SELECT @Rows = COUNT(u.name) FROM '+@NewDB+'.sys.database_principals AS u LEFT JOIN sys.server_principals AS l ON u.sid = l.sid WHERE l.sid IS NULL AND u.name = '''+@LoginName+''' '
EXEC sp_executesql @ExecStr, N'@Rows int out', @Cnt out
IF @Cnt > 0
BEGIN
-- Requires ALTER ANY USER
SET @ExecStr = 'USE ['+@NewDB+']; DROP USER ['+@LoginName+']; '
EXEC(@ExecStr)
PRINT char(9) + char(9) + 'Deleted User account for ' + @LoginName
-- Requires ALTER ANY USER
SET @ExecStr = 'USE ['+@NewDB+']; CREATE USER ['+@LoginName+'] FOR LOGIN ['+@LoginName+']; '
EXEC(@ExecStr)
SET @ExecStr = 'EXEC '+@NewDB+'.dbo.sp_grantdbaccess @loginame = '''+@LoginName+''' ' 
EXEC(@ExecStr)
PRINT char(9) + char(9) + 'Granted '+@NewDB+' access to ' + @LoginName
END
IF ISNULL(@Schema,'dbo') <> 'dbo'
BEGIN
-- Change the default schema (for converted DB, will be LoginName instead of dbo)
-- Requires ALTER ANY USER
SET @ExecStr = 'USE ['+@NewDB+']; ALTER USER ['+@LoginName+'] WITH DEFAULT_SCHEMA = dbo; '
EXEC(@ExecStr)
-- Drop the SCHEMA if it is no longer related
SET @ExecStr = 'USE ['+@NewDB+']; IF EXISTS (SELECT * FROM sys.schemas AS s INNER JOIN sys.sql_logins AS l ON s.name = l.name LEFT JOIN sys.objects AS o ON s.schema_id = o.schema_id WHERE s.name NOT IN (''sa'',''dbo'',''guest'',''sys'',''INFORMATION_SCHEMA'') AND o.object_id IS NULL AND s.name = '''+@LoginName+''') '
+'DROP SCHEMA ['+@LoginName+']; '
EXEC(@ExecStr)
END
IF LEN(@Role) > 0
BEGIN
-- Drop the role schema if it exists
SET @ExecStr = 'USE ['+@NewDB+']; IF EXISTS (SELECT * FROM sys.schemas WHERE name = '''+@Role+''') '
+'DROP SCHEMA ['+@Role+']; '
EXEC(@ExecStr)
END
-- get the next record
FETCH NEXT FROM UserCursor INTO @LoginName, @Role, @Type, @Schema
END -- User account loop
-- Clean up
CLOSE UserCursor
DEALLOCATE UserCursor
IF (SELECT OBJECT_ID('tempdb..#Users')) > 0 
EXEC ('DROP TABLE #Users')
END -- @Err = 0
GO

SET NOCOUNT OFF
PRINT ''
PRINT 'Finished Restore Single database, Fixing Logins and User accounts at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))
GO