MSDE database help,

[Kenos]

Issue: I have a MSDE SQL database that needs to return it to the original state after it is shown to customers.

I would like to create a job that will
Detach the database,
Copy the .mdf and .ldf file from a backup location located on the same machine
Attach the database.

Question:
If I use Enterprise Manager from another SQL server (Across Network) will this affect the
The way the job is run?
Using jobs on a MSDE database, will it affect the database performance?

Any help on this would be great.

Thanks in advance

Kenos

 

[Kenos]

For everyone out that may need to do this

In SQL Management under Jobs
Created Job

Step 1. T-SQL Script
Use Master
DECLARE @result int

EXEC @result = sp_detach_bd '<name of database>','true'

If (result = 0)
PRINT 'Success'
ELSE
PRINT 'Failed'

SETP 2. T-SQL script
Use master
DECLARE @result int

EXEC @result = xp_cmdshell 'copy C:\Databackup\database.mdf C:\WorkingData\database.mdf'

if (@result = 0)
@result = xp_cmdshell 'copy C:\Databackup\database.ldf C:\WorkingData\database.ldf'

ELSE

PRINT 'Failed'


STEP 3. T-SQL Script
USE master
DECLARE @result int

EXEC @result = sp_attach_db @dbname=N'<name of database>,
@filename1 = '<location of .mdf file>,
@filename2 = '<location of .ldf file>

if (results = 0)
PRINT 'Success'
ELSE
PRINT 'Failed'