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