[Summary] How to implement automatic data backup in sqlserver!

1st methods:

Enterprise Manager
-- Manage
-- Right-click the database maintenance plan
-- Create a maintenance plan
-- <Next>
-- Select the database you want to back up
-- <Next> until "specifying the database backup plan"
-- Specifies the location where the backup file is stored. Select the backup file or disk as needed.
-- Click "change" button next to scheduling
-- Define the schedule of your data backup plan and confirm after completion
-- <Next>
-- You can set the backup file retention time and the backup file extension.
-- <Next>
-- Select whether to make a log backup plan
-- <Next> until the process is completed.

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

2nd methods:

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 )"
-- "Database": select the database for Command Execution
-- Enter the statement to be executed in "command": backup database name to disk = 'C: \ backup file name. Bak'
-- 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.

3rd methods:

Create a job:
Enterprise Manager, manage -- SQL Server proxy -- job -- Right-click to create a job -- enter a name on the general page -- create a step on the step page, enter a name, and add an SQL statement: -- create a scheduling task on the "scheduling" Page, enter a name, select "recurrence", and change the time -- "OK"

If the SQL agent is not started, it is automatically started when the OS is started in service manager --> SQL Agent

In the Enterprise Manager, right-click the job you just created -- start the job -- OK

---- Add the following SQL statement: -- and modify the Database Name and backup directory.

Declare @ filedir varchar (200), @ dir varchar (200)
Declare @ dbname varchar (50), @ bakname varchar (50), @ SQL varchar (4000)
Select @ filedir = filename from sysfiles
Set @ dir =''
While charindex ('\', @ filedir) <> 0
Begin
Set @ dir = @ dir + Left (@ filedir, charindex ('\', @ filedir ))
Set @ filedir = substring (@ filedir, charindex ('\', @ filedir) + 1, Len (@ filedir)-charindex ('\', @ filedir ))
End
Set @ bakname = 'bak' + Cast (datepart (weekday, getdate ()-1 as varchar (1 ))
----- Specify Database
Set @ dbname = 'scmbuilder'

Set @ SQL = 'backup database ['+ @ dbname +'] to disk = n' + 'e: \ sqlbak \ '+ @ bakname + ''' with init, nounload, noskip, stats = 10, noformat'
-- Back up the file to the "E: \ sqlbak \" Directory
Exec (@ SQL)

Application may be downgraded during BackupProgramDatabase operation performance, but SQL will be well coordinated, but application operations on the database may be slower

If you encounter a problem when starting the SQL Agent service and cannot start it, the following error is reported:

The service does not return an error. This may be a Windows internal error or a service internal error.
If the problem persists, contact your system administrator.

After reading the help, I found that my SA database password has changed. Is it necessary to verify the connection to the SQL Agent service?

Then, right-click the SQL Server Agent ----> attribute ----> connection.

Reset the SA logon password and then enable the SQL Agent service.