Offsite Backup and Restore scenarios

The content is obtained from the Baidu Library, on the two of the backup when this is the fourth method, the following content only provides reference

The first implementation is as follows: (SQL Server 2000) This is a common requirement, and there are related solutions on the web, mainly by mapping the network disk and executing the Cmdshell command. Today, the implementation method is slightly different, the idea comes 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 idea 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, if it is based on domain mode, there is no need to build account number 3: A maintenance plan for offsite backups in SQL Server server, in full backup and transaction log backups, using the UNC path of the input offsite shared folder (\ Shared Machine ip\
　　Shared folders).
　　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. Two: Offsite Backup Demo 1: Starts the SQL Server service and the SQL Server Agent service in the SQL Server server with a domain user account. (Admin tools-> Service-> login) The administrator account is used directly in this experiment to switch other domain user accounts according to the actual environment.
       (Login ID: This account is set up) 2: Set up a shared folder in the file backup server, delete Everyone in the share permissions, join the administrator user, permission settings (readable, writable).
　　3: In the SQL Server server, open Enterprise Manager, 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 completeOffsite 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.
---------------------------------------The second implementation approach is as follows:-------------------------SQL Server 2005 Automated Machine Backup
  I looked up some information on the network after finally in a very silly way to solve the problem of SQL 2005 of the different machine backup, now I give you two: SQL Server 2005 database system support three kinds of backup methods: Full backup, differential backup and log backup. My current implementation is: first: 1 times a week full backup, backup time of 6 per week 0 o'clock, and the full backup of this time to another file server.
