Several Methods for automatic backup of MS SQL2000 database (ms SQL 2000)

I. simple and practical backup methods:

Tools and database maintenance planner in Enterprise Manager can set Regular automatic backup schedules for databases. Start the SQL Server Agent to automatically run the backup plan. The procedure is as follows:
1. Open the Enterprise Manager and click Microsoft SQL Server> SQL Server group in the root directory of the console. Double-click to open your server.
2. Click Tools in the menu above --> select Database Maintenance Scheduler
3. Next, select the data to be automatically backed up --> next, update the data optimization information. You do not need to select here --> next, check data integrity.
4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "backup every day". Click "OK ".
5. Next, specify the backup disk directory and select the specified directory. For example, you can create a directory on disk D, such as D:/databak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, the default backup file extension is Bak.
6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report, it is best to use the default option --> next step to complete
7. After completion, the system will probably prompt that the SQL Server Agent service is not started. First, click confirm to complete the plan settings, find the SQL green icon in the rightmost status bar of the desktop, and double-click it, select SQL Server Agent from the service list, and click the run arrow to automatically start the service when the OS is started.
8. enable SQL Server Agent Startup: run services. MSC and set SQLServerAgent to Automatic startup.

Modify the backup plan:
Open the Enterprise Manager and click Microsoft SQL Server> SQL Server group> double-click your server> Manage> database maintenance plan in the root directory of the console.

Ii. Remote Backup

Runtime Environment Analysis:

The specific configurations of the two machines are as follows:

Operating System: both are Windows 2000 Advanced Server

Database: SQL Server 2000

IP Address: one is 192.168.0.1 and the other is 192.168.0.100.

Requirements: regularly and automatically back up the bbsbsk database in the database 192.168.0.1 to the machine 192.168.0.100, and delete the automatically backed up bbsbsk database in 192.168.0.100 15 days ago.

Note: This operation involves advanced settings, database stored procedures, job addition, and operations of simple transact_ SQL statements in the ms SQL Server2000 Database Data Conversion service, please refer to the relevant materials and the official Microsoft website.

Procedure:

