Automate copy and restore of all PROD databases to a separate server

Question

Automate copy and restore of all PROD databases to a separate server

jdc

Old Hand

Points: 367
More actions
June 20, 2019 at 5:49 pm

#3655870

Hi,
We are using ola hallengren backup script to backup databases backups.
Now, I'm planning to automate copying those weekly full backups to a separate server and restore them. Can you please advise if there are any automated methods or scripts available to achieve this?
Thanks,
-jdc
June 21, 2019 at 6:57 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

There are a lot of articles available on this site and web posts about refreshing databases. And there are articles on automating this. The articles basically say - create a restore script for your environment, create a script for cleaning up the users and then put these in an Agent job. That's how it's automated. You can search the scripts up here - search on restore or refresh and you will find quite a few examples. There are a lot of others if you search the internet for something like: automate database refresh sql server

And in addition, you will need to understand exactly what the scripts do so you can support them or make any necessary changes. Understanding what the scripts do, how they work and how you can change or modify them is critical.

Sue

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

If prod is on a SAN, a lot of SAN vendors provide the ability to do "SAN Snapshots", which are virtually instantaneous.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andreas.kreuzberg SSCertifiable Points: 6192 More actions · Answer 3

Hi,

take a look at dbatools.io

You can copy your databases with one script from Server A to Server B, with or without user etc.

Kind regards,

Andreas

jdc Old Hand Points: 367 More actions · Answer 4

I use datapump, expdp and impdp in Oracle to refresh lower environments. Do we have similar functionality in SQL Sever instead of backup/restore ?

This reply was modified 5 years, 6 months ago by jdc.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

dbatools can do this, but SQL Server doesn't really have this. I guess the Data Tier Applications and the .bacpac extrat/import do this as well. I'd lean towards dbatools.

http://dbatools.io

Sue_H SSC Guru Points: 90860 More actions · Answer 6

jdc wrote:

I use datapump, expdp and impdp in Oracle to refresh lower environments. Do we have similar functionality in SQL Sever instead of backup/restore ?

Nothing the same but it also depends on what functionality you use with Data Pump. For functionality like the PL/SQL API or picking and choosing which objects/schemas and such, you'd probably need to use something like SSIS. If you are just using it for nothing other than the export, import than dbatools and Powershell would work.

Sue