Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Restore 2000 DB to 2005

By David A. Long, 2007/09/14

This script was designed to restore a backup of a SQL Server 2000 database from disk file to SQL Server 2005.

Unfortunatelly the RESTORE will leave land mines for you to find the hard way, this script was updated to fix all that I have found:

Change CONCAT_NULL_YIELDS_NULL ON, PAGE_VERIFY CHECKSUM, Compatibility level to 90, remove the database role and user schema creation, and reassign the user's schema to dbo.

Like the ealier RestoreDB script posted here, this version will handle multiple DB files, move the files to new location, with new database name that is customized for the date of the backup, create any missing logins, and fix any user accounts that are orphaned by differing SIDs from the other server.

-- 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