Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Backup and restore (copy) sql database to specific drive based on environment
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment