SQL Server backup and recovery practices

SQL Server backup and recovery practices

I started to back up and restore the database because a project was completed right away. I checked several methods to achieve this. My goal is to back up the database on the server, so I found several methods for implementation, which are roughly divided:

1: Back up data using SQL

At this time, SQL is divided into stored procedures and remote calls.

 

The stored procedure is as follows:Article, Is more comprehensive, in addition to restrictions, want to create a variety of functions under the master, other relatively simple

Http://blog.csdn.net/zjcxc/archive/2003/12/29/20074.aspx

 

2: The remote execution of SQL backup is actually the same step as backing up data files to a remote location, but during the backup, the remote shared folder is changed to a local one.

There are four steps for the remote end:

1) enable remote shared folder

NET ShareRegistrant forder= C:/appdatabackup

2) SQL executes the SQL Server Extension process, accesses the remote shared folder, and establishes a shared credit relationship.

Master.. xp_mongoshell 'net use // 10.10.50.212/Registrant forder Userpassword/User:Domainname/Username'

3) Back up database files to a remote shared folder

Backup database pnc1 to disk = '// 10.10.50.212/Registrant forder/Database. Bak'

4) Close and delete Shared Folders

NET ShareRegistrant forder/Delete

 

Implementation reference

Http://www.cnblogs.com/oletan/archive/2009/01/06/1370175.html

 

I have also tested the implementation here. I don't know why shared folders cannot be accessed. It may be caused by the Company domain or firewall, and my requirement is to back up data to the local server, so you can change the shared folder to a local one.

 

3: implemented through sqldmo

REFERENCE The sqldmo DLL, dynamically link the library, and use it to back up the remote database. The backup file is located on the server.

I encountered a problem during the development of this implementation, because my backup file is still relatively small, there is no prompt on the backup progress bar, I suspect it isProgramAfter a problem occurred, I checked it and found out that everyone had encountered a similar problem. The progress bar will be displayed only when the file size is greater than the number and the machine performance is poor, it seems that machine performance is not necessarily a good thing for Microsoft. Microsoft seems to like performance like 586.

You can find another method to display the progress bar to achieve backup and recovery. During restoration, all other processes connected to the server will be killed. An error will be thrown when the application is backed up and connected again. You need to establish a new connection with the server. This is something you want to pay attention to. Others are relatively simple.

The attachment contains examples of my tests and cases of using SQL to perform various database operations. They are all put together in a centralized manner. There is also an example of the connection string for encryption and decryption, in case you don't need to look for examples, there will be a lot of articles under Google.

 

: Http://download.csdn.net/source/3112211