SQL Server practical experience tips Set [II]

server| tips several ways to delete duplicate data in a database
During the use of the database, due to the problems of the program, there are sometimes duplicate data, which causes the database to be set up incorrectly. Itbulo. C omozh1q

Method a Itbulo. C omozh1q

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 main field = @id Fetch cur_rows into @id, @max End Close Cur_rows SET ROWCOUNT 0itbulo. C omozh1q

Itbulo. C omozh1q

Itbulo. C omozh1q

Method two Itbulo. C omozh1q

There are two duplicates of the record, one is a completely duplicate record, that is, all the fields are duplicate records, the second is some key fields duplicate records, such as the Name field repeat, and other fields do not necessarily repeat or repeat can be ignored.

1, for the first duplication, easier to solve, using Itbulo. C omozh1q

SELECT DISTINCT * FROM tablenameItbulo. C omozh1q

Itbulo. C omozh1q

You can get a result set without duplicate records. Itbulo. C omozh1q

If the table needs to delete duplicate records (1 for Duplicate records), you can delete Itbulo. C omozh1q as follows

SELECT DISTINCT * into #Tmp tablename DROP TABLE TableName SELECT * INTO TableName from #Tmp drop table #Tmpitbulo. C omozh1q

This duplication occurs because the table is poorly designed and can be resolved by adding a unique index column. Itbulo. C omozh1q

2. This type of repetition usually requires the first record in the duplicate record to be retained, as follows
Assuming that there is a duplicate field name,address, the result set of the two fields is required Itbulo. C omozh1q

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)Itbulo. C omozh1q

The last select gets the name,address result set (but one more autoid field, which can be written in the SELECT clause to omit this column) Itbulo. C omozh1q

Two ways to change the user of a table in a database
You may often encounter a database backup restored to another machine the result is that all tables cannot be opened, because the database user was used when the table was built ... Itbulo. C omozh1q

Itbulo. C omozh1q

--Change a table Itbulo. C omozh1q

EXEC sp_changeobjectowner ' tablename ', ' dbo 'itbulo. C omozh1q

--store changes to all tables Itbulo. C omozh1q

CREATE PROCEDURE dbo. User_changeobjectownerbatch @OldOwner as NVARCHAR (128), @NewOwner as NVARCHAR (128) AsItbulo. C omozh1q DECLARE @Name as NVARCHAR (128) DECLARE @Owner as NVARCHAR (128) DECLARE @OwnerName as NVARCHAR (128)Itbulo. C omozh1q DECLARE Curobject CURSOR for Select ' Name ' = name, ' Owner ' = user_name (UID) From sysobjects where user_name (UID) = @OldOwner Order BY nameItbulo. C omozh1q OPEN Curobject FETCH NEXT from Curobject into @Name, @Owner while (@ @FETCH_STATUS =0) BEGIN If @Owner = @OldOwner Begin Set @OwnerName = @OldOwner + '. ' + RTrim (@Name) EXEC sp_changeobjectowner @OwnerName, @NewOwner End --Select @name, @NewOwner, @OldOwneritbulo. C omozh1q FETCH NEXT from Curobject into @Name, @Owner EndItbulo. C omozh1q Close Curobject Deallocate curobjectitbulo. C omozh1q Goitbulo. C omozh1q

Direct loop write data in SQL Server
There's nothing left to say, see it for yourselves, sometimes it's useful Itbulo. C omozh1q

DECLARE @i int Set @i=1 While @i<30 Begin INSERT INTO Test (userid) VALUES (@i) Set @i=@i+1 EndItbulo. C omozh1q

Itbulo. C omozh1q

Itbulo. C omozh1q

No database log File Recovery database method two
Incorrect deletion of database log files or other causes damage to the database log itbulo. C omozh1q

Method a Itbulo. C omozh1q

1. Create a new database with the same name Itbulo. C omozh1q

2. Then stop SQL Server (be careful not to detach the database)Itbulo. C omozh1q

