SQL Server practical experience and tips

This article is a collection of SQL Server practical operations tips, including SQL database MDF data file database recovery, during installation, the system prompts pending operations, shrinking databases, compressing databases, transferring databases to new users with existing user permissions, checking backup sets, and restoring databases.

SQL database MDF data file database recovery

The MDF file is a major data file in the database. It is the starting point of the database and points to other parts of the database file. When the computer fails and the database cannot be backed up, you can only use SQL commands to attach the. MDF data file to the current server to restore the original data. Open the query analyzer and click OK to connect to the server (enter the password if SA has a password), as shown below:

 

Go to the query Analyzer:

In the window on the right, enter the file recovery command in the format:
Sp_attach_single_file_db @ dbname = 'dbname'
, @ Physname = 'physical _ name'
Dbname: name of the database to be restored.
Physname: physical file name.
Physical_name: Specifies the. MDF file path.

For example, the it2000.mdf file is restored to the current server.

 

Click the execution button shown above to restore the original it2000 database. The command line prompts that the command has been executed.

SQL database restoration method

In SQL Server, Ms re-designs the storage mode of database files, removing the tedious process of creating a device and then creating a database. The new storage format. A database contains two files: MDF database files and LDF log files. So we can copy the two files of the database you want to back up when reinstalling the machine, and then restore them.
SQL Server provides the restoration method for stored procedures.
1. sp_attach_db [@ dbname =] 'dbname', [@ filename1 =] 'filename _ N'
Add a database to the system, specify the database name in dbname, and specify the database file and log file in filename_n. For example, if I have a voogiya database, stop the SQL Server service from backing up voogiya_data.mdf, voogiya_log.ldf, start SQL Server, and delete the database, then copy the two files to the SQL server data directory and execute the following statement in query Analyzer:
Exec sp_attach_db @ dbname = n' voogiya ',
@ Filename1 = n'd:/MSSQL7/data/voogiya_data.mdf ',
@ Filename2 = n'd:/MSSQL7/data/voogiya_log.ldf'
The database will be added to the SQL server group.
2. sp_attach_single_file_db [@ dbname =] 'dbname ',
[@ Physname =] 'physical _ name'
This command is similar to the above function. In physical_name, you only need to write the physical file name of the database, and the SQL server of the log file will be re-created. To run this stored procedure, you must first execute the following stored procedure:
Sp_detach_db @ dbname = 'dbname'
Take the preceding example as an example:
Exec sp_detach_db @ dbname = 'voogiya'
Exec sp_attach_single_file_db @ dbname = 'voogiya ',
@ Physname = 'd:/MSSQL7/data/voogiya_data.mdf'

Note that the user executing the above stored procedure should be in SysAdmin.

(1) pending operations

When installing the SQL or SP patch, the system prompts that a pending installation operation is required. Restart is often useless. solution:

To HKEY_LOCAL_MACHINE/system/CurrentControlSet/control/Session Manager

Delete pendingfilerenameoperations

(2) shrinking the database

-- Re-Indexing DBCC reindex DBCC indexdefrag -- Shrink data and logs DBCC shrinkdb DBCC shrinkfile

(3) compressing the database

DBCC shrinkdatabase (dbname)

(4) Transfer the database to a new user with existing User Permissions

Exec sp_change_users_login 'Update _ one', 'newname', 'oldname' Go

(5) Check the backup set

Restore verifyonly from disk = 'e:/dvbbs. Bak'

(6) database Restoration

Alter database [dvbbs] Set single_user Go DBCC checkdb ('dvbbs ', repair_allow_data_loss) with tablock Go Alter database [dvbbs] Set multi_user Go -- Checkdb has three parameters: -- Repair_allow_data_loss

-- Execute all the repairs completed by repair_rebuild, including allocating and unassigning rows and pages to correct the allocation errors, structure row or page errors, and deleting corrupted text objects. These fixes may cause some data loss. The repair operation can be completed under the user transaction to allow the user to roll back the changes. If the rollback is fixed, the database will still contain errors and the backup should be restored. If an error fix is missing due to the severity of the severity level provided, it will omit any fix that depends on the fix. After the restoration, back up the database.

-- Repair_fast Performs small and time-consuming repair operations, such as fixing the additional keys in non-clustered indexes. These repairs can be completed quickly without the risk of data loss.

-- Repair_rebuild: execute all the repairs completed by repair_fast, including repairs that require a long period of time (such as re-indexing ). There is no risk of data loss when performing these repairs.

-- DBCC checkdb ('dvbbs ') with no_infomsgs, physical_only

Two Methods for clearing SQL server logs

During usage, we often encounter very large database logs. Here we introduce two solutions ......

Method 1

In general, the contraction of the SQL database does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed regularly to avoid excessive database logs.

1. Set database mode to simple mode: Open the SQL Enterprise Manager, in the root directory of the console, open Microsoft SQL Server --> SQL Server group --> double-click your server --> double-click to open the database directory --> select your database name (such as the Forum database) forum) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and click OK to save.

