SQL Server Remote Backup Method

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/C $ "password"/User: 192.168.1.145/Administrator '
/* -- Note:
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 databases
Backup database test to disk = ' Z:/test. bak '

--Finally, delete the ing after the backup is complete.
ExecMaster .. xp_mongoshell'Net use Z:/delete'
--From Network

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

-- Create 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 For
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 Mappings
Exec Master .. xp_mongoshell ' Net use W:/delete '

Go

--Use job.
--SQL Server2000 as an Example

Enterprise Manager->Database Server->Manage directories->SQL Server proxy->Job->Right-click and choose->New

General options page->Enter the job name->Select the owner.

Step Options page->Create->Enter the step name->Type tsql script->Select the database to be executed->Enter your SQL script in the Command box:

For example:UpdateTBSetStatus=...WhereDate ...........

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

Scheduling option page->Create scheduling->Enter the scheduling name->You can select or click the change button in the lower right corner to change the scheduling type.

Double-click the icon of the SQL Server server in the taskbar and select the SQL Server Agent from the service. Click Start./Continue]. Select to automatically start the service when the OS is started.

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

If you need to generate a script, Enterprise Manager->Database Server->Manage directories->SQL Server proxy->Job->Right-click the job you just completed->All tasks

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

 

 

 

 

 

----------------------------------------------------------------------------------

 

 

-- The following two steps can be taken to back up the environment above sql2005:
-- Create linked server
Exec Sp_addmediaserver ' Link_server_name ' , '' , ' Sqloledb ' , ' SERVER_NAME or server_ip ' ;
Exec Sp_add1_srvlogin ' Link_server_name ' , ' False ' , ' SA ' , ' SA ' , ' Password ' ;

-- remote backup of dbname database
exec ( ' backup database dbname to disk = '' C:/dbname. bak '' ; ' ) at link_server_name;