7 MSSQL Database Backup and Recovery

0 MSSQL Database backup

1 SQLAgent Configuration
2 Setting connection Properties
3 Enter the SA account password
4 SQL Backup Script configuration
5 Generate SQL Full backup script
6 Generating SQL Differential backup script
7 Modifying the SQL differential backup script
8 SQL Full-scale backup strategy
9 SQL Differential Backup policy
Full-scale SQL backup
One-to-one SQL differential backup

MSSQL Database Recovery
13 Generating Recovery scripts
14 Backup script Modifications
15 performing a recovery operation

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

Chapter I. MSSQL BACKUP

1. Make sure the MSSQL database can be used normally

2, install the NBU client software on Windows, install the client software will automatically install the MSSQL agent, installation and restart.

3. Set the MSSQL user name and password in the Set server Connections properties property of the client's NetBackup MSSQL clients tool, enter the user name and password, press apply, and then close.

4, generate backup MSSQL various types of backup scripts (full backup/differential backup/log backup, etc.)

On the client open the NetBackup MSSQL Client tool, select Backup SQL server objects

Select the database/backup type that you want to back up, choose Save Save, and then click Backup to save the backup script.

5, set up backup strategy of MSSQL

Select a policy type of ms-sql-server

Add a backup schedule with the backup type selected as automatic backup. Default-application-backup can not be deleted, it is actually back up the MSSQL plan, and its retention data time retention changed to automatic backup retention the same.

Add Client

Add a backup script created by the client to add only one script.

6. Perform a manual backup test

Chapter II SQL Server recovery

When recovering a database, you do not have to stop the MSSQL instance service, and you do not have to remove other normal databases.

1, in the server-side backup/archive and Restore tool specify NetBackup machines and policy type set the source and destination machine, the recovery policy type is Ms-sql-server

2, on the server side with NetBackup MSSQL client to restore MSSQL

Select Restore SQL Server objects

Select Database

Select the database you want to recover, only one database at a time. Select Use Replace option (can replace existing database files) and recovered (to automatically recover the database, so that the database is not grayed out after the recovery is complete, the database becomes unavailable), the database check is None, select the launch After immediately, press Restore to recover. You can see the progress of the recovery job in Activity monitor.

This is a native restore restore, if you need to use a script to restore, the first to make a script, and then modify the script, when making the script, the launch immediately changed to save; And then modify the script (because this document was written by another engineer, I was fully replenished in my test environment, so I saw a different name for the recovery database)

Attention:

NBU when recovering master datbase, it is not necessary to stop and boot the MSSQL instance into single user mode, but choose Use Replace option (which can replace the existing database file) and recovered (the database is automatically recovered. This will not find the database grayed out after the recovery is complete and the database becomes unavailable. During the recovery process, the MSSQL instance is NBU shut down and then started.

If the entire MSSQL database crashes, restore the master database and restore the other normal databases first.

When recovering a normal database, if the database is grayed out after the recovery is complete and "loading" is displayed, the user is required to enter into the SQL parser with the following command: Restore Database Database-name with recovery

Chapter III SQL Server recovery for different machines

Recovery prerequisites: (These three points are very important, remember, otherwise, recovery will have a variety of problems)

1. Confirm the source host and destination host, install the same operating system, version and patch are the same;

2. Verify that the same version of the SQL database for the source and destination hosts includes the patch

3. Verify that the NBU version of the server server, source host, and destination host is the same as the patch

Log Analysis: General client Backup method, the log will be saved under Dbclient, and Bphdb is the log generated after running the script;

General VMware Backup logs are stored under, Bpvmutil,bpvmreq and Bpfis, where Bpfis is the log of the snapshot; Bpvmutil is a log of policy configuration and restore; Bpvmreq is the log at the time of recovery

The procedure for the machine recovery is similar to the above, but it is necessary to specify two points:

1 ) allow for heterogeneous recovery ( operating master server on NBU software server)

Configure the host to allow redirection recovery (that is, allow for a different machine recovery) (Linux environment)

#su –root

#mkdir –p/usr/openv/netbackup/db/altnames/

#cd/usr/openv/netbackup/db/altnames/

#touch no.restrictions

Configure the host to allow redirection recovery (that is, allow for heterogeneous recovery) (Windows environment)

cd C:\Program Files\openv\netbackup\db\altnames\

2) allow the client to recover, set the following (Operation master server on NBU software server):
On a Windows master server, run the command:
<install_path>\veritas\netbackup\bin\admincmd\bpgetconfig

Disallow_client_list_restore = YES
Disallow_client_restore = YES

Be sure to change disallow_client_list_restore and Disallow_client_restore to No
Disallow_client_list_restore = NO
Disallow_client_restore = NO

Otherwise it will be reported ORA-19507 ORA-27029 ORA-19511 error

3) make a link to the NBU library in the $oracle_home/lib directory (operation on the recovery machine)

CD $ORACLE _home/lib
Ln-s/usr/openv/netbackup/bin/libobk.so.

Otherwise it will report ORA-19554 ORA-27211 error

7 MSSQL Database Backup and Recovery