Backup and restore (copy) sql database to specific drive based on environment

CopySqlDatabase.sql

-- This script copies a database. The paths in this script are designed to run on IQ-RGVSQL009.
-- You need to have SQLCMD mode enabled to run this script (Query menu -> SQLCMD Mode).
:setvar DbToBackup  "SeedCore"				-- The database to copy from.
:setvar DbToRestore "Developer_DanS"		-- The database to create/overwrite.
:setvar Environment ""	-- Should be either "Test", "Automation", or an empty string (for Dev). Corresponds to the Sql Server Instance the DB is on.

:setvar TruncateDevDatabases "True"			-- Leave this as 'True' to not keep full logs for the DbToRestore on the Dev Sql Server Instance.
:setvar PutAutomationDbOnSlowDrive "True"	-- Leave this as 'True' to restore the DbToRestore to the Slow hard drive on the Automation Sql Server Instance.

USE [master]
GO

--=====================================
-- Backup Database.
--=====================================
BACKUP DATABASE [$(DbToBackup)] TO DISK = N'E:\Backup\$(Environment)\$(DbToBackup).bak'
WITH COPY_ONLY,		-- Do not have this backup interfere with the regularly scheduled backups.
INIT				-- Overwrite previous backups rather than appending to them.
GO


--=====================================
-- Restore Database.
--=====================================
-- Disconnect any users from the database before restoring it.
DECLARE @spid INT
SELECT @spid = min(spid) FROM master.dbo.sysprocesses WHERE dbid = db_id('$(DbToRestore)')
WHILE @spid Is Not Null
BEGIN
	EXECUTE ('Kill ' + @spid)
	SELECT @spid = min(spid) FROM master.dbo.sysprocesses WHERE dbid = db_id('$(DbToRestore)') and spid > @spid
END
GO

-- Let's drop the database too, since we're restoring overtop of it anyways.
-- Sometimes we get an error that the DB is still in use when restoring; hopefully this will fix that.
IF EXISTS (SELECT Name FROM master.sys.databases WHERE name = '$(DbToRestore)')
	ALTER DATABASE [$(DbToRestore)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS (SELECT Name FROM master.sys.databases WHERE name = '$(DbToRestore)')
	DROP DATABASE [$(DbToRestore)]
GO

-- Get the Data and Log file names to use for the restore.
DECLARE @DataFileName varchar(5000)
DECLARE @LogFileName varchar(5000)
SELECT TOP 1 @DataFileName = name FROM sys.master_files WHERE database_id = db_id('$(DbToBackup)') AND type_desc = 'ROWS'
SELECT TOP 1 @LogFileName = name FROM sys.master_files WHERE database_id = db_id('$(DbToBackup)') AND type_desc = 'LOG'

-- If we are restoring an Automation DB, specify to restore it to the slow disk drive.
DECLARE @RestoreDrive NVARCHAR(1)
SET @RestoreDrive = 'E'			-- Most databases exist on drive E.
IF ('$(Environment)' = 'Automation' AND '$(PutAutomationDbOnSlowDrive)' = 'True')
BEGIN
	Set @RestoreDrive = 'S'		-- Drive S is the slow disk drive.
END

-- Build the paths to retore the database's Data and Log files to.
DECLARE @DataFileRestorePath NVARCHAR(1000)
DECLARE @LogFileRetorePath NVARCHAR(1000)
SET @DataFileRestorePath = @RestoreDrive + N':\DATA\$(Environment)\$(DbToRestore).mdf'
SET @LogFileRetorePath = @RestoreDrive + N':\TLOG\$(Environment)\$(DbToRestore).ldf'

-- Perform the actual restore.
RESTORE DATABASE [$(DbToRestore)] FROM  DISK = N'E:\BACKUP\$(Environment)\$(DbToBackup).bak' WITH FILE = 1,  
MOVE @DataFileName TO @DataFileRestorePath,
MOVE @LogFileName TO @LogFileRetorePath,
REPLACE
GO

-- If we are restoring a Dev database, change it from keeping Full logs to Simple logs, and truncate the logs to save space.
USE [$(DbToRestore)]
IF ('$(Environment)' = '' AND '$(TruncateDevDatabases)' = 'True')
BEGIN
	PRINT N'Shrinking log file for development database...'
	ALTER DATABASE [$(DbToRestore)] SET RECOVERY SIMPLE
END
GO
IF ('$(Environment)' = '' AND '$(TruncateDevDatabases)' = 'True')
	DBCC SHRINKFILE (2, 100)
GO