2. Right-click the current database to view the shrinking database in all tasks. Generally, the default settings in the database do not need to be adjusted. Click OK directly.

3. After the database is shrunk, we recommend that you set your database attributes to the standard mode. The operation is the same as the first one, because logs are often an important basis for database recovery in case of exceptions.

Method 2

Set nocount on Declare @ logicalfilename sysname, @ Maxminutes int, @ Newsize int Use tablename -- Name of the database to be operated Select @ logicalfilename = 'tablename _ log', -- Log File Name @ Maxminutes = 10, -- limit on time allowed to wrap log. @ Newsize = 1 -- the size of the log file you want to set (m)

-- Setup/initialize Declare @ originalsize int Select @ originalsize = size From sysfiles Where name = @ logicalfilename Select 'original size of '+ db_name () + 'Log is' + Convert (varchar (30), @ originalsize) + '8 K pages or '+ Convert (varchar (30), (@ originalsize * 8/1024) + 'mb' From sysfiles Where name = @ logicalfilename Create Table dummytrans (Dummycolumn char (8000) not null)

Declare @ counter int, @ Starttime datetime, @ Trunclog varchar (255) Select @ starttime = getdate (), @ Trunclog = 'backup log' + db_name () + 'with truncate_only' DBCC shrinkfile (@ logicalfilename, @ newsize) Exec (@ trunclog) -- Wrap the log if necessary. While @ maxminutes> datediff (MI, @ starttime, getdate () -- time has not expired And @ originalsize = (select size from sysfiles where name = @ logicalfilename) And (@ originalsize * 8/1024)> @ newsize Begin -- outer loop. Select @ counter = 0 While (@ counter <@ originalsize/16) and (@ counter< 50000 )) Begin -- Update Insert dummytrans values ('fill log ') Delete dummytrans Select @ counter = @ counter + 1 End Exec (@ trunclog) End Select 'final size of '+ db_name () + 'Log is' + Convert (varchar (30), size) + '8 K pages or '+ Convert (varchar (30), (size * 8/1024) + 'mb' From sysfiles Where name = @ logicalfilename Drop table dummytrans Set nocount off

Several Methods for deleting duplicate data in a databaseDuring database usage, due to program problems, duplicate data may occur, which leads to incorrect database settings ......

Method 1

Declare @ Max integer, @ ID integer Declare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)> 1 Open cur_rows Fetch cur_rows into @ ID, @ Max While @ fetch_status = 0 Begin Select @ max = @ max-1 Set rowcount @ Max Delete from table name where primary field = @ ID Fetch cur_rows into @ ID, @ Max End Close cur_rows Set rowcount 0

Method 2

There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate name fields, other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easier to solve.

Select distinct * From tablename

You can get the result set without repeated records.

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

Select distinct * into # TMP from tablename Drop table tablename Select * into tablename from # TMP Drop table # TMP

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows.

Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.

Select Identity (INT, 1, 1) as autoid, * into # TMP from tablename Select min (autoid) as autoid into # tmp2 from # TMP group by name, autoid Select * from # TMP where autoid in (select autoid from # tmp2)

The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)

Two Methods for changing the user of a table in the database

You may often encounter a problem where all the tables cannot be opened due to the result of restoring a database backup to another machine. The reason is that the current database user was used during table creation ......

Maintain the index of a table in SQL Server

Some problems are often encountered in the use and creation of database indexes. here we can use some alternative solutions...
-- Step 1: Check whether maintenance is required and whether the scan density/scan density is 100%

Declare @ table_id int
Set @ table_id = object_id ('table name ')
DBCC showcontig (@ table_id)

-- Step 2: reconstruct the table Index
DBCC dbreindex ('table name', pk_index name, 100)

-- Redo the first step. If the scan density/scan density is less than 100%, rebuild all the indexes in the table.

-- YANG Jing: Not necessarily 100%.
DBCC dbreindex ('table name', '', 100)

SQL Server patch installation FAQ
Let's take a look at the problem :)

I. FAQs during patch installation

If the following error occurs during patch installation:

1. During the installation process, the following steps show:

A. Restart the machine and install it again. If the error persists, follow these steps:
B. Enter Regedit in start-> running
C. Go to the HKEY_LOCAL_MACHINE/system/CurrentControlSet/control/Session Manager location.
D. Select File-> inverted, save
E. Right-click pendingfilerenameoperations in the right window, select Delete, and then confirm
F. Restart the installation and solve the problem.

If the problem persists, check whether the value exists in other registries. If yes, delete it.

2. When installing SQL Server SP3, sometimes there will be a Password error in both Windows Authentication and Hybrid Authentication. In this case, check the sqlsp in the temporary directory. 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 bug in SQL Server SP3. When installing SP3, you do not listen to the TCP/IP Port. You can follow these steps:

1. Enable the SQL Server Client Network utility and server network tool to ensure that the enabled protocol contains name pipe and is placed first.

2. Ensure that [HKEY_LOCAL_MACHINE/software/Microsoft/MSSQLServer/client/connectto]
"Dsquery" = "dbnetlib ".
If not, create your own

3. Stop MSSQL.

