Sql Server实用操作小技巧集合

 

Sql Server实用操作小技巧集合 包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 1.挂起操作 在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法： 到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager 删除PendingFileRenameOperations 2.收缩数据库 --重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 3.压缩数据库 dbcc shrinkdatabase(dbname) 4.转移数据库给新用户以已存在用户权限 exec sp_change_users_login 'update_one','newname','oldname' go 5.检查备份集 RESTORE VERIFYONLY from disk='E:/dvbbs.bak' 6.修复数据库 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 7.SQL SERVER日志清除的两种方法 在使用过程中大家经常碰到数据库日志非常大的情况，在这里介绍了两种处理方法…… 方法一 一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大 1、设置数据库模式为简单模式：打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”，然后按确定保存 2、在当前数据库上点右键，看所有任务中的收缩数据库，一般里面的默认设置不用调整，直接点确定 3、收缩数据库完成后，建议将您的数据库属性重新设置为标准模式，操作方法同第一点，因为日志在一些异常情况下往往是恢复数据库的重要依据 方法二 SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE databasename -- 要操作的数据库名 SELECT @LogicalFileName = 'databasename_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 8.删除数据库中重复数据的几个方法 数据库的使用过程中由于程序方面的问题有时候会碰到重复数据，重复数据导致了数据库部分设置不能正确设置…… 方法一 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字段重复，而其他字段不一定重复或都重复可以忽略。 A、对于第一种重复，比较容易解决，使用 select distinct * from tableName 就可以得到无重复记录的结果集。 如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除 select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp 发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决 B、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下 假设有重复的字段为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子句中省去此列） 9.更改数据库中表的所属用户的两个方法 大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了，原因是建表的时候采用了当时的数据库用户…… --更改某个表 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 10.SQL SERVER中直接循环写入数据 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end 11.无数据库日志文件恢复数据库方法两则 数据库日志文件的误删或别的原因引起数据库日志的损坏 方法一 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 方法二 设置数据库为紧急模式 停掉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语句添加的。 12.维护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) 13.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的版本和补丁号。对应关系如下： SQL Server 2000 版本和级别 @@VERSION 产品级别 SQL Server 2000 原始版本 8.00.194 RTM Database Components SP1 8.00.384 SP1 Database Components SP2 8.00.534 SP2 Database Components SP3、SP3a 或 MSDE 2000 Release A 8.00.760 SP3 Database Components SP4 8.00.2039 SP4 这样我们就能看到SQL Server的正确版本和补丁号了。 我们也可以用xp_msver看到更详细的信息 14.Sql Server数据库的备份和恢复措施 14.1、备份数据库 1、打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server 2、SQL Server组-->双击打开你的服务器-->双击打开数据库目录 3、选择你的数据库名称（如论坛数据库Forum）-->然后点上面菜单中的工具-->选择备份数据库 4、备份选项选择完全备份，目的中的备份到如果原来有路径和名称则选中名称点删除，然后点添加，如果原来没有路径和名称则直接选择添加，接着指定路径和文件名，指定后点确定返回备份窗口，接着点确定进行备份 14.2、还原数据库 1、打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server 2、SQL Server组-->双击打开你的服务器-->点图标栏的新建数据库图标，新建数据库的名字自行取 3、点击新建好的数据库名称（如论坛数据库Forum）-->然后点上面菜单中的工具-->选择恢复数据库 4、在弹出来的窗口中的还原选项中选择从设备-->点选择设备-->点添加-->然后选择你的备份文件名-->添加后点确定返回，这时候设备栏应该出现您刚才选择的数据库备份文件名，备份号默认为1（如果您对同一个文件做过多次备份，可以点击备份号旁边的查看内容，在复选框中选择最新的一次备份后点确定）-->然后点击上方常规旁边的选项按钮 5、在出现的窗口中选择在现有数据库上强制还原，以及在恢复完成状态中选择使数据库可以继续运行但无法还原其它事务日志的选项。在窗口的中间部位的将数据库文件还原为这里要按照你SQL的安装进行设置（也可以指定自己的目录），逻辑文件名不需要改动，移至物理文件名要根据你所恢复的机器情况做改动，如您的SQL数据库装在D:/Program Files/Microsoft SQL Server/MSSQL/Data，那么就按照您恢复机器的目录进行相关改动改动，并且最后的文件名最好改成您当前的数据库名（如原来是bbs_data.mdf，现在的数据库是forum，就改成forum_data.mdf），日志和数据文件都要按照这样的方式做相关的改动（日志的文件名是*_log.ldf结尾的），这里的恢复目录您可以自由设置，前提是该目录必须存在（如您可以指定d:/sqldata/bbs_data.mdf或者d:/sqldata/bbs_log.ldf），否则恢复将报错 6、修改完成后，点击下面的确定进行恢复，这时会出现一个进度条，提示恢复的进度，恢复完成后系统会自动提示成功，如中间提示报错，请记录下相关的错误内容并询问对SQL操作比较熟悉的人员，一般的错误无非是目录错误或者文件名重复或者文件名错误或者空间不够或者数据库正在使用中的错误，数据库正在使用的错误您可以尝试关闭所有关于SQL窗口然后重新打开进行恢复操作，如果还提示正在使用的错误可以将SQL服务停止然后重起看看，至于上述其它的错误一般都能按照错误内容做相应改动后即可恢复 14.3、收缩数据库 一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大 1、设置数据库模式为简单模式：打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”，然后按确定保存 2、在当前数据库上点右键，看所有任务中的收缩数据库，一般里面的默认设置不用调整，直接点确定 3、收缩数据库完成后，建议将您的数据库属性重新设置为标准模式，操作方法同第一点，因为日志在一些异常情况下往往是恢复数据库的重要依据 14.5、设定每日自动备份数据库 强烈建议有条件的用户进行此操作！ 1、打开企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器 2、然后点上面菜单中的工具-->选择数据库维护计划器 3、下一步选择要进行自动备份的数据-->下一步更新数据优化信息，这里一般不用做选择-->下一步检查数据完整性，也一般不选择 4、下一步指定数据库维护计划，默认的是1周备份一次，点击更改选择每天备份后点确定 5、下一步指定备份的磁盘目录，选择指定目录，如您可以在D盘新建一个目录如：d:/databak，然后在这里选择使用此目录，如果您的数据库比较多最好选择为每个数据库建立子目录，然后选择删除早于多少天前的备份，一般设定4－7天，这看您的具体备份要求，备份文件扩展名一般都是bak就用默认的 6、下一步指定事务日志备份计划，看您的需要做选择-->下一步要生成的报表，一般不做选择-->下一步维护计划历史记录，最好用默认的选项-->下一步完成 7、完成后系统很可能会提示Sql Server Agent服务未启动，先点确定完成计划设定，然后找到桌面最右边状态栏中的SQL绿色图标，双击点开，在服务中选择Sql Server Agent，然后点击运行箭头，选上下方的当启动OS时自动启动服务 8、这个时候数据库计划已经成功的运行了，他将按照您上面的设置进行自动备份 修改计划： 1、打开企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->管理-->数据库维护计划-->打开后可看到你设定的计划，可以进行修改或者删除操作 14.6、数据的转移（新建数据库或转移服务器） 一般情况下，最好使用备份和还原操作来进行转移数据，在特殊情况下，可以用导入导出的方式进行转移，这里介绍的就是导入导出方式，导入导出方式转移数据一个作用就是可以在收缩数据库无效的情况下用来减小（收缩）数据库的大小，本操作默认为您对SQL的操作有一定的了解，如果对其中的部分操作不理解，可以咨询动网相关人员或者查询网上资料