SQL Server practical experience skill Set [III]

server| tips for maintaining indexes on tables in SQL Server

In the use and creation of database indexes often encounter some problems, here can be used in some alternative way to solve ... ww W.itbulo.cogjjwe

--First step: see if you need maintenance to see if the scan density/scan density is 100%

DECLARE @table_id int
Set @table_id =object_id (' Table name ')
DBCC SHOWCONTIG (@table_id)ww w.itbulo.cogjjwe

--Second step: Refactoring table Index
DBCC DBREINDEX (' table name ', PK_ index name, MB)ww W.itbulo.cogjjwe

--Redo the first step, if you find that scan density/scan density or less than 100% all indexes of the refactoring table
Yang Yi: Not necessarily up to 100%.
DBCC DBREINDEX (' table name ', ', ', ')ww w.itbulo.cogjjwe

SQL Server Patch Installation FAQ
Who has the problem to look at: ww w.itbulo.cogjjwe

First, the patch installation process FAQ WW W.itbulo.cogjjwe

If you encounter a similar error when installing a patch: ww w.itbulo.cogjjwe

1, during the installation process, "the previous program created a pending file operation, you must restart before running Setup," please follow the following steps to resolve: ww w.itbulo.cogjjwe

A, restart the machine, and then install, if you find the error, please follow the steps below
b, enter regedit in the start-> run
C, to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager location
d, select the file-> pour out, save
E, in the right window right click PendingFileRenameOperations, select Delete, and then confirm
F, reboot installation, problem solving ww w.itbulo.cogjjwe

If you have the same problem, please check the other registry to see if the value exists, if you delete it. ww W.itbulo.cogjjwe

2, in the installation of SQL Server SP3, sometimes appear: regardless of using Windows Authentication or mixed authentication, all appear password error situation, then look at the temporary directory of Sqlsp.out, you will find the following description:
[TCP/IP Sockets] Specified SQL Server not found.
[TCP/IP Sockets] ConnectionOpen (Connect ()).
In fact, this is a small SQL Server SP3 bug, when installing SP3, did not listen for TCP/IP ports, you can follow these steps: ww w.itbulo.cogjjwe

1. Open the SQL Server Client Network Utility and the Server Network tool to ensure that the enabled protocol contains name pipe and is in the first place. ww W.itbulo.cogjjwe

ww W.itbulo.cogjjwe

2. ensure [Hkey_local_machine\software\microsoft\mssqlserver\client\connectto]
"DSQUERY" = "dbnetlib".
If not, please build your own
3, stop MSSQL.
4, to install. ww W.itbulo.cogjjwe

This will allow for proper installation. ww W.itbulo.cogjjwe

Second, the SQL Server patch version of the check ww w.itbulo.cogjjwe

SQL Server patch version check is not as direct as Windows patch version check, a system administrator, if you do not understand the SQL Server version of the corresponding patch number, may also encounter a bit of trouble, so in this explanation, this way to identify the machine is a safe way, Does not have any effect on the system. ww W.itbulo.cogjjwe

1, with isql or SQL Query Analyzer to log on to SQL Server, if it is using isql, please enter the Isql-u sa in the cmd window, enter the password, enter; If you are using SQL Query Analyzer, start from the program, enter the SA and password (or you can use Windows authentication). ww W.itbulo.cogjjwe

2, in the isql input:
Select @ @Version;
Go ww w.itbulo.cogjjwe

or SQL Query Analyzer input (in fact, if you do not want to enter, just open the help of the About on it:))
Select @ @Version;
followed by execution;
The version information for SQL is returned as follows:
Microsoft SQL Server 2000-8.00.760 (Intel X86) Dec 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterp Rise Edition on Windows NT 5.0 (build 2195:service Pack 3)

The 8.00.760 is the version and patch number of SQL Server. The corresponding relationship is as follows: ww W.itbulo.cogjjwe

8.00.194-—————— SQL Server Watts RTM
8.00.384-—————— (SP1)
8.00.534-—————— (SP2)
8.00.760-—————— (SP3) ww w.itbulo.cogjjwe

So we can see the correct version and patch number of SQL Server. ww W.itbulo.cogjjwe

We can also use xp_msver to see more detailed information about ww W.itbulo.cogjjwe

Backup and recovery measures for SQL Server databases
The most common operation, the novice to see ... ww W.itbulo.cogjjwe

Related articles:

ww W.itbulo.cogjjwe

First, backup database ww w.itbulo.cogjjwe

1. Open SQL Enterprise Manager, click to open Microsoft SQL Server in the console root directory
2, SQL Server group--> double hit Open your server--> double-click to open the database directory
3, select your database name (such as Forum Database Forum)--> then click the Tools in the above menu--> Select Backup Database
4, Backup options Select a full backup, the backup in the destination to select the name point if the original path and name is deleted, then click Add, if there is no path and name, select Add directly, then specify the path and filename, specify the backend to return to the Backup window, and then click OK to backup ww W.itbulo.cogjjwe

Second, restore database ww w.itbulo.cogjjwe

1. Open SQL Enterprise Manager, click to open Microsoft SQL Serverww w.itbulo.cogjjwe in the console root directory

2, SQL Server group--> double hit Open your server--> point icon Bar of the new database icon, the name of the new database to take ww W.itbulo.cogjjwe

