Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have one sql server instance with two databases :

myDB_LIVE

myDB_TEST

both point to separate mdf and ldf files and are backed up each night. I want to take a copy of LIVE and put it in TEST. I would even like to do this as a regular job, what is the best way ?

I can do a restore from a live back-up but was concerned I would overwrite the existing live database as they are on same server instance ?

share|improve this question
2  
Please check your open source zealotry at the door. Connotations like putting a "$" in MS are unprofessional. –  Chris S May 16 '12 at 14:26

1 Answer 1

up vote 1 down vote accepted

I would set the following up as a Stored Procedure and then create a job to run it each night:

----Make Database to single user Mode
ALTER DATABASE myDB_TEST
SET SINGLE_USER WITH
 ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE myDB_TEST
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
 mode.
 If error occurs please execute following command it will convert
 database in multi user.*/
ALTER DATABASE myDB_TEST SET MULTI_USER
 GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.