T-SQL for restoring a database

Some random and not-so-random stuff I came across today and couldn't help keep silent about!

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

T-SQL for restoring a database

Comments

More from this blog

Tiny Elf - Transferring ownership of automated triggers

I dug a trench and unearthed some Software Engineering principles

Happy Birthday, Luminary - H5YR!

Content delivery API in Umbraco 12 RC 1 - Did I say Headless?

Tiny Elf - a Slack bot with Google sheets and Typescript

Command Palette

Comments

More from this blog