SQL SERVER实用技巧

　（一）挂起操纵

　在安装Sql或sp补丁的时辰体系提醒之前有挂起的安装操纵，要求重启，这里每每重启无用，办理步伐：

　到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager

　删除PendingFileRenameOperations

　（二）紧缩数据库

　--重建索引

DBCC REINDEX
DBCC INDEXDEFRAG

　--紧缩数据和日记

DBCC SHRINKDB
DBCC SHRINKFILE

　（三）压缩数据库

dbcc shrinkdatabase(dbname)

　（四）转移数据库给新用户以已存在用户权限

exec sp_change_users_login 'update_one','newname','oldname'
go

　（五）搜查备份集

RESTORE VERIFYONLY from disk='E:dvbbs.bak'

　（六）修复数据库

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 有3个参数:

    --REPAIR_ALLOW_DATA_LOSS

    --执行由 REPAIR_REBUILD 完成的全部修复，包罗对行和页举办分派和打消分派以纠正分派错误、布局行或页的错误，以及删除已破坏的文本工具。这些修复也许会导致一些数据丢失。修复操纵可以在用户事宜下完成以应承用户回滚所做的变动。假如回滚修复，则数据库仍会含有错误，应该从备份举办规复。假如因为所提供修复品级的缘故漏掉某个错误的修复，则将漏掉任何取决于该修复的修复。修复完成后，备份数据库。

    --REPAIR_FAST 举办小的、不耗时的修复操纵，如修复非聚积索引中的附加键。这些修复可以很快完成，而且不会有丢失数据的伤害。

    --REPAIR_REBUILD 执行由 REPAIR_FAST 完成的全部修复，包罗必要较长时刻的修复（如重建索引）。执行这些修复时不会有丢失数据的伤害。

    --DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY

   SQL SERVER日记破除的两种要领

　在行使进程中各人常常遇到数据库日记很是大的环境，在这里先容了两种处理赏罚要领……

　要领一

　一样平常环境下，SQL数据库的紧缩并不能很洪流平上减小数据库巨细，其首要浸染是紧缩日记巨细，该当按期举办此操纵以免数据库日记过大

　1、配置数据库模式为简朴模式：打开SQL企业打点器，在节制台根目次中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的处事器-->双击打开数据库目次-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在妨碍还原的模式中选择“简朴”，然后按确定生涯

　2、在当前数据库上点右键，看全部使命中的紧缩数据库，一样平常内里的默认配置不消调解，直接点确定

　3、紧缩数据库完成后，提议将您的数据库属性从头配置为尺度模式，操纵要领同第一点，由于日记在一些非常环境下每每是规复数据库的重要依据

　要领二

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT

USE     tablename             -- 要操纵的数据库名
SELECT  @LogicalFileName = 'tablename_log',  -- 日记文件名
@MaxMinutes = 10,               -- Limit on time allowed to wrap log.
        @NewSize = 1                  -- 你想设定的日记文件的巨细(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

 　删除数据库中一再数据的几个要领

　数据库的行使进程中因为措施方面的题目偶然辰会遇到一再数据，一再数据导致了数据库部门配置不能正确配置……

　要领一

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 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 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

　要领二

　有两个意义上的一再记录，一是完全一再的记录，也即全部字段均一再的记录，二是部门要害字段一再的记录，好比Name字段一再，而其他字段不必然一再或都一再可以忽略。

　1、对付第一种一再，较量轻易办理，行使

    select distinct * from tableName

　就可以获得无一再记录的功效集。

　假如该表必要删除一再的记录（一再记录保存1条），可以按以下要领删除

    select distinct * into #Tmp from tableName
    drop table tableName
    select * into tableName from #Tmp
    drop table #Tmp

　产生这种一再的缘故起因是表计划不周发生的，增进独一索引列即可办理。

　2、这类一再题目凡是要求保存一再记录中的第一笔记录，操纵要领如下

    假设有一再的字段为Name,Address，要求获得这两个字段独一的功效集

    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)

    最后一个select即获得了Name，Address不一再的功效集（但多了一个autoID字段，现实写时可以写在select子句中省去此列）

　变动数据库中表的所属用户的两个要领

　各人也许会常常遇到一个数据库备份还原到其它一台呆板功效导致全部的表都不能打开了，缘故起因是建表的时辰回收了其时的数据库用户……

    --变动某个表

exec sp_changeobjectowner 'tablename','dbo'

    --存储变动所有表

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

GO

　SQL SERVER中直接轮回写入数据

　没什么好说的了，各人本身看，偶然辰有点用处

declare @i int
set @i=1
while @i<30
begin
   insert into test (userid) values(@i)
   set @i=@i+1
end

 　无数据库日记文件规复数据库要领两则

　数据库日记文件的误删或此外缘故起因引起数据库日记的破坏

　要领一

　1.新建一个同名的数据库

　2.再停掉sql server(留意不要疏散数据库)

　3.用原数据库的数据文件包围掉这个新建的数据库

　4.再重启sql server

　5.此时打开企业打点器时会呈现置疑，先不管，执行下面的语句（留意修改个中的数据库名)

　6.完成后一样平常就可以会见数据库中的数据了,这时,数据库自己一样平常还要题目,办理步伐是,操作
数据库的剧本建设一个新的数据库,并将数据导进去就行了.

USE MASTER
GO

SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
GO

UPDATE SYSDATABASES SET STATUS =32768 WHERE NAME='置疑的数据库名'
Go

sp_dboption '置疑的数据库名', 'single user', 'true'
Go

DBCC CHECKDB('置疑的数据库名')
Go

update sysdatabases set status =28 where name='置疑的数据库名'
Go

sp_configure 'allow updates', 0 reconfigure with override
Go

sp_dboption '置疑的数据库名', 'single user', 'false'
Go

　要领二

　工作的因由

　昨天，体系打点员汇报我，我们一个内部应用数据库地址的磁盘空间不敷了。我留意到数据库变乱日记文件XXX_Data.ldf文件已经增添到了3GB，于是我决意缩小这个日记文件。颠末紧缩数据库等操纵未果后，我犯了一个自进入行业以来的最大最愚笨的错误：竟然误删除了这个日记文件！其后我看到全部论及数据库规复的文章上都说道：“无论怎样都要担保数据库日记文件存在，它至关重要”，乃至微软乃至有一篇KB文章讲怎样只靠日记文件规复数据库的。我真是不知道我当时辰是怎么想的？！

　这下子坏了！这个数据库连不上了，企业打点器在它的旁边写着“(置疑)”。并且最要命的，这个数据库从来没有备份了。我独一找获得的是迁徙半年前的其它一个数据库处事器，应用倒是能用了，可是少了很多记录、表和存储进程。真但愿这只是一场恶梦！

　没有结果的规复步调

　附加数据库

_Rambo讲过被删除日记文件中不存在勾当日记时，可以这么做来规复：

    1，疏散被置疑的数据库，可以行使sp_detach_db
    2，附加数据库，可以行使sp_attach_single_file_db

　可是，很遗憾，执行之后，SQL Server质疑数据文件和日记文件不符，以是无法附加数据库数据文件。

　DTS数据导出

　不可，无法读取XXX数据库，DTS Wizard陈诉说“初始化上下文产生错误”。

　紧张模式

　怡红令郎讲过没有日记用于规复时，可以这么做：

    1，把数据库配置为emergency mode

    2，从头成立一个log文件

    3，把SQL Server 从头启动一下

    4，把应用数据库配置成单用户模式

    5，做DBCC CHECKDB

    6，假如没有什么大题目就可以把数据库状态改归去了，记得别忘了把体系表的修改选项关掉

 　我实践了一下，把应用数据库的数据文件移走，从头成立一个同名的数据库XXX，然后停掉SQL处事，把原本的数据文件再包围返来。之后，凭证怡红令郎的步调走。

　可是，也很遗憾，除了第2步之外，其他步调执行很是乐成。痛惜，重启SQL Server之后，这个应用数据库如故是置疑！

　不外，让我欣慰的是，这么做之后，倒是可以或许Select数据了，让我大出一口吻。只不外，组件行使数据库时，陈诉说：“产生错误：-2147467259,未能在数据库 'XXX' 中运行 BEGIN TRANSACTION，由于该数据库处于回避规复模式。”

 　最终乐陋习复的所有步调

　配置数据库为紧张模式
     停掉SQL Server处事；

      把应用数据库的数据文件XXX_Data.mdf移走；

      从头成立一个同名的数据库XXX；

      停掉SQL处事；

     把原本的数据文件再包围返来；

      运行以下语句，把该数据库配置为紧张模式；

     运行“Use Master

Go

sp_configure 'allow updates', 1

reconfigure with override

Go”

　执行功效：

DBCC 执行完毕。假如 DBCC 输出了错误信息，请与体系打点员接洽。

　已将设置选项 'allow updates' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。

 　接着运行“update sysdatabases set status = 32768 where name = 'XXX'”

　执行功效：

　（所影响的行数为 1 行）

     重启SQL Server处事；

    运行以下语句，把应用数据库配置为Single User模式；

     运行“sp_dboption 'XXX', 'single user', 'true'”

