Restore DB from Disk File script

-- RestoreDB script DAL - 4/22/2003
--
-- Search for %%% to find areas that need customization
--
USE master
GO

PRINT 'Restoring backup, Fixing Logins and User accounts at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
PRINT ''
GO

DECLARE @BAK_file varchar(128)
	, @DB_Path varchar(260)
	, @DB_Name varchar(128)
	, @Password varchar(10)
	, @EPassword varchar(10)

-- %%% database name to restore to
SET @DB_Name = 't_DB'
-- %%% path for the backup file
SET @BAK_file = 'C:\Xfer\t_DB_20030404'
-- %%% default path for the new database files
SET @DB_Path = 'C:\Program Files\Microsoft SQL Server\MSSQL\data-- %%% default default password for a new login
SET @Password = 'password'
-- %%% default app password for a new login
SET @EPassword = '9@R_6X-A'

DECLARE @NewDB varchar(128)
	, @LogicalName varchar(128)
	, @PhysicalName varchar(260)
	, @DatabaseName varchar(128)
	, @Ext varchar(20)
	, @MoDay varchar(4)
	, @ExecStr nvarchar(4000)
	, @LoginName varchar(20)
	, @RoleName varchar(20)
	, @Pass varchar(10)
	, @Cnt int
	, @Err int

SET NOCOUNT ON

CREATE TABLE #Media (
	LogicalName varchar(128),
	PhysicalName varchar(260), 
	Type char(1),
	FileGroupName varchar(128),
	Size numeric(20,0),
	MaxSize numeric(20,0) 
	)

CREATE TABLE #Header (
	BackupName varchar(128),
	BackupDescription  varchar(255),
	BackupType smallint,
	ExpirationDate datetime,
	Compressed tinyint,
	Position smallint,
	DeviceType tinyint,
	UserName varchar(128),
	ServerName varchar(128),
	DatabaseName varchar(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 varchar(128)
	)

