Last active
April 19, 2016 10:28
-
-
Save emmanueltissera/2f2ce445abdafafd9b3e to your computer and use it in GitHub Desktop.
Generic T-SQL for restoring a database from a backup
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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!'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment