SqlServer2008 data backup and remote backup

For developers, database backup is very important, whether it is a normal development of the version of the maintenance or operation of data backup, yesterday back up the database when the problem is sorted out for your reference:

This article is taking SQL Server R2 as an example.

1. The database itself has its own database backup, in the database-management-Maintenance plan right-click New Maintenance Plan (same as the Maintenance Plan Wizard effect), enter the plan name (Mmdbbackup) in the pop-up window,

Once confirmed, drag the backup data task to the design screen in the bottom left corner of the window, then right-click to edit it, including database selection, backup to disk, storage path, verifying database integrity,

Set up backup compression (using the server default settings) to determine the completion settings (the picture is too large to be forgiven). So the backup settings are complete, and the following set what time to execute, in the sub-plan click on the day

calendar icon, perform the associated time setting, and then finalize the database task schedule backup after the setup is finished.

2. Remote database backup: Implement remote database backup by job, back up the 192.168.35.96 database to 192.168.35.104, as follows:

In SQL Server Agent right-start-job right-new job--fill in the Job name, category (Database maintenance)--"step--New step--Configuration related information (including SQL script)--to determine

--"plan--new plan--Configure the relevant information."

SQL script:

EXEC master. xp_cmdshell ' net use \\192.168.35.104\ Test 96 backup Password123/user:192.168.35.104\administrator '

DECLARE @ss varchar (50)
Set @ss = CONVERT (varchar), GetDate (), 112)
Set @ss = ' \\192.168.35.104\\ Test 96 backup \ ' [email protected]+ '. Bak '
Backup Database Mrdb to [e-mail protected] with FORMAT

EXEC master. xp_cmdshell ' net use \\192.168.35.104\\ Test 96 backup/delete '

Principle: Using xp_cmdshell to create a network map, the backed up database, stored in the map letter, test 96 backup is a 192.168.35.104 server to allow read and write shared folders,

After execution, delete the map and release the resources.

SQL Script parsing: \\192.168.35.104\ Test 96 backup is a shared path that a network allows to read and write

password123 is the login password for the 104 server

192.168.35.104\administrator is the login user name of the 104 server

@ss is to determine the path after the backup and the backup file name (\\192.168.35.104\\ Test 96 backup \20140625.bak)

With format can overwrite any existing backup and create a new media set

xp_cmdshell General database will be put into the security settings, you need to turn it on:

Execute: Enable SQL BACKUP command
EXEC sp_configure ' show advanced options ', 1;--allow configuration
reconfigure;--re-configuration
EXEC sp_configure ' xp_cmdshell ', 1;--open xp_cmdshell
reconfigure;--re-configuration

Step Part reference diagram: