SQL Server utility skills set
Several methods of deleting duplicate data in a 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 easier to solve, use
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, this type of repeating problem typically requires the first record in the repeated record, and the operation method is assumed that there is a repeated field as 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 users of the database in the database may often encounter a database backup to restore to another machine results that cause all the tables that cannot be opened, the reason is that the database users at the time ... - Change a table
EXEC SP_CHANGEOBJECTOWNER 'TABLENAME', 'DBO'
- Storage changes all tables
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR (128), @NewOwner as NVARCHAR (128) AS DECLARE @Name as NVARCHAR (128) DECLARE @Owner as NVARCHAR (128) DECLARE @OwnerName as NVARCHAR (128) DECLARE curObject CURSOR FOR select 'name' = name, 'Owner' = user_name (uid) from sysobjects where user_name (uid) = @ OldOwner order by name 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, @ OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner End Close Curobject Deallocate CUROBJECT GOSQL Server Recycling Data
Nothing to say, everyone look, sometimes a bit used
Declare @i int set @ i = 1 While @i <30 begin insert @ i = @ i 1 end
No agricultural log file restores database method two
Misler-deleted or other causes of database log files cause damage to database logs
method one
1. Create a newly named database
2. Stop SQL Server again (Be careful not to separate the database)
3. Overwate this newly built database with the original database data file
4. Restart SQL Server
5. At this time, you will be suspicious when you open the Enterprise Manager. Whether you do this, you will do the following statement (pay attention to the database name)
6. The data in the database can be accessed after completion. At this time, the database itself is generally issued, the solution is to create a new database using the database's script and will pass the data.
USE MASTER GO SP_CONFIGURE 'ALLOW UPDATES', 1 RECONFIGURE WITH OVERRIDE GO UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = 'question database name' Go sp_dboption 'database name doubt', 'single user', 'true' Go DBCC CHECKDB ( 'question database name') Go update sysdatabases set status = 28 where name = 'question database name' Go sp_configure 'allow updates', 0 reconfigure with override Go sp_dboption' question database name ',' single user ',' false 'Method Two
Thing's cause
Yesterday, the system administrator told me that our disk space in which we have internal application database is under. I noticed that the database event log file XXX_DATA.LDF file has grown to 3GB, so I decrease the log file. After the failure of the contracting database, I made a maximum and fooling error since I have entered the industry: I accidentally deleted this log file! Later, I saw all the discussions and database recovery articles: "If you have to ensure that the database log file exists, it is critical, even Microsoft even has a KB article how to recover only by log files. I really don't know what I think at that time? !
This is broken! This database can't be connected, and the enterprise manager is written next to its "(quenus)". And the most important, this database never backed up. I found some of the database server for half a year ago, it can be used, but there are fewer records, tables, and stored procedures. I really hope this is just a nightmare!
Recovery steps that have no effect, attached database _Rambo speechless when there is no activity log in the deleted log file, can be recovered:
1. Separate the quenched database, you can use sp_detach_db 2, additional database, you can use sp_attach_single_file_db
However, unfortunately, after execution, SQL Server questioned that the data files and log files did not match, so they were unable to attach the database data file.
The DTS data is not available, and the XXX database cannot be read, and the DTS Wizard reports "Initialization Context has an error".
Emergency mode Yihong Bani said that there is no log for recovery, you can do this:
1. Set the database to Emergency Mode
2, re-establish a log file
3, restart SQL Server
4, set the application database to single user mode
5, do DBCC Checkdb
6. If there is no big problem, you can change the status of the database. Remember to turn off the modification options for the system table.
I practiced, remove the data file applying the database, re-establish a database XXX of the same name, then stop the SQL service, and then override the original data file back. Thereafter, walk according to the steps of the Yihong Mon.
However, it is also unfortunate that other steps are very successful in addition to step 2. Unfortunately, after restarting SQL Server, this application database is still quilted! However, let me be pleased, after doing this, it is able to select data, let me take a breath. However, when the component uses the database, the report says: "An error: -2147467259, Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode." Final successfully recovered all steps to set the database to emergency mode Stop SQL Server service;
Remove the data file XXX_DATA.MDF of the application database;
Re-establish a database xxx;
Stop SQL service;
Cover the original data file back;
Run the following statement, set the database to emergency mode;
Run "Use master Go sp_configure 'allow updates', 1 Reconfigure with override go"
Results of the:
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 0 to 1. Run the Reconfigure statement to install.
Then run "update sysdatabase set status = 32768 where name = 'xxx'"
Results of the:
(The number of rows affects is 1 line)
Restart SQL Server service;
Run the following statement, set the application database to Single User mode;
Run "sp_dboption 'XXX', 'Single User', 'True'"
Results of the:
The command has been successfully completed.
ü make DBCC CHECKDB;
Run "DBCC CHECKDB ('XXX')" execution result:
'Xxx' DBCC results.
'sysObjects' DBCC results.
Object 'sysObjects' has 273 rows, which are located in page 5.
'sysindexes' DBCC results.
Object 'sysIndexes' has 202 rows, which are located in page 7.
'syscolumn' 's DBCC results.
.........
ü Run the following statement to turn off the modification options for the system table;
Run "sp_resetstatus" xxx "Go sp_configure 'allow updates', 0 reconfigure with override go"
Results of the:
Update the entry of the database 'xxx' in sysdatabases, mode = 0, status = 28 (status Suspect_bit = 0),
No rows in sysdatabases are updated because the mode and status have been properly reset. There is no error, no changes are made.
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 1 to 0. Run the Reconfigure statement to install. Re-establish another database xxx.lost;
DTS Export Wizard Run the DTS Export Wizard;
Copy Source Select EmergencyMode's database XXX, import to XXX.LOST;
Select "Replication objects and data between SQL Server Database", trying multiple times, it seems that you can't copy all table structure, but there is no data, no view and stored procedures, and the DTS wizard finally reports the replication;
So finally select "Source Database Copy Table and View", but later discover, this always can only copy a part of the table record;
So select "Data to be transferred with a query", which is missing which table record, which is
The view and stored procedures are added to the SQL statement.
