T-SQL for restoring a database

Bits and pieces of SQL code were gathered for my own use when restoring a database. Best used when back-porting data. Posted as a helper for myself - But feel free to modify and use it.

-- INSTRUCTIONS:
-- 1. Replace custom_database with the actual database name (Use a replace all on custom_database)
-- 2. Change @BackUpFileName to the actual location
-- 3. Change parameters for @DbUser & @LoginPassword

-- ============================
-- Restore [custom_database]
-- ============================
DECLARE @BackUpFileName varchar(100), @DbUser varchar(100), @LoginPassword varchar(100);

SELECT @BackUpFileName = 'c:\temp\backups\custom_db_20150101.bak';
SELECT @DbUser = 'custom_db_user';
SELECT @LoginPassword = 'p@55w0rd';

PRINT 'Restoring [custom_database]';
USE master;

-- ============================
-- Kill All connections to DB
-- ============================
PRINT '-------------------------';
PRINT 'Killing all connections to database...';
ALTER DATABASE [custom_database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

IF @@ERROR <> 0 
    GOTO FAILURE;

PRINT '-------------------------';
PRINT 'Retoring database...';
PRINT @BackUpFileName;

RESTORE DATABASE [custom_database]
    FROM  DISK = @BackUpFileName
WITH  
    FILE = 1,  
    NOUNLOAD,  
    REPLACE,
    STATS = 10;

IF @@ERROR <> 0 
    GOTO FAILURE;

-- ============================
-- Fix Users on [custom_database]
-- ============================
PRINT '-------------------------';
PRINT 'Fixing users on database...';
USE [custom_database];

EXEC sp_change_users_login 'Auto_Fix', @DbUser, NULL, @LoginPassword;
EXEC sp_change_users_login 'update_one', @DbUser, @DbUser;

IF @@ERROR <> 0 
    GOTO FAILURE;

-- ============================
-- ANY OTHER CUSTOM ACTIONS
-- ============================

--  i.e. UPDATE db_users SET PASSWORD = 'password'

IF @@ERROR <> 0 
    GOTO FAILURE;

-- ============================
-- Allow connections to DB
-- ============================
PRINT '-------------------------';
PRINT 'Allowing connections to database...';
ALTER DATABASE [custom_database] SET MULTI_USER;

IF @@ERROR = 0 
    GOTO SUCCESS;    
ELSE    
    GOTO FAILURE;

SUCCESS:
    PRINT '-------------------------';
    PRINT 'Successfully restored database!';
    GOTO FINISH;

FAILURE:
    PRINT '-------------------------';
    PRINT 'An error ocurred :('
    GOTO FINISH;

FINISH:
    PRINT '-------------------------';
    PRINT 'DONE!';

---

This article was originally written on Google's Blogger platform and ported to Hashnode on 17 Sep 2022.