3, click on the new database name (such as Forum Database Forum)--> then click the Tools in the above menu--> select Restore Database ww w.itbulo.cogjjwe

4, in the pop-up window of the Restore option select from the device--> point Select Device--> Point add--> and then select your backup file name--> Add after the point of return, this time the device bar should appear you just select the database backup file name, The backup number defaults to 1 (if you have made multiple backups of the same file, you can click on the view next to the backup number, select the latest backup point in the checkbox)--> then click the option button next to the general above to WW W.itbulo.cogjjwe

5. Select the Force restore on the existing database in the window that appears, and select the option to keep the database running but not to restore other transaction logs in the recovery completion state. In the middle of the window to restore the database file to here to follow the installation of your SQL settings (you can also specify your own directory), the logical file name does not need to change, to move to the physical file name to change according to the machine condition you restored, such as your SQL database installed in D:\Program files\ Microsoft SQL Server\MSSQL\Data, then follow the changes that you have made to the directory where you restored the machine, and the last file name is best changed to your current database name (such as the original Bbs_ Data.mdf, now the database is forum, changed to Forum_data.mdf), log and data files are to do in this way related changes (log file name is *_ Log.ldf end of), here's the recovery directory you can set freely, provided that the directory must exist (such as you can specify D:\sqldata\bbs_data.mdf or d:\sqldata\bbs_log.ldf), otherwise recovery will be the error WW W.itbulo.cogjjwe

6, after the completion of the modification, click on the determination of the following recovery, there will be a progress bar, prompt recovery progress, the system will automatically prompt success after recovery, such as the middle of the prompt error, please record the relevant errors and ask about the SQL operation more familiar with the personnel, The general error is simply a directory error or file name duplication or file name error or lack of space or database is in use error, database is using the error you can try to close all about the SQL window and then reopen for recovery operations, If you also hint that the error you are using can stop the SQL service and then restart it, the other errors mentioned above can be restored to the ww W.itbulo.cogjjwe after they have been changed according to the wrong content.

Third, shrinking database ww w.itbulo.cogjjwe

In general, the contraction of the SQL database does not greatly reduce the size of the database, its primary role is to shrink the log size, this should be done periodically to avoid the database log too large. ww W.itbulo.cogjjwe

ww W.itbulo.cogjjwe

1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as Forum Database Forum)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save
2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine
3. After the database is finished, it is recommended to reset your database properties to standard mode, with the 1th, because the log is often the important basis for restoring the database in some unusual cases. ww W.itbulo.cogjjwe

Four, set daily automatic backup Database ww w.itbulo.cogjjwe

It is strongly recommended that a conditional user do this! ww W.itbulo.cogjjwe

1, open Enterprise Manager, in the console root directory in turn open Microsoft SQL Server-->sql Server group--> double hit Open your server ww w.itbulo.cogjjwe

2, then click the Tools in the above menu--> Select Database Maintenance Scheduler WW W.itbulo.cogjjwe

3, next select the data to be automatically backed up--> next update data optimization information, here generally do not choose--> Next check data integrity, also generally do not choose ww w.itbulo.cogjjwe

4, the next step to specify the database maintenance plan, the default is 1 weeks backup once, click on the change to select a daily backup point to determine ww w.itbulo.cogjjwe

5, next specify the backup disk directory, select the specified directory, such as you can create a new directory in D disk such as: D:\databak, and then choose to use this directory here, if your database is more than best choose to create subdirectories for each database, and then choose to delete the backup earlier than how many days ago, Generally set 4-7 days, this depends on your specific backup requirements, backup file extensions are generally bak with the default
6, next specify the transaction log backup plan, see your need to make a choice--> next to generate reports, generally do not choose--> Next maintenance Plan history, preferably with the default option--> next step to complete ww W.itbulo.cogjjwe

7, after the completion of the system is likely to prompt the SQL Server Agent service is not started, the first point to determine the completion plan, and then find the desktop rightmost status bar in the SQL Green icon, double-click Point Open, select SQL Server Agent in the service, and then click the run arrow, Select the below when starting OS automatically start service ww W.itbulo.cogjjwe

8, this time the database plan has been successfully run, he will follow your settings above the automatic backup ww w.itbulo.cogjjwe

Modify schedule:ww w.itbulo.cogjjwe

1, open Enterprise Manager, in the console root directory in order to open Microsoft SQL Server-->sql Server group--> double hit Open your server--> admin--> Database Maintenance Plan--> open to see the plan you set , you can modify or delete the Operation ww W.itbulo.cogjjwe

V. Transfer of data (new database or transfer server)ww w.itbulo.cogjjwe

Under normal circumstances, it is best to use Backup and restore operations to transfer data, in special cases, you can use the Import export method to transfer, here is the Import Export mode, import Export mode transfer data one function is to reduce (shrink) the size of the database in the case of shrinking the database. This operation by default for you have a certain understanding of the operation of the SQL, if the part of the operation does not understand, you can consult the dynamic network related personnel or query online data ww w.itbulo.cogjjwe

1, the original database of all the tables, stored procedures exported into a SQL file, the time to export note in the option to write index script and write primary key, foreign key, default value and check Constraint script options ww w.itbulo.cogjjwe

2. Create a new database, execute the SQL file created in the first step of the new database ww w.itbulo.cogjjwe

3, with SQL import and export way, to the new database to import all the table content in the original database ww w.itbulo.cogjjwe

ww W.itbulo.cogjjwe