Maitenance Plan Restoring db

SQL Server Administration
1

Hi everyone
I have a server sql 2008 r2 std in which there are two databases: 006 for production and 007 for testing, what I need to do is update the test base daily and automatically with the information of the production database, every day a maintenance plan is executed that makes a backup of the production bd, my question is if I can make another maintenance plan that restores the bd 007 with this backup file
is this possible?

Thanks in advance

2

The way I do it is

  1. Copy the database from backup location to test server
  2. Using SQL job restore to test db

Make sure there are no confidential or sensitive data in there you would need to obfuscate

3

Hi yosiasz,
The problem is that I dont see the SQL job restore option, where can I found it? I'm looking on the jobs secction but is not there

thanks

4

This is what I use in my case
I have a table that defines the databases I want to restore

CREATE TABLE [dbo].[DatabaseRestore](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [varchar](50) NOT NULL,
	[ActiveFlag] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER proc [dbo].[restoredatabase_sp]
as
begin

	--database_you_want_to_restore
	if exists (select 1 from dbo.DatabaseRestore where databasename = 'database_you_want_to_restore' and activeFlag = 1)
		begin
			EXEC DBAdmin.dbo.vrKillSPIDS 'database_you_want_to_restore';
			alter database database_you_want_to_restore set   RESTRICTED_USER  with rollback immediate;
			
			RESTORE DATABASE database_you_want_to_restore FROM  DISK = N'D:\RestoreDB\PRODBACKUPS\database_you_want_to_restore.bak' WITH  FILE = 1,  
			MOVE N'database_you_want_to_restore' TO N'D:\DATA\database_you_want_to_restore.mdf',  
			MOVE N'database_you_want_to_restore_log' TO N'D:\LOGS\database_you_want_to_restore.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
			
			alter database database_you_want_to_restore set  multi_user with rollback immediate;
end

and then add this to a SQL Job and schedule it. There could be other better ways but this is what I do.

5

also recently added the following

EXEC sp_change_users_login 'Auto_Fix', 'YOURDOMAIN\svc-account', NULL

in case you use a different service account in test, which you really should.