Automatic local area network backup and Deletion

Job operations

Enterprise Manager
-- Manage
-- SQL Server proxy
-- Right-click a job
-- Create a job
-- Enter the job name in "general"
-- "Step"
-- New
-- Enter the step name in "Step name"
-- Select "Transact-SQL script (tsql)" in "type" -- for example, the following SQL statement
-- "Database": select the database for Command Execution
-- Enter the statement to be executed in "command:
Exec stored procedure name... -- this stored procedure is used to create a table

-- OK
-- "Scheduling" item
-- Create Scheduling
-- Enter the scheduling name in "name"
-- Select your job execution schedule in "scheduling type"
-- If "repeated appears" is selected"
-- Click "change" to set your schedule

Then start the SQL Agent service and set it to Automatic startup. Otherwise, your job will not be executed.

Setting method:
My computer -- control panel -- Administrative Tools -- service -- Right-click SQLServerAgent -- properties -- start type -- select "auto start" -- OK.

-- Run the following code in the job for scheduling: automatic backup and automatic deletion of backups four days ago

-- 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


-- 2005 use Max to Support 2 GB strings
Declare @ s nvarchar (max), @ del nvarchar (max)
Select @ s = '', @ del =''

Select
@ S = @ s +
Char (13) + 'backup database' + quotename (name) + 'to disk = ''W:' + name + '_' + convert (varchar (8 ), getdate (), 112) + '. bak ''with init ',
@ Del = @ del +
Char (13) + 'exec master .. xp_mongoshell ''del W: '+ name +' _ '+ convert (varchar (8), getdate ()-4,112) + '. bak '', no_output'

From master.. sysdatabases where dbid> 4 order by dbid ASC

Exec (@ del)

Exec (@ s)