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