nikosag (10/5/2010)

---

Hello,
I do the same in my production system with the following, relatively simple 4 steps job:

Step1: Alter dev database to Single user mode to prepare it for the restore. (No need to delete the database or the datafiles first.)

ALTER DATABASE DevSystem SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Step2 Restoring Production database from the production server using a network path, containind the backup file.
(I use the MOVE command for the data and log files due to different storage paths. )

RESTORE DATABASE [DevSystem] FROM DISK = N'\\ProductionServer\BackupFolder\Dev.bak' WITH FILE = 1,
MOVE N'Datafile1' TO N'd:\MSSQL\Data\DevSystem_data1.mdf',
MOVE N'Datafile2' TO N'd:\MSSQL\Data\DevSystem_data2.ndf',
MOVE N'Logfile' TO N'd:\MSSQL\Data\DevSystem_log.LDF', NOUNLOAD, REPLACE, STATS = 90
GO

Step 3: Set the database to working state..

ALTER DATABASE DevSystem SET MULTI_USER
go

Step 4: Finally, if the dev database is on a different SQL Servel like in my case is essential to map the login username of the 1st SQL to login username of the 2nd with the following command:

sp_change_users_login update_one ,exampleusername,exampleusername
go

Regards

Nikos

Step 4: Finally, if the dev database is on a different SQL Servel like in my case is essential to map the login username of the 1st SQL to login username of the 2nd with the following command:

sp_change_users_login update_one ,exampleusername,exampleusername
go

Regards

Nikos

Why use VB scripts.

SanjayAttray (10/5/2010)

---

Why do this task with VB script when you can do the whole process with T-SQL scripts?