Generic T-SQL for restoring a database from a backup

restore-database.sql

-- 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!';