Database backup solution

To ensure the security of SQL Server data, the database administrator should regularly back up the database and adopt different backup database backup policies under different circumstances to maintain data security, on the other hand, the SQL Server can run smoothly. Do your best to reduce the loss caused to customers due to data damage. Concept 1) backup work plan the stability of computer software and hardware has not yet reached a very stable state, and data in the computer is still lost or destroyed. The data stored in the computer is insecure. Even if a fault-tolerant device is used, the database security cannot be guaranteed by 100%. The cost of computer hardware may be high, but computer data is more precious. Backing up data can ensure the integrity of the database in the case of various accidents. The Administrator must spend time backing up the database, but SQL SERVER also provides many automated functions. 2) backup mode database-full (total) is the content of backing up the entire database. If you back up the entire database, you need a lot of storage space to store the backup data, but the advantage is that when you restore the database, you only need to restore the entire database from a database backup to SQL SERVER. It is the basis of other backups, and must be backed up in this way before other backups are performed. Database-difference (Differential) Only backs up the data changed after the last "full" database backup is performed. Therefore, the backup time and backup space are less than the "full" mode, therefore, this method is most suitable for regular backup. Transaction Log only backs up the content of the Transaction record file. Because the Transaction Log file only records the changes made to the database after the previous database backup or Transaction Log backup, that is to say, only the database changes in a certain period of time are recorded. Therefore, a "complete" database backup file and file group must be performed before the transaction record backup: if the database content is stored in multiple files or file groups, and the database is already very large, it may take time and storage space for a complete database backup. This method can be used to back up some files or file groups of the database. Because only part of the files or file groups are backed up at a time, different backups must be performed several times to complete the backup of the entire database. The advantage of using this method is that when the destroyed data is only a file or file group in the database, you only need to restore the bad file or file group backup. 3) The backup scheme is formulated in principle to plan the database backup policy. Various backup variables must be taken into account, and the restoration job must also be taken into account. When performing a backup, consider the following. (1) how much time is there and when backup can be performed (2) how often the database changes (3) database size (4) When the database goes wrong, the user is willing to wait for the database to be restored (5) What backup methods should be used and how should they be matched (6) What storage media should be used (4) backup solution (1) full database backup and transaction log backup are relatively large for database data, but the daily changes are small. This method can be considered. Complete database backup can be performed on a regular basis, for example, weekly or monthly "full" database backup. Then, a transaction log is backed up every day or every two days for a short period of time. In this way, the backup workload is relatively low, and data can be restored to the most recent status when the backup occurs. (2) full database backup and differential backup. This mode changes the transaction log backup mode in the previous mode to differential backup. The advantage is that, during restoration, you only need to restore the last database backup, and then use the differential backup of the previous day to restore it. Unlike the transaction record backup, it takes one day to restore. The disadvantage is that in the days after comparison, the time and space required for each differential backup increase. (3) the combination of the three backup methods can be used in combination because the transaction record backup and differential backup have their own advantages and disadvantages. Data is very important. Data is backed up every one or two hours. The possible loss is only one or two hours. (4) When the database is too large and difficult to back up, you can use the file or file group backup method. You can consider saving the database separately in different files and file groups, then, the entire database is backed up in the form of files and file groups. In addition, if only some tables in the database change frequently and the other part changes little, you can save the two parts in different files and file groups, then, we use different backup frequencies to back up its content. The disadvantage of using this method to back up data is that data can be backed up only several times. (5) This method is used by the backup server to prepare another server. A full backup is performed on the original server first, and then the backup is put on the backup server for restoration, to synchronize the data on both sides, you can regularly back up the transaction logs of the original database and store the transaction logs on the backup server for restoration. When a problem occurs on the original server, you can use the backup server to connect to the network to provide services. Note: The backup and restoration of System databases are easily ignored by many users in defining database backup solutions. Although business data is not stored in the system database, many SQL Server settings are saved, if you do not back up the system database, reconfiguration of all SQL Server settings will be a nightmare when a Server failure causes system damage. DEMO solution 1: Database Backup (common solution) in the SQL Server backup window, you can schedule settings to set the current backup processing to timed automatic processing. The procedure is as follows: 1. Expand a server group. 2. Expand a Server (SQL Server instance ). 3. Expand database, right-click the database to be backed up, and select all tasks> backup database in the pop-up menu. 4. In the subsequent SQL Server backup window, set backup options. For more information, see the preceding backup solution. Click the General tab. In the scheduling tab, select the scheduling check box, and click... next to the scheduling check box 【...] Button. In the edit scheduling dialog box that appears later, set the time scheduling schedule. 5. After the configuration is complete, click OK. SQL Server performs the backup operation and automatically creates the corresponding job in SQL Server proxy> job. 6. In the SQL Server proxy> job window, you can modify or delete the backup plan defined in the database backup window as if you were dealing with common jobs. Demonstration solution 2 data transfer method (DTS) the specific solution is: 1. the master server and backup server are loaded with identical operating system and database software, as well as corresponding management software; 2. select tables, views, and stored procedures to be updated on a regular basis. If necessary, select all databases for updates. use the data conversion service import/export Wizard provided by SQL Server (taking SQL Server2000 as an example) to copy an SQL Server Object task, copy the Microsoft SQL Server Object from one SQL Server instance to another SQL Server. 4. edit repeated job scheduling to determine the frequency and time of occurrence. Generally, due to the large amount of data, it is not recommended to occur multiple times a day. The advantages of this solution are: 1. low cost, high reliability, simple operation and easy maintenance; 2. Short System conversion time. It takes only a few minutes to replace the backup system with the master server. Even in the event of an accident, the backup system will soon return to normal, with only a small amount of data lost and re-input; 3. both local and remote backup are performed at night when the network is idle, without increasing the daily network traffic and increasing the network burden. Note that if you select a transfer object, SQL Server of the same version can be backed up from an earlier version to a later version, for example, an instance of SQL Server 7.0 is backed up to an instance of SQL Server 2000. An SQL Server 2000 instance is backed up to another SQL Server 2000 instance. Backup operation steps: 1. Open Enterprise Manager, select SQLServer Server database, right-click, select "all tasks", and click "import data" or "export data ". If you operate on the master server, select export data. If you operate on the backup server, select import data. 2. after step 1, call the "Data Conversion service import/export wizard" interface and click "Next" to go to the "data source selection interface of the" DTS import/export wizard "and select" purpose ", select "create table copy or query" and select the object to be copied. When weak selection is made, select the data source based on the specific client. If it is an ole db client, select "OLEDB program provided by Microsoft for SQL Server ". Select the Server that provides the data source, and then select the database access method. You can select "use Windows Authentication" or "use SQL Server Authentication ". If you select "use SQL Server Authentication", you must enter the user that SQL Server recognizes and has the right to modify the data. Generally, you can select "SA" and its password. Finally, select the database to be copied. 3. In "create table copy and query", select "copy objects and data between SQL Server databases" and click Next. Select a specific copy object and related options .. 4. Save the scheduling and copy packages to customize the replication time and frequency. 5. After all the settings are complete, you will be prompted to list the source, purpose, method used, database, and other options, which will be confirmed by you. 6. if you want to modify the custom time after setting, in the Enterprise Manager, select a specific job through the server, database, management, SQL server proxy, and job, and call up the interface to modify the time. Now, you can easily implement regular and automatic data backup between multiple database servers in your own small and medium-sized enterprise environment. Solution 3: Database Maintenance Plan + Xcopy command (recommended) 1. Expand a Server group 2. Expand a Server instance (SQL Server instance ). 3. Expand manage, right-click Database Maintenance Plan, and select create maintenance plan from the shortcut menu. 4. in the subsequent "database maintenance plan-(Local)" dialog box, click "Next" to skip the welcome page. In the next "select database" step, define the database to be backed up. 5. click the next button to skip steps unrelated to database backup. In the "specify database backup plan" step, select the "Back up database as part of the maintenance plan" check box; click tape or disk to select a backup device. Click Change on the right of the scheduling option, you can set the maintenance schedule in the edit recurring Job Scheduling dialog box that appears later. 6. click Next. If the backup device is a disk device in step (5), the backup disk directory is specified, set the content in this step. 7. Click "Next" to go to the "specify transaction log backup plan" step. If you need to back up transaction logs, select the "Back up transaction logs as part of the maintenance plan" check box. For more information about how to back up transaction logs, see (5 )~ (6 ). 8. set other steps as needed. Finally, go to the "completing database maintenance plan wizard" step, set the "Plan Name", and click "finish" to define the database maintenance plan. 9. Complete steps (1 )~ (8) Click database maintenance plan. In the Details window on the right of the Enterprise Manager Console, you can see the defined database maintenance plan, expand SQL Server proxy> job. In the Details window on the right of job, you can see the backup job automatically created in the maintenance plan. 10. Select the attribute command through the organic maintenance plan to modify the defined maintenance plan. 11. right-click the maintenance plan and select the DELETE command to delete the defined maintenance plan. The related jobs in SQL Server proxy> job are also automatically deleted. Create a new batch file Copy. bat. The specific script is as follows: @ echo offtitle is transferring the database backup file to the remote backup server! Echo prompt: Start to verify whether there is a backup file on the server! Echo, please wait! If exist F: \ DBback \ *. bak (goto Process) else goto UnFound: Processecho prompt: the backup file is found on the server! Echo ------------------------------------------------------------------------------- echo prompt: Start to delete the original file on the remote backup server! Rem Note: The following IP addresses are the IP addresses of the remote backup server! Del \ 192.168.244.24 \ DBback/q/s/fdel \ 192.168.244.24 \ DBback \ *. */q/s/fif errorlevel 0 goto success: successecho prompt: the original file is deleted successfully! Echo ------------------------------------------------------------------------------- echo prompt: Start copying the database backup file to the Remote Backup Server \ 192.168.244.24 \ DBback! Echo, please wait! Rem remarks: Please change \ 192.168.244.1 \ DBback to the location where the backup files are stored on the server. \ 192.168.244.24 \ DBback is the storage location on the backup server! Xcopy \ 192.168.244.1 \ DBback \ 192.168.244.24 \ DBback/y/frem remarks: When the Xcopy command ends, the returned Errorlevel is 4, indicating that the disk control is insufficient. If the Xcopy command returns 0, the operation is successfully completed! If errorlevel 4 goto lowmemory if errorlevel 0 goto showmessage: lowmemoryecho warning: Check whether the disk space is insufficient!: UnFoundecho warning: the backup file cannot be found on the server, and the batch task is about to end. The backup on the server does not seem to work properly. Check whether the backup file is generated on the server! Goto exit: showmessageecho prompt: Batch task completed! Echo prompt: the database is successfully copied and backed up to the remote server \ 192.168.244.24 \ DBback! Echo press any key to end and quit! Echo ------------------------------------------------------------------------------: exitpauseecho successfully copies the PersonnelM database to the remote server \ 192.168.244.24 \ 1! Pause uses the Windows Task Plan to schedule a batch Task 1. click Start> program> attachment> System Tools> Task Plan 2. double-click Create task plan. In the displayed Task Plan wizard, click Next ". In the select Task form, select Browse ". Select the Copy. bat file to be scheduled. 3. In the following pop-up form, name the task scheduler and select the execution frequency. Select "Daily". 4. Enter the local password in the Password confirmation form. Click "Next" and select "finish ". Now the task plan is set. (If necessary, you can click the "open Advanced properties of this task when you click Finish" check box to enable advanced settings for this task !) Solution 4: Synchronous Backup Server (SCRIPT)/* This part refers to the script created by the producer * // * -- Note: The following Code demonstrates how to use the log restoration function, the data changes in the primary database are promptly fed back to the standby database. The data in the standby database can be used for query at any time, but cannot be updated (the standby database is read-only ). -- */-- First, CREATE a DATABASE (primary DATABASE) for demonstration create database Db_testON (NAME = Db_test_DATA, FILENAME = 'C: \ Db_test.mdf ') log on (NAME = Db_test_LOG, FILENAME = 'C: \ Db_test.ldf ') GO -- back up the DATABASE Db_test to disk = 'C: \ test_data.bak 'with formatgo -- RESTORE the DATABASE to a backup DATABASE (demonstrating the synchronization between the primary DATABASE and the backup DATABASE) restore database Db_test_bak from disk = 'C: \ test_data.bak' with replace, STANDBY = 'C: \ db_test_bak.ldf ', MOVE 'db _ test_DATA' TO 'C: \ Db_te St_data.mdf ', MOVE 'db _ test_LOG' TO 'C: \ db_test_log.ldf' GO -- start the SQL Agent service EXEC master .. xp_expose shell 'net start sqlserveragent ', no_outputGO -- create a job for synchronization between the master server data training and the backup server database DECLARE @ jogid uniqueidentifierEXEC msdb .. sp_add_job @ job_id = @ jogid OUTPUT, @ job_name = n' Data Synchronization process' -- create a synchronization processing step EXEC msdb .. sp_add_jobstep @ job_id = @ jogid, @ step_name = n' data synchronization', @ subsystem = 'tsql', @ command = n' -- backup log Db_test in the master database To disk = "c: \ test_log.bak "with format -- restore the log backup of the primary database in the standby database (the latest changes in the primary database of the application-in practice, the restoration jobs of the primary database backup and backup database should be on the primary server respectively and the backup server, in addition, the backup file should be stored in the shared directory that can be accessed by the master server and the slave server. restore log Db_test_bak from disk = 'C: \ test_log.bak 'with standby = 'C: \ test_log.ldf ', @ retry_attempts = 5, @ retry_interval = 5 -- create a schedule (executed once every minute) EXEC msdb .. sp_add_jobschedule @ job_id = @ jogid, @ name = n' schedule ', @ freq_type = 4, @ freq_interval = 1, @ freq_subday_type = 0x4, @ freq_sub Day_interval = 1, @ freq_recurrence_factor = 1 -- add the target server EXEC msdb. dbo. sp_add_jobserver @ job_id = @ jogid, @ server_name = n' (local) 'Go -- process through the above, the synchronization relationship between the master database and the slave database has been set up. Next, we will test whether synchronization can be implemented. CREATE a test table create table Db_test.dbo.TB_test (ID int) in the master database) GO -- Wait 1 minute 30 seconds (because the synchronization interval is set to 1 minute, so the latency is required to see the effect) waitfor delay '00: 01: 30' GO -- query the standby database to see if synchronization is successful SELECT * FROM Db_test_bak.dbo.TB_test/* -- result: ID ----------- (the number of affected rows is 0) -- */-- test successful G O -- delete all test drop database Db_test, Db_test_bakEXEC msdb .. sp_delete_job @ job_name = n'data synchronization process' GO/* ========================== ===================================== * // * -- server downtime processing instructions use this method to create database synchronization, when the primary database is unavailable (for example, the primary database is damaged or shut down for repair), you can use either of the following methods to make the standby database available. -- */-- 1. if the primary database is damaged and the latest logs cannot be backed up, you can directly use the following statement to make the standby database readable and writable (loss of all data after the last log restoration ). Restore log Db_test_bak with recovery--2. If the primary database can back up the latest LOG, you can use the following statement. -- First back up the latest transaction LOG of the primary database backup log Db_test to disk = ''c: \ test_log.bak ''with format -- then restore the latest transaction LOG in the standby database, in addition, the Standby database can be read and written (upgraded to the primary database) restore log Db_test_bak from disk = 'C: \ test_log.bak 'solution 5. Before learning about replication and subscription, we should first clarify the following concepts: replication refers to copying data from one location to one or more destinations. Due to the increasing demand for distributed database applications, SQL Server also provides the Replication function. The replication function of SQL Server basically uses the structure of "Publisher" and "Subscriber", which contains several different components. (1) Publisher: in replication, the Publishing Server is the SQL Server responsible for publishing database content to other servers (generally we regard the master Server as the Publishing Server, the image is similar to the publishing SERVER, which is like the author of a book. (2) Subscriber: the Subscriber is the SQL SERVER published by the publishing SERVER. Generally, there is only one Publishing SERVER, however, the subscription server can have multiple instances. (The image is similar to the subscription service. There are many readers who buy the same book .) (3) distribution SERVER (Distributor): apart from the distribution SERVER and subscription SERVER, the other role that SQL SERVER can play in the entire replication structure is the distribution SERVER. The distribution server is responsible for publishing and transmitting the database published by the publishing server to the servers of each subscription server. In a simple replication structure, the publisher and distributor usually use the same SQL SERVER. If it is set as the distribution SERVER's SQL SERVER, a system database named distribution will store the replication information. (4) Publishing item (Article): The data published by the publishing server is called a publishing item, and the publishing item is the replication unit during replication, A published item can be a database object such as a table or stored procedure, or a certain number of columns or items in a knowledge table. (5) Publish: A combination of Specific release items is called Publish, that is, the data that is copied by SQL SERVER during replication. When the subscription server subscribes to data from the Publishing Server, it refers to publishing, rather than Publishing items. That is, the subscription server can only receive all articles in the entire release. Instead of selecting just a few release item subscriptions, each release only has content from a single database, but we can set multiple releases for the database. (6) to meet different replication requirements, SQL SERVER provides two different subscription methods: Push and Pull. The difference is as follows: (a) Push subscribe (also called forced Subscription): The sending subscribe is actively sent and published to the subscriber by the publishing server, that is, the publishing server controls the replication operation. As long as the replication time is reached, the Publishing Server will transmit data to the subscription server no matter whether the subscription server has made any request. (B) Pull Subscribe: The request Subscription is opposite to the Subscription sent by the subscriber. The Subscriber needs to subscribe to and publish to the Publishing Server Based on the schedule, this subscription method is usually used when the number of subscription servers is large or the latest data content does not need to be retrieved in real time. Step: describes how to copy data. (Taking snapshot replication as an example) Preparations: 1. on the Publishing Server, the subscription server creates a windows user with the same name and sets the same password, as a valid user who publishes a snapshot folder-my computer-control panel-management tools-Computer Management-users and groups-Right-click a user-create a Logon windows account that belongs to the administrator Group user 2. on the Publishing Server, create a new shared directory to store the released snapshot files. For details, click "My Computer -- D: \" to create a directory named: bakPUB -- Right-click the newly created directory -- properties -- share -- select "share this folder" -- use the "permission" button to set specific user permissions, make sure that the user created in step 1 has all permissions on the folder-OK 3. set the startup user of the SQL Server Agent service (this setting is done by the publishing/subscription server) start -- program -- management tool -- service -- Right-click SQLSERVERAGENT -- properties -- log on -- select "this account" -- enter or select Step 1 Enter the password of the user created in windows logon username -- "password. 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 SQL instance -- properties -- Security -- authentication -- select "SQL Server and Windows" -- OK 5. register the enterprise manager on the Publishing Server and subscription Server -- 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 -- connection usage, select the second "SQL Server Authentication" -- next -- enter the user name and password -- next -- select the SQL Server group, or create a new group -- next -- finish 6. if you can only use an IP address and cannot use a computer name, register the server alias for it (configured on the connection end, for example, if you configure it on the subscription server, in the server name, enter the Publishing Server IP address.) start -- program -- Microsoft SQL Server -- client network utility -- alias -- add -- Network Database select "TCP/IP" -- Server alias enter SQL Server name -- connection parameter -- enter SQL Server ip address in Server name -- if you modified the SQL port, deselect "dynamically determining ports ", enter the corresponding port number ============================================ ========================================================== === official start: 1. configure the Publishing Server. select the specified [server] Node B. select the [Publish, subscribe, and distribute] command c from the [copy] submenu in the [tools] drop-down menu. in the displayed dialog box, click [next] And then follow the prompts to perform the operation until "specify snapshot folder" -- enter the directory created during preparation in "snapshot folder: \ <Server Name> \ BakPUB 1 [next] operation is complete. D. after setting the Publishing Server, the system will add a replication monitor to the tree structure of the server and generate a distribution database (distribution) Ready 2. create release. select the specified server B. select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. The system will pop up a dialog box c. select the database for which you want to create a release, and click [Create release] d. 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) e. 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. But here we choose to run "SQL SERVER 2000" Database SERVER f. click [next] and select the objects to be published (such as tables, views, stored procedures, and tables) g. then [next] until the operation is complete. After the publication is created, the database for creating the publication becomes a shared database. --------------------------------------------------------------------------- 3. design subscription. select the specified subscription server B. from the [tools] drop-down menu, select [copy] submenu [request subscription] c. follow the prompts and 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. D. Click [finish] to 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 copy under the Publishing Server -- 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. occurred, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the copy is successful. Open C: \ Program Files \ Microsoft SQL Server \ MSSQL \ REPLDATA \ and check whether there are some folders with time as the file name.

Http://zhidao.baidu.com/question/146860960.html