I would like to create a process where everyday or every week I backup a database from my production server and restore it on my staging server for testing purpose.
My first idea was to create a Maintenance plan but maintenance plan can only backup. When I backup the backup system also generate name including the date a time of the backup.
On my file system I can find my backup under:
Now from my ServerStaging I cannot use a Maintenance Plan because there is no restore database option. So I'm going to create a job. Step 1 of my job I kill all connection on existing database and I drop it. I don't care it is staging. Step 2 I restore:
RESTORE DATABASE MyDb FROM DISK = '\\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak'
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device '\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
And now I'm lost. No need to tell you I'm not use to database administration.