此文是Sql Server实用操作小技巧集合，包括Sql数据库MDF数据文件数据库恢复,安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等。
Sql数据库MDF数据文件数据库恢复
mdf文件是数据库中的一个主要数据文件，它是数据库的起点，指向数据库中文件的其它部分。当电脑出现故障无法备份数据库时，只能通过SQL命令的方式将.mdf数据文件附加到当前服务器，还原原有的数据。打开查询分析器，点击【确定】连接到服务器（若SA有密码请输入密码），如下：
进入到查询分析器：
在右面的窗口中输入文件恢复命令,格式为：
sp_attach_single_file_db @dbname = 'dbname'
, @physname = 'physical_name'
dbname：即要还原的数据库名字。
Physname：即物理文件名。
Physical_name：即.mdf文件路径。
例如：IT2000.mdf文件还原至当前服务器。
点击如上所示的执行按钮，则恢复了原来IT2000数据库，命令行提示：命令已被执行。
SQL数据库恢复方法
在SQL Server 中由于MS重新设计了数据库文件的存储方式，取消了新建设备再建数据库这一繁琐的过程。新的存储格式,一个数据库包括两个文件，mdf数据库文件和ldf日志文件。所以我们在重装机器备份时可以把你要备份的数据库的这两个文件拷贝出来，重新安装之后再恢复。
在SQL Server中提供了这种恢复方式的存储过程。
1.sp_attach_db [@dbname =] 'dbname',[@filename1 =] 'filename_n'
给系统添加一个数据库，在dbname指定数据库名称，filename＿n指定数据库的文件和日志文件。比如我有一个voogiya的库，停止SQL Server服务备份voogiya_data.mdf,voogiya_log.ldf，启动SQL server,删除掉这个库，然后再把这两上文件拷到sql server DATA目录中，在Query Analyzer中执行如下语句：
EXEC sp_attach_db @dbname = N'voogiya',
@filename1 = N'd:/mssql7/data/voogiya_data.mdf',
@filename2 = N'd:/mssql7/data/voogiya_log.ldf'
就会把这个库加入到SQL Server Group中.
2.sp_attach_single_file_db [@dbname =] 'dbname',
[@physname =] 'physical_name'
这个命令和上面的功能一样，在physical_name中只要写上据库的物理文件名就可以了，日志文件SQL server会重新建立。这个存储过程的运行要先执行下面的存储过程：
sp_detach_db @dbname = 'dbname'
同样以上面的为例：
EXEC sp_detach_db @dbname = 'voogiya'
EXEC sp_attach_single_file_db @dbname = 'voogiya',
@physname = 'd:/mssql7/data/voogiya_data.mdf'
要注意执行以上存储过程的用户要在sysadmin中.
（一）挂起操作
在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法：
到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session 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子句中省去此列）
更改数据库中表的所属用户的两个方法
大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了，原因是建表的时候采用了当时的数据库用户……
维护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_MACHINE/SYSTEM/CurrentControlSet/Control/Session 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_MACHINE/SOFTWARE/Microsoft/MSSQLServer/Client/ConnectTo]
"DSQUERY"="DBNETLIB".
如果没有，请自己建立
3、停止mssql.
4、进行安装.
这样就可以进行正确安装了。
一、备份数据库
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 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服务停止然后重起看看，至于上述其它的错误一般都能按照错误内容做相应改动后即可恢复。
三、收缩数据库
一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大。
1、设置数据库模式为简单模式：打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”，然后按确定保存。
2、在当前数据库上点右键，看所有任务中的收缩数据库，一般里面的默认设置不用调整，直接点确定。
3、收缩数据库完成后，建议将您的数据库属性重新设置为标准模式，操作方法同第一点，因为日志在一些异常情况下往往是恢复数据库的重要依据。
四、设定每日自动备份数据库
强烈建议有条件的用户进行此操作！
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组-->双击打开你的服务器-->管理-->数据库维护计划-->打开后可看到你设定的计划，可以进行修改或者删除操作。
五、数据的转移（新建数据库或转移服务器）
一般情况下，最好使用备份和还原操作来进行转移数据，在特殊情况下，可以用导入导出的方式进行转移，这里介绍的就是导入导出方式，导入导出方式转移数据一个作用就是可以在收缩数据库无效的情况下用来减小（收缩）数据库的大小，本操作默认为您对SQL的操作有一定的了解，如果对其中的部分操作不理解，可以咨询动网相关人员或者查询网上资料。
1、将原数据库的所有表、存储过程导出成一个SQL文件，导出的时候注意在选项中选择编写索引脚本和编写主键、外键、默认值和检查约束脚本选项。
2、新建数据库，对新建数据库执行第一步中所建立的SQL文件。
3、用SQL的导入导出方式，对新数据库导入原数据库中的所有表内容。