Maitenance Plan Restoring db

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

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

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


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
ALTER proc [dbo].[restoredatabase_sp]

	if exists (select 1 from dbo.DatabaseRestore where databasename = 'database_you_want_to_restore' and activeFlag = 1)
			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;

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

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.