SQLTeam.com | Weblogs | Forums

Automation scripts for Backup and restore


#1

i have a requirement like we need to backup a database and restore it on daily basis. Instead of doing the same task daily I want to automate the process so that the jobs will automatically backup the database and restore them at scheduled intetrval. Do we have any scripts for that?


#2

Microsoft has some tools built in to do automatic backups - look in SSMS object explorer under Management -> Maintenance Plans. They have wizards which will walk you through the process.

Many people use third party scripts. Two that are well regarded are Ola Hallegren's scripts and Minion Backup.

As for restoring, that is not something that people normally do automatically (or maybe, but I don't know). You can use the user interface to set up a restore, and just before clicking the final OK in the restore dialog, click the script button at the top left. That will generate the script to restore the database per your settings in the dialog. You can take that script and modify it to suit your needs and schedule it.


#3

We use the Hallengren Maintenance solution for backups
As for your restore, where is it being restored to? like a dev or QA environment?

Here are some references for The Maintenance Solution and scripts for restoring when using the maintenance solution:

Maintenance Script Credit: Ola Hallengren
https://ola.hallengren.com/

Original Restore Script Credit: Greg Robidoux

Modified Restore Script Credit: Jason Carter
http://jason-carter.net/professional/restore-script-from-backup-directory-modified.html


#4

In my opinion Hallengren's scripts are very heavy-weight. Fine for someone DBA-like needing the sophisticated options it offers, but for someone without those skill levels I think Minion is a better choice. Once installed (which is straightforward) Minion will just start making backups of everything, with default settings which are suitable in most cases, and in the rest will at least be fail-safe .