This article is a collection of SQL Server practical operation, including SQL database MDF data file database recovery, prompts the hangs, shrink the database, compressed database, and transfer the database to new users, check the backup set, Repair the database, etc. SQL Database MDF Data File Database Recovery The MDF file is a primary data file in the database, which is the starting point of the database, pointing to other parts of the file in the database. When the computer fails to back up the database, only the .mdf data file can be attached to the current server and restore the original data. Open the query analyzer, click [OK] to connect to the server (if the SA has a password, please enter the password), as follows:
Enter the query analyzer:
Enter the file recovery command in the window, the format is: sp_attach_single_file_db @dbname = 'dbname', @physName = 'physical_name'dbname: The database name to be restored. PhysName: Physical file name. Physical_name: That is, the .mdf file path.
For example: IT2000.mdf file is restored to the current server.
Click the execution button as shown above, restore the original IT2000 database, command line prompt: The command has been executed.
SQL database recovery method
In SQL Server, since MS redesigned the storage mode of the database file, the cumbersome process of re-establishing the new devices will re-establish the database. New storage format, a database includes two files, MDF database files, and LDF log files. So we copy the two files of the database you want to back up when you reload the machine backup, and then restore it after reinstalling. This recovery method is provided in SQL Server. 1.sp_attach_db [@dbname =] 'DBNAME', [@ filename1 =] 'filename_n' Add a database to the system, specify the database name, filename_n specify the file and log file of the database name, filename_n specified the database. For example, I have a voogiya library, stop SQL Server service backup voogiya_data.mdf, voogiya_log.ldf, launch SQL Server, remove this library, then copy this two on the SQL Server Data directory, execute in Query Analyzer The following statement: exec sp_attach_db @dbname = n'voogiya ', @ filename1 = n'd: /msql7/data/voogiya_data.mdf', @ filename2 = n'd: /msql7/data/voogiya_log.ldf 'will put this The library is added to SQL Server Group. 2.sp_attach_single_file_db [@dbname =] 'dbname', [@physName =] 'physical_name' This command is the same, in Physical_name, just write the physical file name of the library The log file SQL Server will re-establish. Run the stored procedure must be executed following stored procedure: sp_detach_db @dbname = 'dbname' similar to the above example: EXEC sp_detach_db @dbname = 'voogiya' EXEC sp_attach_single_file_db @dbname = 'voogiya', @physname = 'd: /msql7/data/voogiya_data.mdf 'To pay attention to the user above the stored procedure to be in sysadmin. (1) Hanging operation When the system prompts before installing the SQL or SP patch, the system prompts have hangned installation operations, requires restart, here Often Restart Us, Solution: To HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / CONTROL / Session Manager
Delete PendingFileerenameOperations
(2) shrinking the database
- Reconstruction Index DBCC REINDEX DBCC Indexdefrag - Shrink Data and Log DBCC Shrinkdb DBCC ShrinkFile
(3) Compressed database
DBCC ShrinkDatabase (DBNAME)
(4) Transferring database to new users with existing user rights
EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'NEWNAME', 'OLDNAME' Go (5) Check Backup Set
Restore Verifyonly from disk = 'e: /dvbbs.bak'
(6) Repairing the database
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 3 parameters: --RePAIR_ALLOW_DATA_LOSS
- Perform all fixes completed by REPAIR_REBUILD, including allocation and unassigning and unassigning to correct allocation errors, structural rows or pages, and deleting corrupted text objects. These repairs may result in some data loss. Repair operations can be done under user transactions to allow users to roll back. If rollback fixes, the database still contains errors and should be recovered from backup. If an error is missing, any repair depending on the repair is missing because the repair level is provided. After the repair is complete, the database is backed up.
--RePair_fast is small, not time consuming, such as repairing additional keys in the non-aggregated index. These repairs can be completed quickly and will not have the risk of losing data.
--Repair_Rebuild Performs all fixes completed by Repair_Fast, including repair required for a longer period of time (such as rebuilding indexes). Do not have the risk of losing data when performing these fixes.
--DBCC CHECKDB ('DVBBS') with no_infomsgs, physical_only
Two methods of SQL Server log clearance
In the process of use, everyone often encounters a lot of database logs, here introduces two processing methods ...
method one
In general, the shrinkage of the SQL database does not greatly reduce the size of the database. Its main role is to shrink the log size, and this will be performed regularly to prevent the database log.
1. Setting the database mode as simple mode: Open SQL Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group -> Double click to open your server -> Double click to open your database -> Double click to open the Database directory - > Choose your database name (such as the forum database forum) -> then right click to select Properties -> Select Options -> Select "Simple" in the fault reduction mode, then press OK.
2. Right-click on the current database to see the contraction database in all tasks, the default settings in the general settings are not adjusted, and the direct point is determined.
3
Method Two
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 - you want to set the size of the log file (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 '8k 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) ' 8K Pages OR ' Convert (VARCHAR (30), (Size * 8/1024)) 'MB' from sysfiles where name = @LogicalFileName Drop Table DummyTrans Set NoCount OFF Delete Several Methods of Repeating Data in Database
During the use of the database, the repetitive data is sometimes encountered during the use of the database, and the duplicate data has caused the database part settings that cannot be set correctly ...
method one
declare @max integer, @ id integer declare cur_rows cursor local for select primary field, count (*) from table group by the 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 main field = @id fetch cur_rows @ ID, @ max end close cur_rows set rowcount 0
Method Two
There are two sense recording, one is a full repeated record, that is, all fields are repeated records, and the other is a repeated record of some of the key fields, such as the Name field repeat, and other fields are not necessarily duplicated or repeated. ignore. 1. For the first repetition, it is relatively easy to solve, using Select Distinct * from TableName
You can get the result set without repeated recording.
If the table needs to delete repetitive records (1 repeated record reserved), you can delete it as follows
Select Distinct * Into #tmp from Tablename Drop Table Tablename Select * Into Tablename from #tmp Drop Table #TMP
This repetition is that the design is not yaw in the case, and the unique index will be resolved.
2, such repetition problems typically require the first record in the repeated record, the operation method is as follows.
Suppose there is a repeated field to Name, Address, requires the unique result set of these 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 selection gets Name, address non-repeated result set (but there is more autoid field, and can write this column in the SELECT clause when writing)
Two ways to change the user of the database in the database
Everyone may often encounter a database backup to restore another machine results that cause all the tables that cannot be opened, because the time when the table is used, using the database users at the time ...
Maintain the index of the table in SQL Server
Some problems often encounter in use and create a database index, you can use some alternative methods to solve ... - Step: Check if you need maintenance, check whether scan density / scan Density is 100%
Declare @table_id int set @ Table_ID = Object_ID ('Name') DBCC Showcontig (@table_id)
- Step 2: Reconstruction of the table index DBCC DBREINDEX ('Name ", PK_ index name, 100)
- Take the first step, if you find that scan density / scan Density is still less than 100%, all indexes of the reconstruction table
- Yang Wei: It is not necessarily 100%. DBCC DBREINDEX ('Name ",' ', 100)
SQL Server patch installation FAQ Who will take a look at you :)
First, the common problem during the patch installation
If you encounter the following matches when installing the patch:
1. "The previous program creates a suspended file operation during the installation process. Before running the installer", follow these steps:
a, restart the machine, then install, if there is still this error, press Step B, enter RegeDit C in Start -> Run to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / Control / Session Manager Location D, Select File -> Pour out, save E, right-click PendingFileerenameOperation on the right window, select Delete, then confirm F, restart the installation, problem solving
If there is also the same problem, please check if there is this value in other registry, if you have any deletion.
2, in the installation of SQL Server SP3, sometimes appears: Whether you use Windows certification or mixing authentication, password errors, at this time, the SQLsp.out under the temporary directory will find the following description:
[TCP / IP SOCKETS] Specified SQL Server Not Found. [TCP / IP Sockets] ConnectionOpen ()). In fact, this is a small bug of SQL Server SP3. When installing SP3, there is no monitoring TCP / IP port, you can Follow the steps below:
1. Open the SQL Server Client Network Utility and Server Network Tool to ensure that the Name Pipe is included in the protocol enabled, and the location is in the first.
2, make sure [HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Client / ConnectTo] "DSQuery" = "DBNetLib". If not, please build yourself
3, stop MSSQL.
4, make installation.
This will be installed correctly.
One,
backup database
1. Open the SQL Enterprise Manager and click Microsoft SQL Server in the Control Station root directory.
2, SQL Server Group -> Double click to open your server -> Double-click to open the database directory.
3. Select your database name (such as the forum database forum) -> then click Tools in the Menu -> Select Backup Database.
4, the backup options Select full backup, backup in the destination to select the name point delete if the original path and name, then add it, if there is no path and name, then select Add, then specify the path and file name, specified after the specified point OK Returns the backup window, then click OK to make a backup.
two,
Restore database
1. Open the SQL Enterprise Manager and click Microsoft SQL Server in the Control Station root directory.
2, SQL Server Group -> Double-click Open your server -> Click the new database icon for the point icon, the name of the new database is taken.
3. Click the newly entered database name (such as the forum database forum) -> then click Tools in the Menu -> Select Restore Database.
4. Select from the device -> point selection device -> and select your backup file name -> Add the backup file name -> Add the backup point OK, then the equipment bar should There is a database backup file name you just selected. The backup number is 1 (if you have multiple backups to the same file, you can click on the view next to the backup number, select the latest backup in the check box. ) -> Then click on the option button next to the normal side.
5. Select to force restore on the existing database in the window, and select Options that make the database can continue to run but cannot restore other transaction logs in the recovery completion state. Restoring the database file for the intermediate part of the window To set up the installation of your SQL installation (you can also specify your own directory), the logical file name does not need to be changed, move to the physical file name to do according to your recovery machine situation Change, such as your SQL database in D: / Program Files / Microsoft SQL Server / MSSQL / DATA, then follow the directory of your recovery machine to change, and the last file name is best to change your current database name (If it is bbs_data.mdf, now the database is forum, change to forum_data.mdf), log and data files must be related to the related changes in this way (the file name of the log is * _log.ldf), Here you can freely set, provided that this directory must exist (if you can specify D: /SQLDATA/BBS_DATA.MDF or D: /SQLDATA/BBS_LOG.LDF), otherwise recovery will report an error 6, after the modification is complete, click The following determination is restored, then a progress bar, prompting the progress of recovery, the system will automatically prompt after the recovery is completed, such as the intermediate prompt error, please record the relevant error content and ask the person familiar with SQL operation, General errors are nothing more than directory error or file name duplicate or file name error or space is not enough or the database is in use, and the database is using an error. You can try to close all the SQL windows and reopen the recovery operation, if you still prompt you Used error can stop the SQL service and then restarted, as for other errors, it can be recovered after the error content is made accordingly.
three,
Shrink database
In general, the shrinkage of the SQL database does not greatly reduce the size of the database, and its main role is to shrink the log size, which should be performed on a regular basis to prevent the database log too much.
1. Setting the database mode as simple mode: Open SQL Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group -> Double click to open your server -> Double click to open your database -> Double click to open the Database directory - > Choose your database name (such as the forum database forum) -> then right click to select Properties -> Select Options -> Select "Simple" in the fault reduction mode, then press OK.
2. Right-click on the current database to see the contraction database in all tasks, the default settings in the general settings are not adjusted, and the direct point is determined.
3
four,
Set a daily automatic backup database
It is highly recommended that there is a conditional user!
1. Open Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group in the root directory -> SQL Server Group -> Double-click to open your server.
2, then click Tools above the menu -> Select the database maintenance planner.
3. Next Select data to be automatically backed up -> Next Update Data Optimization Information, here usually do not do the choice -> Next check data integrity, it is generally not selected.
4, the next step specifies the database maintenance plan, the default is 1 week backup, click Change to select the backup date after backup.
5. Next Specify the backup disk directory, select the specified directory, such as you can create a directory in the D: / DataBak, then choose to use this directory, if your database is more best to choose to choose for each The database creates a subdirectory and then selects the deletion of how many days ago. Generally set up 4-7 days, see your specific backup request, the backup file extension is generally Bak using the default. 6, Next Specify the transaction log backup plan, see your needs to do the selection -> The next step to generate the report, generally do not choose -> Next Maintenance plan history, it is best to use the default option -> One step is done.
7. After the completion, the system is likely to prompt the SQL Server Agent service that is not started, first determine the completion plan setting, then find the SQL green icon in the rightmost status bar in the desktop, double-click Open, select SQL Server Agent in the service, then Click the run arrow, select the automatic start of the service when you start the OS.
8, this time the database plan has been successfully run, he will automatically back up the settings you above.
Modify plan:
1. Open Enterprise Manager, click on Microsoft SQL Server in the Contents of the Console -> SQL Server Group -> Double click to open your server -> Management -> Database Maintenance Plan -> After opening To your set plan, you can modify or delete the operation.
Fives,
Data transfer (new database or transfer server)
In general, it is preferable to perform transfer data using backup and restore operations. In special cases, it can be transferred in a special way. This is an imported export method, and an imported export mode transfer data is that one can be used in a shrink database. In the case where the size of the database is used to reduce the size of the database, this operation is default that you have a certain understanding of the operation of SQL. If you don't understand the part of it, you can consult the relevant personnel or query online information.
1. Export all the tables, stored procedures, stored procedures into one SQL file, pay attention to the option to write index scripts and write primary keys, foreign keys, defaults, and check constraints options in the options.
2, create a new database, perform the SQL file established in the first step.
3. Import the new database into all table content in the original database with the imported database of SQL.