3. Overwrite the new database Itbulo. C omozh1q with the data file of the original database.

4. Restart SQL Serveritbulo. C omozh1q

5. When you open Enterprise Manager, there will be doubt, regardless, execute the following statement (note modify the database name)Itbulo. C omozh1q

6. After the completion of the general access to the data in the database, at this time, the database itself generally have problems, the solution is to use
The database script creates a new database and guides the data into the line. Itbulo. C omozh1q

use MASTER Go itbulo. C omozh1q sp_configure ' ALLOW UPDATES ', 1 reconfigure with OVERRIDE Goitbulo. C omozh1q UPDATE sysdatabases SET STATUS =32768 WHERE name= ' suspect database name ' Goitbulo. C omozh1q sp_dboption ' suspect database name ', ' Single user ', ' true ' Goitbulo. C Omozh1q DBCC CHECKDB (' Suspect database name ') Goitbulo. C omozh1q Update sysdatabases set Status =28 where name= ' suspect database name ' Goitbulo. C omozh1q sp_configure ' allow updates ', 0 reconfigure with override Go itbulo. C omozh1q sp_dboption ' suspect database name ', ' Sin GLE user ', ' false ' Goitbulo. C omozh1q

Itbulo. C omozh1q

Itbulo. C omozh1q

Method two Itbulo. C omozh1q

The cause of things Itbulo C omozh1q

Yesterday, the system administrator told me that there was not enough disk space on one of our internal application databases. I noticed that the database event log file Xxx_data.ldf file has grown to 3GB, so I decided to shrink the log file. After shrinking the database and so on, I made the biggest and most stupid mistake since I entered the industry: I accidentally deleted this log file! I later read all the articles on database recovery and said, "It's important to keep the database log file in any case," and even Microsoft even has a KB article about how to recover the database by only log files. I really don't know what I was thinking at that time?! Itbulo. C omozh1q

It's broken! The database is not connected, and Enterprise Manager says "(suspect)" next to it. And most of all, this database has never been backed up. The only thing I can find is another database server that was migrated six months ago, but the application is available, but there are many fewer records, tables, and stored procedures. I wish it was just a nightmare! Itbulo. C omozh1q

Recovery steps with no effect
Attaching a database
_rambo talked about it. When there is no active log in the deleted log file, you can do so to recover:itbulo. C omozh1q

1, separate the suspect database, you can use the sp_detach_db
2, attach the database, you can use sp_attach_single_file_dbitbulo c omozh1q

However, unfortunately, after execution, SQL Server challenged the data file and log file, so the database data file cannot be attached. Itbulo. C omozh1q

DTS Data Export
No, unable to read the XXX database, DTS Wizard reported "initialization context error". Itbulo. C omozh1q

Emergency mode
The pleasant Red Childe said that there is no log for recovery, you can do this:itbulo. C omozh1q

1, set the database to emergency mode Itbulo. C omozh1q

2, re-establish a log file Itbulo. C omozh1q

3, restart SQL Server itbulo. C omozh1q

4, the application database is set to single user mode Itbulo. C omozh1q

5, do DBCC CHECKDBitbulo. C omozh1q

6, if there is no big problem can be changed to the database status, remember not to forget the system table to change the option to turn off Itbulo. C omozh1q

I practiced, the application of the data file to remove the database, to re-establish a database xxx with the same name, and then stop the SQL service, the original data file back to cover. After, follow the steps of satisfying red Childe. Itbulo. C omozh1q

However, it is also regrettable that the other steps have been very successful in addition to step 2nd. Unfortunately, after restarting SQL Server, this application database is still suspect! Itbulo. C omozh1q

Itbulo. C omozh1q

But, to my relief, I was able to select the data and give me a big breath. Only when the component uses the database, the report says: "Error:-2147467259, failed to run BEGIN TRANSACTION in database ' XXX ' because the database is in a bypass recovery mode." "Itbulo. C omozh1q

All steps in the final successful recovery
Set Database as emergency mode
Stop SQL Server service;itbulo. C omozh1q

