Multiple methods of SQL Server2005 offsite backup _mssql2005

The first method:

This is a very common demand, online also has related solutions, mainly by mapping network disk and executing Cmdshell command to achieve, they do not benefit from data duplication

Today's implementation approach uses SQL Server service account patterns and security mechanisms to access network paths as follows:

1: To enable offsite backups, you must use a domain user account to start the SQL Server service and the SQL Server Agent service, because the local System account cannot access the network. In the Computer Management services to find these two services, the login account to the domain users, pay attention to the password should also be entered, if the password of the domain users later changed, remember here also to change, otherwise these two services can not start.

2: Create a shared folder in a remote machine and set the appropriate permissions.

3: Establish a maintenance plan for offsite backups in the SQL Server server, and in full backup and transaction log backups, use the UPN path of the input offsite shared folder.

4: Configure other settings in the maintenance plan.

If it is not insured, you need to backup in a remote network (not LAN), such as China backup to the United States, because the database file is large, compressible space is very large. That is to data compression using FTP transfer, the following methods:

First, the use of task scheduling +rar implementation of automatic compression

Right-click the file or folder you want to back up, and select Add to compressed file ...
1, Backup, check "Create a compressed file by mask", you can change to "YYYYMMDD"
2, Advanced, check "background compression"
3, the general, browse, and select the location to save
4, General, configuration, select "Save the current settings for new configuration", in the configuration parameters to change the configuration name to "Compressed file", check "Save selected file name", at this point on the desktop to create a shortcut to brighten, the same tick selected. At the same time, save the compressed file name and the immediate execution is also checked, the desktop is a "compressed file" shortcut. Double-click it to compress manually.
Open a task plan, start--Program--attachment--System Tools--task planning, blank right click, New Scheduled Task, renamed "Automatic Compression."
Right-click the "Compress files" shortcut on your desktop to copy the destination address. Double-click the "Auto Compress" task in the Task Scheduler, paste the address in the run, and ask for the current user's password when applied; schedule set

Second, the use of FTP automatic transmission to the destination

The following FTP upload, assuming that we want to upload the file in the D:/ftptmp folder, the server is ftp://10.0.0.2, the account CD, password 123, the server designated folder is XD, the specific three file steps operation. Write the following files separately:
1) The Ftprun1.bat content is as follows:
Ftp-i-s:ftpput.txt
2) The Ftpput.txt content is as follows:
Open 10.0.0.2
Cd
123
CD XD
Mput D:/ftptmp/*.rar
Bye
3) The Ftprun2.bat content is as follows
Del D:/ftptmp/*.rar
The above three steps of the operation of the idea is that the 1th and 2 joint action, the implementation of the file uploaded to the server in the designated folder, the 3rd is to implement the local deletion of the file.
4, the scheduling of tasks are: 1, copy files 2, compressed files 3, upload files, 4, delete files.
The task plan in accordance with the above steps to flexibly arrange the relevant time to run, began a bit of trouble, later on the worry-less easy.

The second method:

This is a very common demand, online there are related solutions, mainly through the mapping network disk and the implementation of the Cmdshell command to achieve! Today, the implementation method is slightly different, the idea stems from the recent in-depth study of the SQL Server service account model and security mechanisms, related articles see blog! This article describes the specific implementation methods in the SQL Server 2000 environment, and for SQL Server 2005, the implementation is the same!

One: Basic ideas
1: To enable offsite backups, you must use a domain user account to start the SQL Server service and the SQL Server Agent service, because the local System account cannot access the network.
2: Create an account with the same name as the domain user account that starts the SQL Server service on the SQL Server server in the remote machine, and the password remains the same. Set up a shared folder in a remote machine and set appropriate permissions.
Note: The new account is for Workgroup mode, and if it is based on domain mode, there is no need to build an account
3: Establish a maintenance plan for offsite backups in the SQL Server server, and in full backup and transaction log backups, use the UNC path of the input offsite shared folder.
4: Configure other settings in the maintenance plan.

The above statement may not be intuitive, and the following is a practical experiment to demonstrate how to do offsite backups. The experimental network model is very simple, the following figure:

II: demo of Offsite backup
1: Start the SQL Server service and the SQL Server Agent service in the SQL Server server with a domain user account.
The administrator account is used directly in this experiment to switch other domain user accounts according to the actual environment.

2: In the file backup server to establish a shared folder, share permissions to remove Everyone users, join the administrator user, permissions set as shown below.

3: On the SQL Server server, open Enterprise Manager and create a new database maintenance plan:

4: In the Maintenance Plan Wizard, select the database that you plan to back up, set up database optimization information, and check the integrity steps.
5: Set up the scheduling schedule in the "Specify Database backup Plan" wizard.
6: In the Specify Backup disk directory interface, manually enter the file Backup server shared folder UNC path.

7: In the transaction log backup schedule interface, depending on the actual environment, decide how to back up.
8: Follow the wizard to set up other steps to complete offsite backup maintenance plan.

Three: Test offsite backup is successful.
1: Check whether the backup file exists in the shared folder.

2: If no backup is successful, review the SQL Server log and check that the permissions settings and the user name and password are identical.

This article comes from "My son is so handsome!" blog

The third method:

One way to do this is to make a local backup and then sync through rsync or some FTP sync tools.