2

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.

enter image description here

enter image description here

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.

Improve this question
2
  • What are the versions of your SQL Server installations? What are the Editions (Enterprise / Standard / Web / Express / ...) of your SQL Server installations? Please add this information to your question either as text or as tags. Thanks.
    – John K. N.
    Commented Nov 13, 2018 at 10:27
  • 2
    Does the staging server have the same instance name and the same data path? If not you'll also need WITH MOVE because once you're able to access the backup, you'll need to make sure the restore directs the files to the right location on the staging server.
    – Aaron Bertrand
    Commented Nov 13, 2018 at 12:40

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • 2
    Fully agree, I would also look at dbatools for automation
    – Randi Vertongen
    Commented Nov 13, 2018 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.