Removing the data file Xxx_data.mdf of the application database;itbulo. C omozh1q

Re-establish a database with the same name xxx;itbulo. C omozh1q

Stop the SQL service;itbulo. C omozh1q

Overwrite the original data file again;Itbulo. C omozh1q

Run the following statement to set the database to emergency mode;Itbulo. C omozh1q

Run the use Masteritbulo. C omozh1q Goitbulo. C omozh1q sp_configure ' allow updates ', 1itbulo. C omozh1q Reconfigure with overrideItbulo. C omozh1q Go "itbulo. C omozh1q

Itbulo. C omozh1q

Execution result:itbulo. C omozh1q

DBCC execution completed. If DBCC prints an error message, contact your system administrator. Itbulo. C omozh1q

Changed configuration option ' allow updates ' from 0 to 1. Please run the RECONFIGURE statement to install. Itbulo. C omozh1q

Then run "Update sysdatabases set status = 32768 where name = ' XXX '"itbulo. C omozh1q

Execution result:itbulo. C omozh1q

(The number of rows affected is 1 rows)Itbulo. C omozh1q

Restart the SQL Server service;itbulo. C omozh1q

Run the following statement to set the application database to single user mode;itbulo. C omozh1q

Run "sp_dboption ' XXX", ' Single user ', ' true 'itbulo. C omozh1q

Execution result:itbulo. C omozh1q

The command has completed successfully. Itbulo. C omozh1q

u do DBCC CHECKDBitbulo. C omozh1q

Run the DBCC CHECKDB (' XXX ')Itbulo. C omozh1q

Itbulo. C omozh1q

Execution result:itbulo. C omozh1q

DBCC results for ' XXX '. Itbulo. C omozh1q

DBCC results for ' sysobjects '. Itbulo. C omozh1q

The object ' sysobjects ' has 273 rows, which are in 5 pages. Itbulo. C omozh1q

DBCC results for ' sysindexes '. Itbulo. C omozh1q

The object ' sysindexes ' has 202 rows, which are in 7 pages. Itbulo. C omozh1q

DBCC results for ' syscolumns '. Itbulo. C omozh1q

......... Itbulo. C omozh1q

U run the following statement to turn off the modification options of the system table;Itbulo. C omozh1q

Run "sp_resetstatus" XXXitbulo. C omozh1q Goitbulo. C omozh1q sp_configure ' allow updates ', 0itbulo. C omozh1q Reconfigure with overrideItbulo. C omozh1q Go "itbulo. C omozh1q

Itbulo. C omozh1q

Execution result:itbulo. C omozh1q

Before updating the entries for database ' XXX ' in sysdatabases, mode = 0, state = 28 (state suspect_bit = 0),itbulo. C omozh1q

No rows in the sysdatabases were updated because the mode and state were reset correctly. No errors, no changes made. Itbulo. C omozh1q

DBCC execution completed. If DBCC prints an error message, contact your system administrator. Itbulo. C omozh1q

Changed configuration option ' Allow updates ' from 1 to 0. Please run the RECONFIGURE statement to install. Itbulo. C omozh1q

Re-establish another database Xxx.lostitbulo. C omozh1q

DTS Export Wizard
Run the DTS Export Wizard;Itbulo. C omozh1q

Copy source Select Emergencymode database xxx, import to Xxx.lostItbulo. C omozh1q

Select "Copy objects and data between SQL Server Databases", try it a few times, it doesn't seem to work, just copy it. All table structures, but no data, no views and stored procedures, and the DTS Wizard finally reports replication failures;Itbulo. C omozh1q

So the last option is to copy tables and views from the source database, but it turns out that you can always copy only a subset of the table records;Itbulo. C omozh1q

Then select "Use a query to specify the data to be transferred", which table is missing, which leads to;Itbulo. C omozh1q

Views and stored procedures are added by executing SQL statements. Itbulo. C omozh1q

Itbulo. C omozh1q