Swaps databases for testing of SQL Server based applications. Backs up current database, replaces it with a prior version.

swap_db.sql

-- old:              Location to save backup
-- db_name:          Name of the SQL Server Database
-- app_admin:        Username of the administration login
-- default_password: Default password for a new administration login

-- BACKUP DATABASE TO DEFAULT LOCATION - NOTE: Filename currently hardcoded

BACKUP DATABASE [{{ db_name }}] TO DISK = '{{ old }}' WITH NOFORMAT, NOINIT,  NAME = N'{{ db_name }}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


-- DELETE APPLICATION ADMINISTRATOR LOGIN

IF EXISTS (SELECT * FROM sys.syslogins WHERE name = N'{{ app_admin }}') 
DROP LOGIN [{{ app_admin }}]

-- DELETE OLD DATABASE
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{{ db_name }}'
GO
USE [master]
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'{{ db_name }}')
ALTER DATABASE {{ db_name }} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [{{ db_name }}]
GO

-- RESTORE database from default location - NOTE: SQL Server file location hardcoded

USE [master]
RESTORE DATABASE [{{ db_name }}] FROM  DISK = '{{ new }}' WITH FILE = {{ file_pos }},  
MOVE N'{{ db_name }}' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\{{ db_name }}.mdf',  
MOVE N'{{ db_name }}_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\{{ db_name }}_log.ldf',  
NOUNLOAD,  STATS = 10
GO



-- DELETE APPLICATION ADMINISTRATOR USER

USE [{{ db_name }}]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'{{ app_admin }}')
DROP USER [{{ app_admin }}]


-- ADD NEW LOGIN & USER

USE [master]
GO
CREATE LOGIN [{{ app_admin }}] WITH PASSWORD=N'{{ default_password }}', DEFAULT_DATABASE=[{{ db_name }}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [{{ db_name }}]
GO
CREATE USER [{{ app_admin }}] FOR LOGIN [{{ app_admin }}]
GO
ALTER ROLE [db_datareader] ADD MEMBER [{{ app_admin }}]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [{{ app_admin }}]
GO


-- UPDATE NEW LOGIN

ALTER ROLE [db_owner] ADD MEMBER [{{ app_admin }}]
GO