4. Install the SDK.

In this way, you can install it correctly.

1. Back up the database 1. Open SQL Enterprise Manager and click Microsoft SQL server in the root directory of the console. 2. SQL Server group --> double-click to open your server --> double-click to open the database directory. 3. Select Your Database Name (such as forum database Forum) --> click tools in the menu above --> select backup database. 4. Select full backup as the backup option. If there is a path or name for the backup in the target project, click Delete and then add. If there is no path or name, select Add directly, specify the path and file name, click OK to return to the backup window, and click OK to back up. 2. Restore the database. 1. Open SQL Enterprise Manager and click Microsoft SQL server in the root directory of the console. 2. SQL Server group --> double-click to open your server --> click the new database icon in the icon bar to create a database. 3. Click the new database name (such as the Forum database Forum) --> then click the tool in the menu above --> select recover database. 4. In the displayed window, select "restore from device"> "select device"> "add"> "select your backup file name"> "add" and click "OK" to return, at this time, the device column should display the database backup file name you just selected. The default backup number is 1. (If you have backed up the same file multiple times, click View next to the backup number, select the latest backup in the check box and click OK) --> then click the option button next to the general button above. 5. In the displayed window, select force restore on the existing database, and select the option to enable the database to continue running but not to restore other transaction logs in the recovery completion status. To restore the database file in the middle of the window, you need to set it according to your SQL installation (you can also specify your own directory). The logical file name does not need to be changed, the file name to be moved to the physical server must be changed based on the recovered machine. For example, if your SQL database is installed in D:/program files/Microsoft SQL Server/MSSQL/data, then modify the directory of the recovered machine according to the change, and the final file name should be changed to your current database name (for example, if it was originally bbs_data.mdf, the current database is forum, change it to forum_data.mdf). The log and data files must be modified in this way (the log file name is * _ log. LDF). You can set the recovery directory as needed, provided that the directory must exist (for example, you can specify D:/sqldata/bbs_data.mdf or D:/sqldata/bbs_log.ldf ), otherwise, an error will be reported. 6. After the modification is complete, click "OK" below to restore. a progress bar will appear, prompting you to recover. After the restoration is completed, the system will automatically prompt success. If an error is reported in the middle, please record the relevant error content and ask people familiar with SQL operations, the common error is nothing more than a directory error, duplicate file names, incorrect file names, insufficient space, or an error in the database being used, you can close all SQL windows and re-open them to restore the database. If an error is prompted, stop the SQL Service and restart it, as for the above other errors, you can change the content of the errors to restore them. Iii. Database shrinking in general, SQL database shrinking does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed on a regular basis to avoid excessive database logs. 1. Set database mode to simple mode: Open the SQL Enterprise Manager, in the root directory of the console, open Microsoft SQL Server --> SQL Server group --> double-click your server --> double-click to open the database directory --> select your database name (such as the Forum database) forum) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and click OK to save. 2. Right-click the current database to view the shrinking database in all tasks. Generally, the default settings in the database do not need to be adjusted. Click OK directly. 3. After the database is shrunk, we recommend that you set your database attributes to the standard mode. The operation is the same as the first one, because logs are often an important basis for database recovery in case of exceptions. 4. Set daily automatic database backup. We strongly recommend that users with conditions perform this operation! 1. Open Enterprise Manager, open Microsoft SQL Server> SQL Server group> double-click in the root directory of the console to open your server. 2. Click Tools> select database maintenance scheduler from the menu above. 3. Next, select the data to be automatically backed up --> next, update the data optimization information. You do not need to select here --> next, check data integrity, or choose not. 4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "Back up every day". Then click "OK. 5. Next, specify the backup disk directory and select the specified directory. For example, you can create a directory on disk D, such as D:/databak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, generally, the backup file extension is the default value for Bak. 6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report, it is best to use the default option --> next. 7. After completion, the system will probably prompt that the SQL Server Agent service is not started. First, click confirm to complete the plan settings, find the SQL green icon in the rightmost status bar of the desktop, and double-click it, select SQL Server Agent from the service list, and click the run arrow to automatically start the service when the OS is started. 8. At this time, the database plan has been successfully run and will be automatically backed up according to the above settings. Modify plan: 1. Open the Enterprise Manager, in the root directory of the console, choose Microsoft SQL Server> SQL Server group> double-click your server> Manage> database maintenance plan. plan, you can modify or delete an object. 5. Data Transfer (new database or transfer server) generally, it is best to use backup and restoration operations to transfer data. In special circumstances, you can use the import/export method for data transfer. Here we will introduce the import/export method. One function of data transfer is to reduce (contract) when the database contraction is invalid) the size of the database. By default, this operation gives you a certain understanding of SQL operations. If you do not understand some of these operations, you can consult the relevant staff of the mobile network or query online information. 1. Export all tables and stored procedures of the original database into an SQL file, when exporting data, make sure to select the options to compile the index script and write the primary key, foreign key, default value, and check the constraints script. 2. Create a database and execute the SQL File Created in step 1 for the new database. 3. Use SQL to import and export all the table content of the original database to the new database.