Second: 1 differential backups per day, the backup time is set at 0 o'clock in the evening, and the backup is routed to the file server. Automated backup implementation: For database backups, SQL Sever 2005 provides a Visual wizard and two backup methods using the BACKUP statement.
The following backup strategies are implemented in two ways. The Visual Wizard creates a backup: SQL Sever 2005 You can create a maintenance plan by using the Maintenance Plan Wizard to automate the backup database. The specific steps are as follows: (1) Right-click pop-up menu on "admin"-"Maintenance Plan", select "Maintenance Plan Wizard", set "name of maintenance plan", and fill in the name of the plan (self-definition).
Set Agent execution maintenance plan account and password (2) Select the maintenance plan type, for example, a database backup (full backup), define a maintenance plan task, where you need to select the appropriate location and filename for the backup file (for example, Test.bak), and change the option to overwrite if the backup file exists (*). (3) Set the schedule execution cycle.
Set to 0 points per Saturday, and keep a good maintenance plan Operation report.
(4) Determine--complete. This completes the complete backup of the database and, following the steps above, sets up a differential and log backup of the database.
After the completion of the test run. You can also use the BACKUP statement to create backups. The BACKUP statement allows you to back up the entire database, the things log, and the syntax for backing up these objects is more complex, but much the same: full backup: Go Backup database TEST (database name OH) to DISK = ' E:\BACK Up\test. BAK (backup file OH) ' \ This is the specified backupLocation of the OH with Init ' Here's init is to overwrite the existing backup set Oh go so add a schedule, set the cycle of execution (once a week), and the time (0 o'clock in the evening), and so on.
Then determine--complete. Similarly, differential backups and log backups can be implemented in the above manner, and the execution cycles and times are set according to the policy of the backup, and their statements are: Differential backups: Using Test go backup DATABASE TEST to DISK = ' E:\BACKUP\TEST.B AK ' \ Specifies the location of the backup with defferential \ Specifies the type of backup for differential backups oh go log back up backup log TEST to DISK = ' E:\BACKUP\LOG. LDF ' \ Specify the location of the backup go machine store backup files in security consideration, even after a backup has been made on this computer, there is still the possibility that the machine itself means that the event has corrupted or lost the backup file, so it is safer to keep a copy of the backup file on the other machines, and this will take advantage of the Windows task Plan and a copy batch file can be implemented. You can use the following two ways to complete the purpose of the different machine storage (I have tried both, the first one for the convenience of OH): (1) in a domain server way: The backup set into the SQL Sever Integrated System server, log in as a domain account, follow the steps below to achieve the automatic backup file transfer: 1. Create a new one on the backup machine. BAT file, edit the following content Save: Copy \\192.168.1.100\E$\BACKUP\TEST. BAK E:\BAK\ Description: 192.168.1.100 is an integrated system server IP address, e$\backup\test.
BAK is a full backup of the integrated system database, E:\BAK\ is the location where the full backup file is transferred.
2. Add a task schedule to the backup machine, locate the newly created batch file, cycle to "weekly", change the time, and enter the domain username and password. 3. Determine---complete.


This allows for a complete backup of the database of the different machine storage purposes.
(2) In addition to the way in which domain users log in, you can also use the net command to copy the backup files to the directory to be placed, but this situation to ensure that the backup machine ipc$ to open, and the two machines are consistent with the user and password (mainly to ensure that the restart of the machine do not have to remap again OH). Concrete steps: 1.
On the integrated system server, start---run-enter cmd and enter the command line window. 2. Under the Command Line window, enter the following command: Net usE z:\\192.168.1.100\e$ ' 123456 '/user:administrator
Description: 192.168.1.100 is the backup machine IP address, 123456 for the password, the administrator is his username, the above statement means that the backup machine e disk map to the local z disk. 3. Create a. bat file on the integrated system server to deliver the file, edit the following and save as a custom filename. bat copy E:\backup\test.bak z:\ The custom file name.
Bat 4. Add a task plan, find the. bat file just defined, cycle to "weekly", change "Start time" to evening 0, date to "Saturday", and enter the username and password of the machine. 5. Determine---complete.
  You can do it. Full backup file Test.bak is stored on the backup machine.
Also, use the above 3, 4, 5 steps to complete the automatic transfer of the differential file. If you are using NET, you must open ports 139 and 445, which poses some danger to the security of the server----------------------------------------The third implementation method: (SQL Server)------
---------------------------Instance Description: Environment: win2k+sqlserver 2k+ Query Analyzer SQL Server service instance name: Mainserver database name to be backed up: msdb Local machine name (client side): David Local User: ZF Password: 123 local domain name: domain local to provide backup requirements folder: E:/test First step: Create a shared folder in the program code call (or CMD window) net share test=e:/
Test or use the Netshareadd API to briefly explain: net share: is a network command within Windows.
Role: Create a local shared resource that displays shared resource information for the current computer.
Syntax: see net share/? Step two: Build a shared credit relationship master.
xp_cmdshell ' net use//david/test 123/user:domain/zf ' brief description: 1:xp_cmdshell: Is an extended stored procedure for SQL Server.
function, executes the given command string in the manner of the operating system command-line interpreter, and returns any output as a text line. Syntax: See SQL Server online Help 2:nEt use: is a network command within Windows. To connect or disconnect a computer from a shared resource, or to display information about a computer connection.
This command also controls persistent network connections.
Syntax: see net use/?
Step three: Back Up database backup db msdb to disk= '//david/test/msdb.bak ' This doesn't need to be explained, syntax see SQL Server online Help step Fourth: Delete shared folders
Call (or CMD window) net share Test/delete in program code or Netsharedel This API result: processed 1376 pages that belong to the file ' Msdbdata ' of the database ' msdb ' (located on file 1).
Processed 1 pages that belong to the file ' Msdblog ' (located on file 1) of the database ' msdb '.
The BACKUP DATABASE operation successfully processed 1377 pages and took 3.653 seconds (3.086 mb/sec). This way, msdb on the Mainserver server is backed up to the David Machine's E:/test/msdb.bak file, which is easy to use. The same is true with the restore database operation, as long as the statement of the third step is changed to ' Restore database msdb from disk= '//david/test/msdb.bak '. You can try it after you see it.
(Simplest test Tool Query Analyzer +cmd window) Note: xp_cmdshell This extended stored procedure can only be invoked by the SA level, and is one of the security vulnerabilities of SQL Server, which many DBAs like to delete or disable, so be careful when using the developer. ----------------------------------------------------Fourth Implementation method:----------------------here provide two methods: 1, written as a stored procedure, set up a job scheduled backup- Map exec master in SQL. xp_cmdshell ' net use z: \\computer_name\D$ ' password '/user:computer_name\administrator '/*--description: Z: is the disk character that maps the network path corresponding to this machine, and corresponds to the following backup \ computer_name\d$ is the network path to map computer_nameis the remote computer name, the administrator is the login username password specified Administrator user password--*/--backup; with init overwrite ¦noinit add backup database name to disk= ' E : \h_backup.bak ' with init--copy exec master. xp_cmdshell ' copy E:\H_BACKUP.bak z: '-delete (this can be removed)--exec master. xp_cmdshell ' del E:\H_BACKUP.bak '--delete map after completion of exec master. xp_cmdshell ' net use Z:/delete ' 2, dispatch Enterprise Manager--Manage--sql Server Agent--right job--New job--Enter the job name in the general item--"Step"--new--"Step name" Enter the step name-- Type Select Transact-SQL script (TSQL)--Database Select database to execute command--Enter the statement to execute in command: DECLARE @strsql varchar (1000) DECLARE @strdirname var CHAR (DECLARE @strcmd varchar DECLARE @strsend varchar (1000) DECLARE @strdate varchar () exec master. xp_cmdshell ' net use \\192.168.0.151\d$ password/user:192.168.0.151\administrator ' Set @strsql = ' Backup Database new_ His to disk= ' \\192.168.0.151\d$\serverd\ ' set @strdirname =replace (substring (varchar (), GETDATE (), 120), 1,10), '-', ', ' + ' Set @strcmd = ' MD \\192.168.0.151\d$\serverd\ ' Set @strcmd = @strcmd + @strdirname exec master. Xp_Cmdshell @strcmd--print @strsql Set @strsql = @strsql + @strdirname + ' \new_hisbackup.dat ' with Init,nounload,noskip, Noformat '--print @strsql exec (@strsql)----which write IP address and shared directory, users modify their own, password must be correct--determine--"schedule"--New schedule--"name" in the Dispatch name-"Scheduling Type select your job execution schedule--If you choose ' Recurring '--click ' Change ' to set your schedule and then start the SQL Agent service, and set to start automatically, otherwise your job will not be implemented set method: My Computer--control Panel--management tools--services--right button
sqlserveragent--Property--Startup Type--select Auto Start--OK. ------------------------------------------Fifth method:--------------------problem: A client's SQL Server 2k database wants to back up to another server, SQL SQL Server and the service account of the agent service can be set as domain users in the server, and the backup files can be written to the specified network path at backup time. 
However, when a user runs SQL Server using a domain user, it reflects that the system is running unstable or that the system account is used so that network backups cannot be achieved. 
Scenario: Previous colleagues experimented with the use of the local computer to specify a network drive, and then write the backup file to the network drive, the implementation of the different machine backup. Environment Description: Hardware environment: Database server 192.168.1.5, backup server 192.168.1.3 operating system: All win 2k ad +SP4 Other: Backup server 192.168.1.3, C:;d:;e: Three hard drives specific practices: 1, in the backup clothing Server settings backup directory and share this directory 1.1 a local user has been created on the backup servers: Dbbak, Password: 12345 1.2 Create a directory backup under E: Under the back up server, \complete \log;\dif 1.3 share Backu P directory, share name is backup, permissions only user Dbbak can complete control, everyone read only 2, set up a backup task on the database server, the backup script is as follows:/*****--Script for task execution ********//*-Map the \\192.168.1.3\backup\ to local drive Y on the database server: specific commands See window help syntax: net use [{devicename | *} ] [\\computername\sharename[\volume]] [{Password | *}]] [/user:[domainname\]username] [/user:[dotteddomainname\] UserName] [/user:[username@dotteddomainname] [/savecred] [/smartcard] [{/delete |/persistent:{yes | no}}] net use [Devic ename [/home[{password | *}] [/delete:{yes | no}] NET use [/persistent:{yes | no}] */Exec xp_cmdshell ' net use Y: \\1 There are two kinds of 92.168.1.3\backup 12345/user:192.168.1.3\dbbak ' backup methods: First, direct backup on the local hard drive, then compress the backup files, then move the compressed files directly to the backup server.  Suitable for local hard disk space in the case--After the backup is completed, the backup file compression, compression complete, delete the original file C:\progra~1\winrar\rar a-df-ep Log2006.rar y:\log\*.* in SQL implementation see: DECLARE @sql varchar (m) DECLARE @Filepath varchar () Set @filePath = ' d\backup\test ' backup database pubs to disk = @filePath + ' Pubs.bak ' Set @Sql = ' C:\progra~1\winrar\rar a-df-ep ' + @Filepath + ' + @Filepath + ' \*.* ' Exec xp_cmdshell @Sql, No_output Set @Sql = ' rd/s/q ' + @Filepath Exec XP_cmdshell @Sql Exec xp_cmdshell net use Y: \\192.168.1.3\backup 12345/user:192.168.1.3\dbbak ' exec xp_cmdshell ' move 
/y D:\dbbak\*.rar y:\ ' Exec xp_cmdshell ' Net use Z:/delete ' II, put the backup files directly on the backup server-execute the following stored procedure, with the backup start date as the directory, and put the backup files of the database log on the backup server, such as: \\192.168.1.3\e$\ Backup\servername\log\200601091200\ *.trn exec s_sys_backup ' Y:\log ', ' Log ', 2-------------------------------------- ----Sixth method: Offsite online backup---------------------------SQL Server I want to contribute author: Anonymous technical article Source: Network clicks: Update time: 2011-11-24★★★ "word
Body: Small large "-SQL Server database remote backup One, establish backup job backup SQL Server database on server databasex to the shared directory Databack of B server. The steps are as follows:--sql Server Agent--right key job--New job--Enter job name in general item--"Step"--new--Enter step name in step Name--select "Transact-SQL script (TSQL)" in "type"--"database" SELECT Execute Database databasex--Command Enter the statement you want to execute: DECLARE @strsql varchar (1000) DECLARE @strfilename varchar (a) DECLARE @strcmd varchar ( DECLARE @strsend varchar (1000) DECLARE @strdate varchar () exec master. xp_cmdshell ' net use Y: \\B\databack "password string"/user:b\ username ' Set @strsql = ' Backup DAtabase databasex to disk= ' y:\ ' Set @strfilename =databasex+replace (CONVERT (substring), varchar (), 120), 1,10), '-', ') + '. Bak '--print @strsql set @strsql = @strsql + @strfilename + ' ' with init '--print @strsql exec (@strsql)--After completion Delete Map exec master ... xp_cmdshell ' net use Y:/delete ' II, license xp_cmdshell the xp_cmdshell option introduced in SQL Server 2005 and above is the server configuration option, which enables system administrators to control whether XP_ can be executed on the system Cmdshell extended stored procedures. By default, the xp_cmdshell option is disabled on newly installed software, but can be enabled using policy-based management or running sp_configure system stored procedures, as shown in the following code example: Copy code--to allow advanced
Options to be changed.
EXEC sp_configure ' show advanced options ', "1 Go"-To update the currently configured value for advanced options.
Reconfigure go-to enable the feature.
EXEC sp_configure ' xp_cmdshell ', 1 Go--to update the currently configured value for this feature. Reconfigure go---------------------------------------