Automate offsite storage of database backups with scheduled tasks and batch handlers

Source: Internet
Author: User
Tags file copy management studio sql server management sql server management studio

The first step is to create a shared folder (password protected) on the backup destination computer, and map the shared folder to the cost geomagnetic disk (which maps the z-disk, which appears in the code below).

Second step, the database is backed up regularly (SQL2008 example)

1. Open SQL Server Management Studio, and in Object Explorer, point to open local--> Management--maintenance plan--right-click Maintenance Plan Wizard;

2. Maintenance Plan Wizard

3. Select a Schedule attribute

4. Operation Plan

5. Select maintenance Tasks

6. Select the database to be backed up

Select the backup destination path

7. Select report Options

8. Complete

The third step is to create a bat file on the server that automatically copies the files

Copy and paste the following code into the TXT file to save, and then change the txt file suffix name to bat

The path to the first half is the database backup source folder Note that there was no

The latter half of the path is the destination folder finally has \

(The parameters in the management command do not make too much explanation here)


@echo off

xcopy/d/e/c/i/h/r/y "C:\Program Files (x86) \microsoft SQL Server\mssql\backup" "Z:\03 system Data backup \tjbs\ small r3\"
IF ERRORLEVEL 0 ECHO Successful copy file
IF ERRORLEVEL 1 ECHO not found copy file
IF ERRORLEVEL 2 ECHO user aborts copy operation via Ctrl-c
IF ERRORLEVEL 3 ECHO Preset error prevents file copy operation
IF ERRORLEVEL 4 Write error during ECHO copy


Exit



Fourth step, set up Scheduled Tasks (take Windows Server R2 as an example)

1. Create basic tasks

2. Create Basic Task Wizard

3. Task trigger

4. Set execution time and interval

5, select the Task automatic program mode

6. Select the Startup program

7. Complete

Final validation

1. Manual Operation

This prompts the batch command to run successfully complete

Go to the destination path to check if the file is automatically copied over.

This is simply the ability to automatically back up data by scheduling tasks and batch program time data, as well as backing up too large to delete a long-overdue backup of data.

Automate offsite storage of database backups with scheduled tasks and batch handlers

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.