SET @ExecStr = 'RESTORE HEADERONLY FROM DISK = '''+@BAK_file+''''
INSERT INTO #Header EXEC(@ExecStr)
SELECT @MoDay = REPLACE(CONVERT(varchar(5),BackupStartDate,1),'/',''), 
	@DatabaseName = DatabaseName 
FROM #Header
DROP TABLE #Header
SET @NewDB = @DB_Name+'_'+@MoDay


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

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

OPEN MediaCursor
-- Get the 1st record
FETCH NEXT FROM MediaCursor INTO @LogicalName, @PhysicalName
SET @ExecStr = 'RESTORE DATABASE ['+@NewDB+'] FROM DISK = '''+@BAK_file+''' WITH '

-- Process the recordset
WHILE @@fetch_status = 0
	BEGIN
		SET @Ext = RIGHT(@PhysicalName,CHARINDEX('_',REVERSE(@PhysicalName)))
		SET @ExecStr = @ExecStr + 'MOVE '''+@LogicalName+''' TO '''+@DB_Path+@NewDB+@Ext+''', '
		FETCH NEXT FROM MediaCursor INTO @LogicalName, @PhysicalName
	END
-- Clean up
CLOSE MediaCursor
DEALLOCATE MediaCursor
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)
SELECT @Err = @@ERROR
IF @Err = 0
BEGIN
	SET @ExecStr = 'BACKUP LOG ['+@NewDB+'] WITH TRUNCATE_ONLY'
	EXEC(@ExecStr)
	SET @ExecStr = 'ALTER DATABASE ['+@NewDB+'] SET RECOVERY SIMPLE'
	EXEC(@ExecStr)
	SET @ExecStr = 'DBCC SHRINKDATABASE (['+@NewDB+'], 20) WITH NO_INFOMSGS'
	EXEC(@ExecStr)
	PRINT ''
	PRINT 'Finished Restoring database '''+@DatabaseName+''' from backup at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
	PRINT ''

	CREATE TABLE #Users (LoginName varchar(128), RoleName varchar(128))
	SET @ExecStr = 'SELECT u.name, r.name FROM '+@NewDB+'.dbo.sysusers  AS u '
	SET @ExecStr = @ExecStr + 'INNER JOIN '+@NewDB+'.dbo.sysmembers AS m ON u.uid = m.memberuid '
	SET @ExecStr = @ExecStr + 'INNER JOIN '+@NewDB+'.dbo.sysusers AS r ON m.groupuid = r.uid '
	SET @ExecStr = @ExecStr + 'WHERE (u.issqluser <> 0 AND u.issqlrole = 0 AND u.gid <> 0) '
	INSERT INTO #Users EXEC(@ExecStr)

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

	OPEN UserCursor
	-- Get the 1st record
	FETCH NEXT FROM UserCursor INTO @LoginName, @RoleName
	-- 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
			-- skip sa, dbo, guest
			IF @LoginName NOT IN ('sa', 'dbo', 'guest')
				BEGIN
					PRINT char(9) + 'Login: ''' + @LoginName + ''' Role: ''' + @RoleName + ''''
					IF @RoleName = 'AppDefault'
						SET @Pass = @Password
					ELSE
						SET @Pass = @EPassword
	        			-- Process the User account
					-- See if the Login exists
					IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE loginname = @LoginName)
						BEGIN
							-- Create the Login, using the default password
							EXEC sp_addlogin @LoginName, @Pass, 'tempdb'
							PRINT char(9) + char(9) + 'Created Login for ' + @LoginName
						END
					ELSE
						IF EXISTS(SELECT loginname FROM master.dbo.syslogins WHERE isntname=0 AND password IS NULL AND loginname = @LoginName)
							BEGIN
								-- Change the Login password from Null
								EXEC sp_password NULL, @Pass, @LoginName
								PRINT char(9) + char(9) + 'Changed password for Login ' + @LoginName
							END
					-- See if the User account is orphaned
					SET @ExecStr = 'SELECT @Rows = COUNT(sysusers.name) FROM '+@NewDB+'.dbo.sysusers AS sysusers LEFT JOIN master.dbo.syslogins AS syslogins ON sysusers.sid = syslogins.sid WHERE syslogins.sid IS NULL AND sysusers.name = '''+@LoginName+''' '
					EXEC sp_executesql @ExecStr, N'@Rows int out', @Cnt out
					IF @Cnt > 0
						BEGIN
							SET @ExecStr = @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(sysusers.name) FROM '+@NewDB+'.dbo.sysusers AS sysusers LEFT JOIN master.dbo.syslogins AS syslogins ON sysusers.sid = syslogins.sid WHERE syslogins.sid IS NULL AND sysusers.name = '''+@LoginName+''' '
					EXEC sp_executesql @ExecStr, N'@Rows int out', @Cnt out
					IF @Cnt > 0
						BEGIN
							SET @ExecStr = @NewDB+'.dbo.sp_dropuser '''+@LoginName+''' '
							EXEC(@ExecStr)
							PRINT char(9) + char(9) + 'Deleted User account for ' + @LoginName
							SET @ExecStr = @NewDB+'.dbo.sp_adduser '''+@LoginName+''', '''+@LoginName+''', '''+@RoleName+''' '
							EXEC(@ExecStr)
							PRINT char(9) + char(9) + 'Created User account for ' + @LoginName
						END		
				END -- @LoginName NOT IN ('sa', 'dbo', 'guest')
			FETCH NEXT FROM UserCursor INTO @LoginName, @RoleName
		END --WHILE @@FETCH_STATUS = 0
	-- Clean up
	CLOSE UserCursor
	DEALLOCATE UserCursor
	DROP TABLE #Users
END -- @Err = 0
SET NOCOUNT OFF 

PRINT ''
PRINT 'Finished Restoring backup, Fixing Logins and User accounts at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
GO