Refresh A SQL Server Database Automatically from PROD to TEST\DEV
As a DBA, we are often asked to refresh a database, which means to overwrite an existing database using a different database's backup. If you are rarely asked to do this, you may decide to do it manually. If you are asked to do this on a regular and perhaps scheduled basis, then you'd want to automate it.
To do the Refresh Manually, you would probably need to follow below steps :
Step-1 : Copy Backup files(here I took Spilt backup-2 files) from Source(Prod) to Destination(Test\Dev)
Step-2 : Take Destination (Test\Dev)Database Users Backup Before Starting the Restore.
Step-3 : Refresh the Destination Database from the copied PROD Backup File( from step-1 )
Step-4 : Drop Users from the Restored Database
Step-5 : Create Users on database from Step-2
Step-6 : Sync the Users.
Step-7 : Update the Statistics
To automate this process, we need to write code that does the above steps and then to schedule that code to run via the SQL Server Agent Job.
As we have to Refresh the TEST\Dev Database from Last night backup on PROD based on this I have Automated DBRefresh Task using below Script and it works Fine in my Environment. You have to schedule the job on Destination Server.
USE [msdb]
GO
/****** Object: Job [DBA - Refresh TEST from PRD] Script Date: 3/4/2018 5:00:53 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [REPL-Snapshot] Script Date: 3/4/2018 5:00:53 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Snapshot' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Snapshot'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - Refresh TEST from PRD',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Version 2.0
',
@category_name=N'REPL-Snapshot',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Copy Backups] Script Date: 3/4/2018 5:00:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Backups',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master..xp_cmdshell ''DEL C:\Restore\*.* /F /Q''
EXEC master..xp_cmdshell ''robocopy \\NODE1\Backup C:\Restore *TEST*.bak''',
@database_name=N'master',
@output_file_name=N'C:\Restore\output.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup Users] Script Date: 3/4/2018 5:00:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Users',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'SQLCMD -h -1 -W -S NODE2 -E -d TEST1 -i "C:\Scripts\script_users_for_db_refresh.sql" -o "C:\Scripts\TEST1_users.sql"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Refresh TEST1] Script Date: 3/4/2018 5:00:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh TEST1',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @Sql NVARCHAR(2000)
DECLARE @BuName1 NVARCHAR (500)
DECLARE @BuName2 NVARCHAR (500)
CREATE TABLE #Temp (Col1 VARCHAR(500))
INSERT INTO #Temp
EXEC master.dbo.xp_cmdshell ''dir C:\Restore''
DELETE #Temp
WHERE Col1 IS NULL
DELETE #Temp
WHERE Col1 NOT LIKE ''%TEST1%''
SELECT @BuName1 = SUBSTRING(Col1,40,75)
FROM #Temp
WHERE SUBSTRING(Col1,40,75) LIKE ''%1.bak''
SELECT @BuName2 = SUBSTRING(Col1,40,75)
FROM #Temp
WHERE SUBSTRING(Col1,40,75) LIKE ''%2.bak''
PRINT @BuName1
PRINT @BuName2
ALTER DATABASE [TEST1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [TEST1]
SET @SQL = ''RESTORE DATABASE [TEST1] FROM DISK = N''''C:\Restore\''+@BuName1+''''''
,DISK = N''''C:\Restore\''+@BuName2+''''''
WITH FILE = 1,
MOVE N''''TEST1'''' TO N''''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TEST1.mdf'''',
MOVE N''''TEST1_log'''' TO N''''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TEST1_log.ldf'''',
NOUNLOAD, REPLACE, STATS = 5''
PRINT @SQL
EXEC (@SQL)
ALTER DATABASE [TEST1] SET MULTI_USER
DROP TABLE #Temp
USE [TEST1]
GO
EXEC sp_changedbowner ''sa''
GO
',
@database_name=N'master',
@output_file_name=N'C:\Restore\output.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Drop Users] Script Date: 3/4/2018 5:00:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Drop Users',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [TEST1]
GO
DECLARE @Sql VARCHAR(1000)
DECLARE @User VARCHAR(25)
DECLARE UserCursor CURSOR
FOR SELECT DISTINCT
name
FROM sys.schemas
WHERE name NOT IN (''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',
''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'',''dbo'',''guest'',
''INFORMATION_SCHEMA'',''sys'',''ID_User'', ''AdsReporting'', ''globalUser'')
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @User
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = ''IF EXISTS (SELECT * FROM sys.schemas WHERE name = ''''''
+ @User + '''''')
DROP SCHEMA ['' + @User + '']''
PRINT @Sql
EXEC (@Sql)
FETCH NEXT FROM UserCursor INTO @User
END
CLOSE UserCursor
DEALLOCATE UserCursor
DECLARE UserCursor CURSOR
FOR SELECT name FROM sys.database_principals
WHERE type <> ''R''
AND name NOT IN ('''')
AND principal_id BETWEEN 5 AND 16383
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @User
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = ''IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''''''
+ @User + '''''')
DROP USER ['' + @User + '']''
PRINT @Sql
EXEC (@Sql)
FETCH NEXT FROM UserCursor INTO @User
END
CLOSE UserCursor
DEALLOCATE UserCursor
GO
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Creat Users] Script Date: 3/4/2018 5:00:54 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Creat Users',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @Exists INT
EXEC master.dbo.xp_fileexist "C:\Scripts\TEST1_users.sql",
@Exists OUT
IF @Exists = 1
BEGIN
EXEC master.dbo.xp_cmdshell ''SQLCMD -S NODE2 -d TEST1 -E -n -i "C:\Scripts\TEST1_users.sql"''
END',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Sync Users] Script Date: 3/4/2018 5:00:54 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Users',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [TEST1]
GO
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
and name <> ''dbo''
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + '' user name being resynced''
EXEC sp_change_users_login ''Update_one'', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Update Stats] Script Date: 3/4/2018 5:00:54 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Stats',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE TEST1
GO
EXEC sp_updatestats',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
No comments:
Post a Comment