SQL SERVER 2008 Database management and Maintenance summary

1, the thing log is divided into small virtual log VLF, it has a number called LSN

2, the checkpoint by default every 1 minutes to save memory data, the failure point occurs after the log redo, the thing according to the submission or not roll forward or rollback.

3, in the overall main menu bar of the database to select the properties to set the data file and log file storage location, after the set up to restart the service, you can right-click the main menu restart the service or through the Windows System service restart.

4, sql2005 in the Configuration tool has a peripheral application manager, after 2008 is in the main menu right-click can also be seen.

5. SQL Server database port is 1433

6, the master database to save the user environment, accounts, variables, etc., model database control other database size, msdb is a scheduled task, tempdb is a temporary table database, restart will be lost.

7, two file types MDF and LDF

8, can be in the property file to add multi-data file multipath, such as put another disk, improve performance, when the data file suffix named Ndf,mdf the most important, through it find ndf,mdf can only for one, NDF and LDF can be more than. General raid on the line, without multiple data files, but can multiple raid groups, multiple ndf, but there is a certain risk, it is not recommended, because once the mid-term a problem, the data may be incomplete.

9, the database file growth size, do not let it grow frequently, you can grow at a time of 100M or more than 20%.

10, shrink the database, you can right click on the task in the main menu-shrink the database, you can reduce the database file size on disk space consumption. Light Delete database file content does not reduce disk consumption, generally used to automatically shrink, you can in the Properties-option to open the automatic contraction function.

11, the Mobile database files need to go offline, and then change, but need to let the master database know, can be executed through the command, more trouble. Another way is by separating and then moving the data files to other places later by attaching them back.

12, the database recovery model is divided into complete, large-capacity logs and simple mode, the complete mode can be resolved to recover the database in any case, large capacity can solve the failure time of the database, unable to recover any desired moment, simple can overwrite the inactive log, that is, deleted the submitted log.

13, Backup can choose full, large capacity, simple, and then from the task-restore database. When restoring, the database must be in an unused state, or a lock will occur, causing the recovery to fail. Or use the command backup database Weipeng to disk= ' e:\profile\weipeng.bak ' Restore Database Weipeng from disk= ' e:\profile\weipeng.ba K ', you can also restore a new database by backing up the Bak file.

14, you can create a backup device, that is, a file path into an icon, the next time each backup will not have to find the path direct device on the line. Convenience only.

15, the full backup log file, the log is from the first full backup to prepare, each subsequent is an interval of backup.

16, the database does not have a full backup, the log file is run out of coverage, so when inserting data data files will become larger, but the capacity of the log file for the disk will not change, the log file content will be constantly erased and gradually increased and then repeated the process.

17, BACKUP log is appended, in the operating system see is a file, backup log Weipeng to disk= ' C:\profile\weipeng.bak ', in the Database task-restore option, you can see the database has been backed up records. The start of the log backup after the first full backup is not after the backup but before the backup.

18, in the multi-day log backup, if the use of the mid-term log restore, if you choose to roll back the uncommitted things so that the database can be used, then after the use of other backup can not be restored. The log is restored and requires a full backup and log backup. To choose to overwrite an existing database when restoring.

19, restore the database at all times to select the target time for the desired point in time.

20. BACKUP LOG Weipeng to disk= ' C:\profile\weipeng.bak ' with No_truncate when the database is damaged

21, the database log as long as the backup after the inactive log will be automatically deleted by the system, to avoid occupying disk space, the subsequent log will be deleted from the blank place to continue to write, log files will not always grow so fast, indicating that backup log files can help reduce the growth of log files. However, this does not reduce the current log file size just to reduce growth, you can truncate the transaction log as follows, reducing the amount of log files on disk space: Backup log Weipeng with NO_LOG, and then select Task-shrink log file.

22. Differential database backups are definitely faster than differential logs. Differential backup is not an incremental backup, a differential backup is a modified data after a backup change, the amount of data is small, the enterprise can be backed by a full backup-log backup-differential backup-log backup ... Wait a minute. Hospitals can use day differential backups (which can be restored to specific points in time), differential backups at night, and, of course, slower log backups. Log backups can be set up where data is analyzed more, and differential backups are used elsewhere.

23, the differential backup command backup database Weipeng to disk= ' C\profile\weipeng.bak ' with differential, you can also back up the database to multiple hard disks by mirroring the backup device. Mirrored backups require the creation of a mirrored device.

24, how to create a database snapshot, need Baidu documentation.

25. Database service Start single user mode, add-m parameter in system service.

26, backup the master database and restore, back up backup databases master to disk= ' C:\profile\master.bak ', restore master, enter Cmd-sqlcmd-restore database Master from disk= ' C:\profile\master.bak '

27, to the database server-level settings, you should back up the master database, such as new database, new login account, modify the server properties or upgrade the database, after modification should back up the master database. Backing up the master database has nothing to do with the small database inside, just some public information.

28, SQL Agent is the necessary condition of automation, it must be opened in the service.

29, the configuration of mail services, the need for external netcom, in the management-Database Mail set its good, want to have SMS prompt, need 136 mobile mailbox, in the settings can be opened. To send messages automatically, you need to have the alert system-mail profile enabled in the Mail agent properties.

30. The Messenger service enables the database to be automatically net send to another computer. Then in cmd you can net send 192.168.1.5 Fdfas. The operator automatically sends information to the operator when the job is completed when it is automated.

31, the automatic task is usually to execute the command regularly, such as CREATE Database Weipeng to Zxta (backup device, created in the server object, used to add a full path name device) with Init each time to append.

32, if you need remote Automation backup, you need to create the same mirror account on both computers, and then found in the service SQL server-Properties-Add the Mirror account name and password. A mirrored account is two created with the same account password and added to the management group.

33, to create a maintenance plan, you can add a number of jobs to a plan, automatic execution of steps, can be easily integrated management.

34, database mirroring, the base to add a complete domain name suffix, the first to create a mirror account in the cmd operation net user Weipeng w2480211/add,net localgroup administrators Weipeng/add, The user password cannot be modified and never expires, and then the SQL Server and SQL Server Agent services in Windows System service are changed to log on and start automatically by the user who was just created.

35, to the Hosts file to add the corresponding computer name and IP address, so that they can parse each other. The recovery mode needs to be changed to full mode in database mirroring. The database name must be the same on the primary server and then copied to the server on which the mirror is to be mirrored, and a second item is selected in the option-no action is taken on the database if you restore it by backing up the database file on the Mirror service! Then select Properties-Mirroring-Configure security on the primary database-Configure the witness server, and the server name is the full domain name when you select the mirror server. When the primary database is connected locally

SQL SERVER 2008 Database management and Maintenance summary