　执行功效：

     呼吁已乐成完成。

ü         做DBCC CHECKDB；

      运行“DBCC CHECKDB('XXX')”

　执行功效：

'XXX' 的 DBCC 功效。

'sysobjects' 的 DBCC 功效。

工具 'sysobjects' 有 273 行，这些行位于 5 页中。

'sysindexes' 的 DBCC 功效。

工具 'sysindexes' 有 202 行，这些行位于 7 页中。

'syscolumns' 的 DBCC 功效。

………

ü         运行以下语句把体系表的修改选项关掉；

      运行“sp_resetstatus "XXX"

go

sp_configure 'allow updates', 0

reconfigure with override

Go”

　执行功效：

　在 sysdatabases 中更新数据库 'XXX' 的条目之前，模式 = 0，状态 = 28（状态 suspect_bit = 0），

　没有更新 sysdatabases 中的任何行，由于已正确地重置了模式和状态。没有错误，未举办任何变动。

　DBCC 执行完毕。假如 DBCC 输出了错误信息，请与体系打点员接洽。

　已将设置选项 'allow updates' 从 1 改为 0。请运行 RECONFIGURE 语句以安装。

    从头成立其它一个数据库XXX.Lost；

　DTS导出领导

    运行DTS导出领导；

    复制源选择EmergencyMode的数据库XXX，导入到XXX.Lost；

     选择“在SQL Server数据库之间复制工具和数据”，试了多次，仿佛不可，只是复制过来了全部表布局，可是没稀有据，也没有视图和存储进程，并且DTS领导最后陈诉复制失败；

     以是最后选择“从源数据库复制表和视图”，可是其后发明，这样老是只能复制一部门表记录；

     于是选择“用一条查询指定要传输的数据”，缺哪个表记录，就导哪个；

     视图和存储进程是执行SQL语句添加的。

    维护Sql Server中表的索引

　在行使和建设数据库索引中常常会遇到一些题目，在这里可以回收一些另类的要领办理…

    --第一步：查察是否必要维护，查察扫描密度/Scan Density是否为100%
declare @table_id int
set @table_id=object_id('表名')
dbcc showcontig(@table_id)

    --第二步:重构表索引
dbcc dbreindex('表名',pk_索引名,100)

    --重做第一步，如发明扫描密度/Scan Density照旧小于100%则重构表的全部索引
--杨铮：并不必然能达100%。
dbcc dbreindex('表名','',100)

    SQL Server补丁安装常见题目
  谁遇到题目就看看咯：）

    一、补丁安装进程中常见题目

    假如在安装补丁的时辰碰着如下相同错误：

    1、安装进程中呈现“早年举办的措施建设了挂起的文件操纵，运行安装措施前，必需从头启动”，请凭证下面步调整决：

a、重启呆板，再举办安装，假如发明尚有该错误，请按下面步调
b、在开始->运行中输入regedit
c、到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager 位置
d、选择文件->倒出,生涯
e、在右边窗口右击PendingFileRenameOperations，选择删除，然后确认
f、重启安装，题目办理

    假如尚有同样题目，请搜查其余注册表中是否有该值存在，若有请删掉。

    2、在安装SQL Server SP3，偶然辰会呈现：无论用windows认证照旧混和认证，都呈现暗码错误的环境，这时查察姑且目次下的sqlsp.out，会发明以下描写：
[TCP/IP Sockets]Specified SQL server not found.
[TCP/IP Sockets]ConnectionOpen (Connect()).
其拭魅这是SQL Server SP3的一个小bug，在安装sp3的时辰，没有监听tcp/ip端口，可以凭证以下步调举办：

    1、打开SQL server客户器收集适用器材和处事器收集器材，确保启用的协议中包括name pipe，而且位置在第一位.
    2、确保[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerClientConnectTo]
"DSQUERY"="DBNETLIB".
假如没有，请本身成立
    3、遏制mssql.
    4、举办安装.

    这样就可以举办正确安装了。

    二、SQL Server补丁版本的搜查

    SQL Server的补丁版本搜查不如Windows 补丁版本搜查直接，一个体系打点员，假如不相识SQL Server版本对应的补丁号，也许也会碰着一点贫困，因此在这声名一下，通过这样的步伐鉴别呆板是安详的步伐，不会对体系发生任何影响。
    1、用Isql可能SQL查询说明器登录到SQL Server，假如是用Isql，请在cmd窗口输入isql -U sa,然后输入暗码，进入；假如是用SQL查询说明器，请从措施中启动，输入sa和暗码（也可以用windows验证）。
    2、在ISQL中输入：
Select @@Version；
go

