SQLServer: Backup a database on a server and restore it on another server 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:

\\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak


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.
It is not necessary to drop the database before restoring. Adding WITH REPLACE will restore the backup over an existing database.

RESTORE DATABASE MyDb FROM DISK = \\ServerProd\d$\DatabaseBackup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak' WITH REPLACE


Regarding the "Access is denied" error. The service account running your SQL Server will need to have permission on the network share.

If you are not using an Active Directory service account (which is highly recommended) you need to copy the .bak file to the server before restore.

Finally, I would suggest looking at Ola Hallengren's maintenance solution. It has a comprehensive backup and restore features: Ola Hallengren SQL Server Backup. 
This will give you a flexible and robust (and free) backup solution.

Randi Vertongen suggested dbatools. 

It is an amazing toolset for all things SQL Server. For your problem look at the commandlets Backup-DbaDatabase and Restore-DbaBackupFromDirectory.