That's a good one Nikos, Thanks for sharing your experience.
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