可能SQL查询说明器中输入(着实假如不想输入，只要打开辅佐的关于就可以了:))
Select @@Version；
然后按执行；
这时会返回SQL的版本信息，如下：
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
个中的8.00.760就是SQL Server的版本和补丁号。对应相关如下：

8.00.194 －——————SQL Server 2000 RTM
8.00.384 －——————(SP1)
8.00.534 －——————(SP2)
8.00.760 －——————(SP3)

　这样我们就能看到SQL Server的正确版本和补丁号了。

　我们也可以用xp_msver看到更具体的信息

　Sql Server数据库的备份和规复法子，最常用的操纵，新手们看看……

　一、备份数据库

1、打开SQL企业打点器，在节制台根目次中依次点开Microsoft SQL Server
2、SQL Server组-->双击打开你的处事器-->双击打开数据库目次
3、选择你的数据库名称（如论坛数据库Forum）-->然后点上面菜单中的器材-->选择备份数据库
4、备份选项选择完全备份，目标中的备份到假如本乐匦路径和名称则选中名称点删除，然后点添加，假如原本没有路径和名称则直接选择添加，接着指定路径和文件名，指定后点确定返回备份窗口，接着点确定举办备份

　二、还原数据库

　1、打开SQL企业打点器，在节制台根目次中依次点开Microsoft SQL Server

　2、SQL Server组-->双击打开你的处事器-->点图标栏的新建数据库图标，新建数据库的名字自行取

　3、点击新建好的数据库名称（如论坛数据库Forum）-->然后点上面菜单中的器材-->选择规复数据库

　4、在弹出来的窗口中的还原选项中选择从装备-->点选择装备-->点添加-->然后选择你的备份文件名-->添加后点确定返回，这时辰装备栏应该呈现您适才选择的数据库备份文件名，备份号默以为1（假如您对统一个文件做过多次备份，可以点击备份号旁边的查察内容，在复选框中选择最新的一次备份后点确定）-->然后点击上方通例旁边的选项按钮

　5、在呈现的窗口中选择在现稀有据库上逼迫还原，以及在规复完成状态中选择使数据库可以继承运行但无法还原其余事宜日记的选项。在窗口的中间部位的将数据库文件还原为这里要凭证你SQL的安装举办配置（也可以指定本身的目次），逻辑文件名不必要窜改，移至物理文件名要按照你所规复的呆板环境做窜改，如您的SQL数据库装在D:Program FilesMicrosoft SQL ServerMSSQLData，那么就凭证您规复呆板的目次举办相干窜改窜改，而且最后的文件名最好改成您当前的数据库名（如原本是bbs_data.mdf，此刻的数据库是forum，就改成forum_data.mdf），日记和数据文件都要凭证这样的方法做相干的窜改（日记的文件名是*_log.ldf末了的），这里的规复目次您可以自由配置，条件是该目次必需存在（如您可以指定d:sqldatabbs_data.mdf可能d:sqldatabbs_log.ldf），不然规复将报错

　6、修改完成后，点击下面简直定举办规复，这时会呈现一个进度条，提醒规复的进度，规复完成后体系会自动提醒乐成，如中间提醒报错，请记录下相干的错误内容并扣问对SQL操纵较量认识的职员，一样平常的错误无非是目次错误可能文件名一再可能文件名错误可能空间不足可能数据库正在行使中的错误，数据库正在行使的错误您可以实行封锁全部关于SQL窗口然后从头打开举办规复操纵，假如还提醒正在行使的错误可以将SQL处事遏制然后重起看看，至于上述其余的错误一样平常都能凭证错误内容做响应窜改后即可规复

　三、紧缩数据库

　一样平常环境下，SQL数据库的紧缩并不能很洪流平上减小数据库巨细，其首要浸染是紧缩日记巨细，该当按期举办此操纵以免数据库日记过大

　1、配置数据库模式为简朴模式：打开SQL企业打点器，在节制台根目次中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的处事器-->双击打开数据库目次-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在妨碍还原的模式中选择“简朴”，然后按确定生涯

　2、在当前数据库上点右键，看全部使命中的紧缩数据库，一样平常内里的默认配置不消调解，直接点确定

　3、紧缩数据库完成后，提议将您的数据库属性从头配置为尺度模式，操纵要领同第一点，由于日记在一些非常环境下每每是规复数据库的重要依据

　四、设定逐日自动备份数据库

　凶猛提议有前提的用户举办此操纵！

　1、打开企业打点器，在节制台根目次中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的处事器

　2、然后点上面菜单中的器材-->选择数据库维护打算器

　3、下一步选摘要举办自动备份的数据

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!