How to back up the database with scheduled tasks?

How to back up the database with scheduled tasks?

SQL code

Formulation of scheduled jobs

 

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:

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.

 

 

 

How can I synchronize server data to my local database?

SQL code

 

Sqlserver2000 synchronous Replication

Author: gaojier Date: 2007-06-25

Font size: medium or large

I. Preparations

1. On the Publishing Server, the subscription server creates a Windows user with the same name, and sets the same password as the valid user who accesses the snapshot folder.

-- Management Tools

-- Computer Management

-- Users and groups

-- Right-click the user

-- Create a user

-- Create a Windows login user (synuser) affiliated to the Administrator Group)

2. Create a new shared directory on the Publishing Server as the directory for storing the published snapshot files. perform the following operations:

My computer -- D: \ creates a directory named pub

-- Right-click the newly created directory

-- Property -- share

-- Select "share this folder"

-- Use the "permission" button to set specific user permissions to ensure that the user (synuser) created in step 1 has all permissions on the folder.

-- OK

3. Set the startup user of the SQL proxy (SQLServerAgent) Service (this setting is done by the publishing/subscription server)

Start --Program-- Management Tools -- services

-- Right-click SQLServerAgent

-- Property -- login -- select "this account"

-- Enter or select the Windows logon User Name (synuser) created in step 1)

-- Enter the user's password in "password"

4. Set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers)

Enterprise Manager

-- Right-click an SQL instance -- Properties

-- Security -- Authentication

-- Select "SQL Server and Windows"

-- OK

5. Register with each other on the Publishing Server and subscription Server

Enterprise Manager

-- Right-click the SQL server group

-- Create an SQL Server registration...

-- Next -- available server, enter the name of the remote server you want to register -- add

-- Next -- connect to use, select the second "SQL Server Authentication"

-- Next -- enter the user name and password (synuser)

-- Next -- select an SQL Server group or create a new group.

-- Next -- complete

6. Register a server alias for an IP address only and a computer name not available (this step is not used in implementation)

(Configure on the connection end. For example, if the server is configured on the subscription server, enter the IP address of the Publishing Server in the server name)

Start -- program -- Microsoft SQL Server -- client network utility

-- Alias -- add

-- Select "TCP/IP" for the network library -- enter the SQL server name as the server alias

-- Connection parameter -- enter the SQL Server IP address in the server name

-- If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number.

Ii. Formal Configuration

1. Configure the Publishing Server

Open the Enterprise Manager and perform the following steps on the Publishing Server (B, c, d:

(1) select [configuration Publishing, subscription server and Distribution] from the [copy] sub-menu in the [tools] drop-down menu to see the configuration publishing and distribution wizard

(2) [next] You can select the distribution server as the distribution server or another SQL Server (select your own)

(3) [next] set the snapshot folder

Use the Default \ servername \ pub

(4) [next] custom configuration

You can select: Yes. Let me set the attributes of the distribution database to enable the Publishing Server or set the publishing settings.

No, use the following default settings (recommended)

(5) [next] use the default value to set the name and location of the Distribution Database

(6) [next] enable the Publishing Server and select as the Publishing Server

(7) [next] select the database and release type to be released

(8) [next] Select register subscription Server

(9) [next] complete configuration

2. Create a publication

Release server B, C, and D

(1) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu.

(2) Select the database for which you want to create the publication, and click [Create release]

(3) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)

(4) Click [next] system requirements to specify the database server type that can subscribe to the release,

Sqlserver allows data replication between different databases, such as oracle or access.

But here we choose to run the database server "SQL Server 2000"

(5) Click [next] And a definition will pop up.ArticleDialog box is used to select the table to be published.

NOTE: If transaction publishing is selected, only tables with primary keys can be selected in this step.

(6) Select the release Name and description.

(7) options provided by the custom publishing properties Wizard:

Yes. I will filter custom data, enable Anonymous subscription, and other custom attributes.

No. Create a release according to the specified method (custom method is recommended)

(8) [next] Select a method for filtering and Publishing

(9) [next] You can select whether anonymous subscription is allowed

1) if you select "signed Subscription", you need to add a subscription server to the Publishing Server.

Method: [tools]-> [copy]-> [configure attributes of publishing, subscription server, and Distribution]-> Add in [subscription server]

Otherwise, a prompt will be displayed when you request subscription on the subscription server: you cannot subscribe anonymously if you change the subscription to publish.

If anonymous subscription is still required, use the following solution:

[Enterprise Manager]-> [copy]-> [publish content]-> [properties]-> [subscription options] Select allow anonymous request subscription

2) if you select Anonymous subscription, the above prompt will not appear When configuring the subscription server.

(10) [next] configuring snapshot Agent Scheduling

(11) [next] complete configuration

After the publication is created, the database for creating the publication becomes a shared database.

 

 

 

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

 

 

-- Reference:

/*

Author: zhujian

*/

 

/* -- Example of synchronizing two databases

 

Data available

Srv1. the database name. Author has the following fields: ID, name, phone,

Srv2. database name... author has fields: ID, name, telphone, adress

 

Requirements:

Srv1. database name... author add record, then srv1. database name... author record is added

If the phone field of srv1. library name... author is updated, the telphone field of srv1. library name... author is updated.

--*/

 

-- General handling steps

-- 1. Create a connection server on srv1 to operate srv2 in srv1 for synchronization

Exec sp_addrole server 'srv2', '', 'sqloledb', 'SQL Instance name or IP address of srv2'

Exec sp_add1_srvlogin 'srv2', 'false', null, 'username', 'Password'

Go

 

-- 2. Start MSDTC (Distributed Transaction Processing Service) in the srv1 and srv2 brains and set it to Automatic startup.

My computer -- control panel -- Administrative Tools -- service -- Right-click Distributed Transaction Coordinator -- Property -- start -- and set the start type to automatic start

Go

 

 

-- Create a job and call the above synchronization processing stored procedure on a regular basis.

 

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": exec p_process

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

 

 

-- 3. synchronous processing method 2: timed synchronization

 

-- Create the following synchronization processing stored procedure in srv1

Create proc p_process

As

-- Update modified data

Update B set name = I. Name, telphone = I. telphone

From srv2. database name. DBO. Author B, author I

Where B. ID = I. ID and

(B. Name <> I. Name or B. telphone <> I. telphone)

 

-- Insert new data

Insert srv2. database name. DBO. Author (ID, name, telphone)

Select ID, name, telphone from author I

Where not exists (

Select * From srv2. database name. DBO. Author where id = I. ID)

 

-- Delete Deleted Data (if needed)

Delete B

From srv2. database name. DBO. Author B

Where not exists (

Select * from author where id = B. ID)

Go