1. Open the SQL database-data conversion service-local package in the Local Machine (Win2000 ()

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/0000policy7l2vtpxyjin.jpg ');} ">

2. Right-click Create package and the screen appears.

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%2_clxgksexphxp.jpg ');} "resized =" true ">

A: First, create Microsoft ole db provider for SQL Server ()

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%3_pkiekqgwar4l.jpg ');} "resized =" true ">

Note the following two points:

The first specified server should be 192.168.0.100;

Select the second 192.168.0.100 database (here I use the master database ).

B: The following is an SQL task: undefined.

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/4244_ohd3jhls8hww.jpg ');} "resized =" true ">

A. Set the command timeout to 300;

B. the SQL statement is:

Select 'bbsback' + convert (varchar (4), datepart (yyyy, getdate () + convert (varchar (4), datepart (mm, getdate ())) + convert (varchar (4), datepart (DD, getdate ()))

Note: The red mark is used to add the year, month, and day after the database is backed up, for example, bbsback20031023.

C. Click parameter (A) to set the parameters, as shown in the interface.

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%5_xmkg6kcx991t.jpg ');} "resized =" true ">

Select output parameters, as shown in figure

The output parameter type is row value. Click Create global variable. The page is displayed.

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%6_xtckaclz5f5o.jpg ');} "resized =" true ">

The global variable name is bbsbk_crt. After confirmation, the parameter ing interface is returned.

Select bbsbk_crt as the output global variable.

OK. The first SQL task is successfully created!

C: create another SQL task: Undefined

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%7_ouon5mk1f0my.jpg ');} "resized =" true ">

The SQL statement is exec crtdatabase_bbsbk.

OK, continue!

D: create a task for copying an SQL Server Object: undefined,

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/4248_fuuvekymg7cd.jpg ');} "resized =" true ">

Note: If the source server is 192.168.0.1, select the database to be backed up (bbsbsk)

Go to the target service: parameter settings

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/00009_xqkipgixhnhi.jpg ');} "resized =" true ">

OK! Continue

E: create a dynamic property task: undefined,

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%10_x5ym8vr9tmzi.jpg ');} "resized =" true ">

OK!

F: Set the workflow status below.

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/201711_ruucj5uhvhkg.jpg ');} "resized =" true ">

Save as bbsbk_dts,

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/42412_liyvn4gnjwhz.jpg ');} "resized =" true ">

The following scheduling package

Ensure that the SQL Agent Server is started before scheduling

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/000013_jzycmrvcbgeo.jpg ');} "resized =" true ">

The operation on the local machine (192.168.0.1) is completed.

3. Open the SQL database-master database-stored procedure on the machine 192.168.0.100,

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/42414_sajkx9u8go1.jpg ');} "resized =" true ">

The stored procedure is as follows:

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%15_3qfunclmzmql.jpg ');} "resized =" true ">

The text syntax is:

Create proc crtdatabase_bbsbk

As

Declare @ SQL varchar (120)

Declare @ sql2 varchar (120)

Declare @ sql3 varchar (260)

Declare @ nalja varchar (12)

Set @ nalja = convert (varchar (4), datepart (yyyy, getdate () + convert (varchar (4), datepart (mm, getdate ())) + convert (varchar (4), datepart (DD, getdate ()))

Set @ SQL = 'create database bbsbak' + @ nalja + 'on (name =' + '''backup _ MDF '+ @ nalja + ''', filename = 'e:/bbsbsk/backup_mdf/bbsbak' + @ nalja + '. MDF '')'

Set @ sql2 = 'Log On (name = '+ 'backup _ ldf' + @ nalja +', filename = ''E: /bbsbsk/backup_ldf/bbsbak' + @ nalja + '. LDF '')'

Set @ sql3 = @ SQL + @ sql2

-- Select @ sql3

Exec (@ sql3)

Go

Note: e:/db_backup/full_backup/The db_backup and full_backup folders in this path should be created on the local edisk in advance.

Create a stored procedure again. The syntax is

Create proc deldatabase_bbsbk

As

Declare @ nalja varchar (12)

Declare @ SQL varchar (120)

Set @ nalja = convert (varchar (4), datepart (yyyy, getdate ()-15) + convert (varchar (4), datepart (mm, getdate () -15 ))

+ Convert (varchar (4), datepart (DD, getdate ()-15 ))

Set @ SQL = 'drop database nage' + @ nalja

Exec (@ SQL)

Go

Finally, create a Job Scheduling stored procedure on the agent server. The step command in the job is exec deldatabase_bbsbk.

Note that the scheduling date should be postponed by 15 days.

OK !!!

Iii. local backup

If Automatic Backup is implemented on the local machine (192.168.0.1?

The operation is as follows: directly add the job bbsbk_full_backup to the SQL Agent.

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/%16_wfvh20vmabm3.jpg ');} "resized =" true ">

The step command in the job is:

Backup database bbpbk to disk = 'f:/db_backup/full_backup/bbsbk. Bak 'with init, nounload, name = n' bbsbackk full backup', Skip, stats = 10, noformat

Note: F:/db_backup/full_backup/The db_backup and full_backup folders in this path should be created on the local machine in advance.

Add a job in the same way as the log file: bbsbk_log_backup

 

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click to view the full graph in the new window/nctrl + scroll wheel zoom in or out ';} "border = 0 onmousewheel =" Return imgzoom (this ); "onmouseover =" If (this. resized) This. style. cursor = 'hand'; "onclick =" If (! This. resized) {return false;} else {window. Open ('attachments/month_0606/000017_52wagchf5fub.jpg ');} "resized =" true ">

 

The step command in the job is:

Backup log [bbsbsk] to disk = n'f:/db_backup/log_backup/bbsbk_log.bak 'with init, nounload, name = n'bbsbaklog backup', noskip, stats = 10, noformat

Note: F:/db_backup/log_backup/The db_backup and log_backup folders in this path should be created on the local machine in advance.

All the operations have been completed. We hope you will continue to discuss them.

 

Automatic database backup and simple pasting (method 2)

============================

1. Configure the Publishing Server

On the Publishing Server, perform the following steps:

(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 distributor as the distributor or another SQL Server.

(3) [next] set the snapshot folder

The default value is // servername/d $/program files/Microsoft SQL Server/MSSQL/repldata.

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

Custom settings are 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

Ii. Create a publication

On the Publishing Server

(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]. A dialog box for defining the document is displayed, that is, 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) custom publishing attributes

Selection provided by the Wizard:

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

No create a release according to the specified Method

We recommend that you use a custom method.

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

Configuration of three subscription servers

(1) configuration of forced subscription

On the Publishing Server

[Enterprise Manager]-> [copy]-> [publish content]-> [properties]-> [subscription]-> [force create]

Then the force subscription wizard appears.

Choose subscription Server> subscription database name> set distribution Agent Scheduling> initialize subscription> Start required services

(The agent of the Publishing Server must be started.)-> complete.

(2) configuration of request subscription

On the subscription server, perform the following operations:

(1) select [request subscription] from the [tools] drop-down menu in the [copy] submenu

(2) select [request a new subscription] to open the request subscription wizard.

(3) [next] Select a registered release

(4) [next] select the database to create the subscription

(5) [next] select Anonymous subscription or signed Subscription

(6) [next] Select initialization Architecture and Data for initialization subscription

(7) [next] select the storage location of the snapshot file. Generally, the default snapshot folder of the release server is used.

** (8) [next] You can set the interval between subscription and update content for the distribution agent scheduling.

(9) [next] The system will prompt you to check the running status of the proxy service of the Publishing Server

(10) [next] complete configuration

Smile on AM

Theoretical remarks:

====================

Use Database Replication Technology to synchronize data updates

Concept of Replication

Replication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites. Copy

Data is published to multiple servers so that different server users can share the data within the permitted range of permissions. Replication Technology ensures that data is distributed in different locations from

Synchronizes updates to ensure data consistency.

The basic elements of SQL replication include

Publishing Server, subscription server, distribution server, publications, articles

How SQL replication works

SQL Server mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The publishing server stores all the data to be published

Copy the changes to the distribution server. The distribution server contains a distribution database that can receive all the changes to the data, save the changes, and distribute the changes to the subscription.

Server

SQL Server replication technology type

SQL Server provides three Replication technologies:

1. Copy a snapshot (this will be used if we stay there)

2. Transaction Replication

3. Merge and copy

As long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.

First, configure the Publishing Server

(1) Select the specified [server] Node

(2) Select the [Publish, subscribe server, and distribute] command from the [copy] sub-menu in the [tools] drop-down menu.

(3) In the dialog box that appears, click [next] And then follow the prompts until the operation is completed.

(4) After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated)

Create a publication

(1) Select the specified server

(2) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.

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

(4) 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 choose the first one, that is, Mo.

Release of recognized snapshots (the other two can help you)

(5) Click [next] system requirements to specify the type of database server that can subscribe to the release. sqlserver allows data replication between different databases, such as oracle or access. However

Here we choose to run the database server "SQL Server 2000"

(6) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.

(7) then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.

Design subscription

(1) Select the specified subscription Server

(2) select [request subscription] from the [tools] drop-down menu in the [copy] submenu

(3) Click [next] until the system prompts you to check the running status of the SQL Server proxy service. The prerequisite for performing the copy operation is that the SQL Server proxy service must be started.

.

(4) Click [finish]. Complete the subscription operation.

The above steps are actually copied successfully. But how can we know whether the replication is successful? This method can be used to quickly check whether the operation is successful. Expand under the Publishing Server

Copy -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately. Then click agent properties, click live scheduling, and set Scheduling to every day.

Occurs every minute between 0:00:00 and 23:59:59. The next step is to determine whether the replication is successful. Open D:/program files/Microsoft SQL

Server/MSSQL/repldata/UNC/xiaowangzi_database_database check whether there are some folders that use time as the file name and generate one in almost one point.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/yxl2005play/archive/2009/04/16/4083468.aspx