This code works on the PROD server, and the real key is the ability to back up to a REMOTE server, you'll see that we're sending the dbs directly to the TEST server, and we restore from there.
This is the restore code, it runs on the TEST server and calls the above code (from the PROD server)
USE [master]
go
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'fp_RestoreTESTDatabses'
)
DROP PROCEDURE dbo.fp_RestoreTESTDatabses
GO
CREATE OR ALTER PROCEDURE fp_RestoreTESTDatabses
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @name varchar(255)
DECLARE @db VARCHAR(255)
DECLARE @sql VARCHAR(max)
DECLARE @spid VARCHAR(10)
--BACKUP all the databases on NGB-SQL-04
PRINT '================================================================================================================'
PRINT 'backing up dbs on SQL 04'
PRINT '================================================================================================================'
--note that this is through a linked server.
--this code runs a proc on the remote server, and that code copies the backup files to this server
SET @sql = '[10.1.101.12].ngb01.dbo.fp_Backup_Databases_To_Test'
EXEC (@sql)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@body_format = 'HTML',
@recipients = 'steve@4penny.net',
@copy_recipients = '',
@blind_copy_recipients = '',
@body = 'DB copy from PROD to TEST starting',
@exclude_query_output = 1,
@subject = 'DB copy from PROD to TEST starting' ;
--================================================================================================================
-- kill spids so that the restore will run
--================================================================================================================
PRINT '================================================================================================================'
PRINT 'Killing SPIDS'
PRINT '================================================================================================================'
--get a list of all the spids to kill
DECLARE @spids TABLE (session_id VARCHAR(255), name VARCHAR(255), nt_user_name VARCHAR(255))
SET @sql = 'SELECT ses.session_id, dbs.name, nt_user_name FROM sys.dm_exec_sessions ses JOIN master..sysdatabases dbs ON dbs.dbid = ses.database_id WHERE is_user_process = 1 AND dbs.filename LIKE ''e%'' '
insert into @spids execute (@sql)
DECLARE curKill CURSOR LOCAL FAST_FORWARD FOR
select session_id, name, nt_user_name FROM @spids
OPEN curKill
WHILE 1=1
BEGIN
FETCH NEXT FROM curKill INTO @spid, @db, @name
if @@fetch_status <> 0 begin
break
end
SET @sql = 'kill ' + @spid
PRINT @sql
EXEC (@sql)
END
CLOSE curKill
DEALLOCATE curKill
--================================================================================================================
-- restore the databases to TEST
--================================================================================================================
PRINT '================================================================================================================'
PRINT 'restoring DOCS'
PRINT '================================================================================================================'
RESTORE DATABASE [DOCS] FROM DISK = N'E:\SQL Backups\DOCS_daily.bak' WITH FILE = 1, MOVE N'DOCS' TO N'E:\SQL Data\DOCS.mdf', MOVE N'DOCS_log' TO N'F:\SQL Logs\DOCS_log.ldf', NOUNLOAD, REPLACE, STATS = 100
PRINT '================================================================================================================'
PRINT 'Cleanup, Kill Users'
PRINT '================================================================================================================'
SET @Sql = 'use dynamics IF OBJECT_ID (''dbo.syVendorAnalysisDeleteTrigger'',''TR'') IS NOT NULL DROP TRIGGER dbo.syVendorAnalysisDeleteTrigger '
EXEC (@sql)
delete dynamics..activity WHERE 1=1
DELETE dynamics..sy01500 WHERE NOT interid = 'ngb01'
--================================================================================================================
-- kill all the users in the databases
--================================================================================================================
DECLARE curOuter CURSOR LOCAL FAST_FORWARD FOR
select name
from master.sys.sysdatabases s
WHERE s.filename LIKE 'e%'
OPEN curOuter
WHILE 1=1
BEGIN
FETCH NEXT FROM curOuter INTO @db
if @@fetch_status <> 0 begin
break
END
PRINT '======================================================================'
PRINT @db
SET @sql = N'select RTRIM(name) from ' + @db + '.sys.sysusers s WHERE NOT name IN (''dbo'',''guest'',''information_schema'',''sys'',''rPostMasterEnterprise'',''RAPIDGRP'',''public'',''DYNWORKFLOWGRP'',''DYNGRP'',''SMARTLISTDESIGNERWF'') AND NOT name LIKE ''db[_]%'' AND NOT name LIKE ''rpt[_]%'' ORDER BY s.name'
PRINT @sql
DECLARE @users TABLE ( name varchar(255))
insert into @users execute (@sql)
DECLARE curInner CURSOR LOCAL FAST_FORWARD FOR
SELECT * FROM @users u
OPEN curInner
WHILE 1=1
BEGIN
FETCH NEXT FROM curInner INTO @name
if @@fetch_status <> 0 begin
break
end
SET @sql = 'use ' + @db + ' DROP SCHEMA IF EXISTS ' + QUOTENAME(@name) + ' ' + 'DROP user if exists' + QUOTENAME(@name)
PRINT @sql
EXEC (@sql)
END
CLOSE curInner
DEALLOCATE curInner
END
CLOSE curOuter
DEALLOCATE curOuter
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@body_format = 'HTML',
@recipients = 'steve@4penny.net',
@copy_recipients = '',
@blind_copy_recipients = '',
@body = 'DB copy from PROD to TEST complete',
@exclude_query_output = 1,
@subject = 'DB copy from PROD to TEST complete' ;
PRINT '================================================================================================================'
PRINT 'Complete'
PRINT '================================================================================================================'
GO
Grant EXEC on fp_RestoreTESTDatabses to public