How to back up the database on the server to a local computer

Reprinted from: http://www.cnblogs.com/guoguo521/archive/2011/12/30/jayme1.html

Declare @ SQL varchar (500)
Select @ SQL = '\ 192.168.2.73 \ testfolder \' + '_ DB _' + convert (varchar (10), getdate (), 112) + substring (convert (varchar (10), getdate (), 108),) + substring (convert (varchar (10), datepart (minute, getdate ())), 1, 2) + '. bak'
Exec master.. xp_mongoshell 'net use \ 192.168.2.73 \ testfolder ""/User: 192.168.2.73 \ jaymezhang'
Backup database master to disk = @ SQL
Go

Use a remote database to back up local data.
Local databases can also be backed up remotely.

-- Backup environment: Back up the database server (192.168.1.8) database (TEST) to C $(192.168.1.145 ).

-- First, perform a ing with the client
Exec master .. xp_mongoshell
'Net use Z: \ 192.168.1.145 \ "/User: 192.168.1.145 \ Administrator'
/* -- Description:
Z: indicates the drive letter of the local machine corresponding to the ing network path, which corresponds to the following Backup
\ 192.168.1.145 \ C $ is the network path to be mapped
192.168.1.145 \ Administrator
192.168.1.145 is the remote computer name, and administrator is the login user name.
The password of the administrator user specified above
--*/

-Second, back up the database
Backup database test to disk = 'z: \ test. Bak'

-- Finally, delete the ing after the backup is complete.
Exec master.. xp_mongoshell 'net use Z:/delete'
-- From Network

-- BelowCodePut it in the job for scheduling, automatic backup, automatic deletion 4 days ago backup

-- Create a ing
Exec master.. xp_mongoshell 'net use W: \ databasebackup $ "password"/User: Roy ', no_output
Go
----- 2000 use a cursor:
Declare @ s nvarchar (200), @ del nvarchar (200)
Select @ s = '', @ del =''

Declare datebak cursor
Select
[Bak] = 'backup database' + quotename (name) + 'to disk = ''W:' + name + '_' + convert (varchar (8 ), getdate (), 112) + '. bak ''with init ',
[Del] = 'exec master .. xp_mongoshell ''del W: '+ name +' _ '+ convert (varchar (8), getdate ()-4,112) + '. bak '', no_output'
From master.. sysdatabases where dbid> 4 -- do not back up the system database
Open datebak

Fetch next from datebak into @ s, @ del
While @ fetch_status = 0
Begin
Exec (@ del)
Exec (@ s)
Fetch next from datebak into @ s, @ del
End
Close datebak
Deallocate datebak
Go
-- Delete ing
Exec master.. xp_mongoshell 'net use W:/delete'

Go

-- Use job.
-- SQL Server2000

Enterprise Manager-> database server-> Management directory-> SQL Server proxy-> job-> right-click and choose-> New

General options page-> enter a job name-> select the owner.

On the step Options page, choose new> enter the step Name> tsql script type> select the database to be executed> enter your SQL script in the Command box:

For example, update TB set status =... where date ...........

Click the "analysis" button in the lower left corner to analyze the syntax, analyze the syntax correctly, and press "OK.

On the scheduling option page, choose create scheduling and enter the scheduling name. You can select a scheduling type or click Change in the lower right corner to change the scheduling type.

Double-click the icon of the SQL Server server in the taskbar to select the SQL Server Agent and click Start/continue. When the OS is started, the service is automatically started.

At the specified time point, SQL Server will automatically execute your script.

If you need to generate a script, choose Enterprise Manager> Database Server> Manage directory> SQL Server proxy> job> right-click the job you just completed> all tasks

-> Generate an SQL script to generate the script you need.