Method of database of MSSQL scheduled backup

A tutorial method for MSSQL scheduled backup database
Method One

To use the remote database, you can back up the local.
The local database can also be backed up to remote.

--Backup environment: Backup database Server (192.168.1.8) database (TEST) to (192.168.1.145) under C $

--First, do a mapping with the client
EXEC master.. xp_cmdshell
' net use z: \192.168.1.145c$ ' password '/user:192.168.1.145administrator '

--second, make a database backup
Backup database TEST to disk= ' Z:test.bak '

--finally. Delete mappings after backup completes
EXEC master.. xp_cmdshell ' net use Z:/delete '
--From Network

--The following code in the job to do scheduling, automatic backup, automatically delete 4 days before the backup

--Create a map
EXEC master.. xp_cmdshell ' net use w:databasebackup$ ' password '/user:roy ', no_output
Go
-----2000 with Cursors: www.111cn.net
Declare @s nvarchar, @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_cmdshell ' 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_cmdshell ' net use W:/delete '

Go

--Use job.
--sql SERVER2000 as an example

Enterprise Manager-> database server-> Management eye

Record->sql Server Agent-> job-> right key select-> New

General Options Page-> Enter the job name-> Select the owner.

Steps Options page-> new-> input step name-> type TSQL Script-> Select the database you want to execute-> enter your SQL script in the Command box:

such as: Update TB Set status = ... where date .....

You can point to the lower left corner of the "Analysis" button, analyze the syntax, analysis is correct, press OK.

Scheduling Options page-> new schedule-> input dispatch name-> Dispatch type you can also click the "Change" button in the lower right corner to make the change, OK.

Small icon for the taskbar SQL Server server double-click the service to select SQL Server AGENT, click Start/Continue, and select the Automatically start service when starting the OS.

At that point in time, SQL Server will automatically execute your script.

If you need to generate scripts, Enterprise Manager-> database server-> Manage Directory->sql Server Agent-> Job-> Right-click the job you just completed-> All tasks

-> generate SQL scripts to generate the scripts you need.
Method Two

Enterprise Manager--management--Database maintenance plan