SqlServer 实用操作小技巧集合
前端之家收集整理的这篇文章主要介绍了
SqlServer 实用操作小技巧集合,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等
(一)挂起操作
在安装
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、收缩
数据库完成后,建议将您的
数据库属性重新设置为标准模式,操作
方法同第一点,因为日志在一些异常情况下往往是恢复
数据库的重要依据
方法二
<div class="codetitle">
<a style="CURSOR: pointer" data="31119" class="copybut" id="copybut31119" onclick="doCopy('code31119')"> 代码如下:
<div class="codebody" id="code31119">
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 '置疑的
数据库名','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','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_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\MS
sqlServer\Client\ConnectTo]
"DSQUERY"="DBNETLIB".
如果没有,请自己建立
3、停止ms
sql.
4、进行安装.
这样就可以进行正确安装了。
二、
sql Server补丁版本的检查
sql Server的补丁版本检查不如Windows 补丁版本检查直接,一个系统
管理员,如果不了解
sql Server版本对应的补丁号,可能也会遇到一点麻烦,因此在这说明一下,通过这样的办法判别机器是安全的办法,不会对系统产生任何影响。
1、用I
sql或者
SQL查询分析器
登录到
sql Server,如果是用I
sql,请在cmd窗口输入i
sql -U sa,然后输入密码,进入;如果是用
SQL查询分析器,请从程序中启动,输入sa和密码(也可以用windows验证)。
2、在I
sql中输入:
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 Files\Microsoft
sql Server\MS
sql\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的导入导出方式,对新
数据库导入原
数据库中的所有表
内容 利用
数据库日志恢复数据到时间点的操作
由于不正常的数据丢失,而又不想使用备份数据还原,只要原来有备份且当前日志保存完好,可以采用这个
方法试试,说不定可挽回损失……
1,如果误操作之前存在一个全库备份(或已有多个差异备份或增量备份),首先要做的事就是进
进行一次日志备份(如果为了不让日志
文件变大而置trunc. log on chkpt.选项为1那你就死翘了)
backup log dbName to disk='fileName'
2,恢复一个全库备份,注意需要使用with norecovery,如果还有其他差异或增量备份,则逐个恢
复
restore database dbName from disk='fileName' with norecovery
3,恢复最后一个日志备份即刚做的日志备份,指定恢复时间点到误操作之前的时刻
restore log dbName from disk='fileName'
with stopat='date_time'
以上这些操作都可以在
sql SERVER企业管理器里完成,难度不大。。。
当然,如果误操作是一些不记日志的操作比如truncate table,select into等操作,那么是无法利
用上述
方法来恢复数据的...
sql Server2000
数据库文件损坏时如何恢复
出现这样的问题是比较严重的了,能否修复只能看你的运气……
sql Server2000中,如果
数据库文件(非系统
数据库文件)遇到
错误的时候,仅适用于非master,msdb的
数据库。
说明如下:
1 建一个测试
数据库test(
数据库类型为完全)
2 建一个表,插入点记录
create table a(c1 varchar(2))
go
insert into a values('aa')
go
insert into a values('bb')
go
3 作完全备份,到
文件test_1.bak
4 在作一点
修改 insert into a values('cc')
go
create table b(c1 int)
go
insert into b values(1)
go
insert into b values(2)
go
5 shutdown
数据库服务器
6 用ultraedit编辑
数据库文件test_data.mdf,随便
修改点字节
内容,相当于
数据库遭到致命的损坏。
7 启动
数据库,并且运行企业管理器,点开
数据库,看到test变成灰色,而且
显示置疑。
8 运行i
sql -SLocalhost -Usa -P
1> backup log test TO DISK='D:Program FilesMicrosoft
sql ServerMS
sqlBACKUP
est_2.bak' WITH NO_TRUNCATE
2>go
已处理 2 页,这些页属于
数据库 'test' 的
文件 'TEST_Log'(位于
文件 1 上)。
BACKUP LOG 操作成功地处理了 2 页,花费了 0.111 秒(0.087 MB/秒)。
9 进行恢复最老的完全备份
1> RESTORE DATABASE test FROM DISK='D:Program FilesMicrosoft
sql ServerMS
sql BACKUP est_1.bak' WITH NORECOVERY
2> go
已处理 96 页,这些页属于
数据库 'test' 的
文件 'TEST_Data'(位于
文件 1 上)。
已处理 1 页,这些页属于
数据库 'test' 的
文件 'TEST_Log'(位于
文件 1 上)。
RESTORE DATABASE 操作成功地处理了 97 页,花费了 0.107 秒(7.368 MB/秒)。
10 恢复最近的日志
1> RESTORE LOG test FROM DISK='D:Program FilesMicrosoft
sql ServerMS
sqlBACKU
P est_2.bak' WITH RECOVERY
2> go
已处理 2 页,这些页属于
数据库 'test' 的
文件 'TEST_Log'(位于
文件 1 上)。
RESTORE LOG 操作成功地处理了 2 页,花费了 0.056 秒(0.173 MB/秒)。 存储过程编写经验和优化措施
经验之谈,看看……
一、适合读者对象:
数据库开发程序员,
数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对
数据库有浓厚兴趣的人。
二、介绍:在
数据库的开发过程中,经常会遇到复杂的业务逻辑和对
数据库的操作,这个时候就会用SP来封装
数据库操作。如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果
数据库的数据量大或者项目对SP的
性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个
性能差的SP的效率甚至高几百倍。
三、
内容:
1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能
显示出该SP所使用的跨库table或view,不方便校验。
2、开发人员在提交SP前,必须已经使用set showplan on分析过
查询计划,做过自身的
查询优化检查。
3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:
a)
sql的使用规范:
i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件
提取数据到临时表中,然后再做连接。
iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。
iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
v. 不要在where子句中的“=”左边进行
函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替select count(1)来判断是否存在记录,count
函数只有在
统计表中所有行数时使用,而且count(1)比count(*)更有效率。
vii. 尽量使用“>=”,不要使用“>”。
viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。
x. 注意存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据
页面(400k),那么系统将会进行锁
升级,页级锁会
升级成表级锁。
b)索引的使用规范:
i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。
ii. 尽可能的使用索引字段作为
查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引
iii. 避免对大表
查询时进行table scan,必要时考虑新建索引。
iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。
v. 要注意索引的维护,周期性重建索引,重新编译存储过程。
c)tempdb的使用规范:
i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和
删除临时表,减少系统表资源的消耗。
iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。
iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式
删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接
查询和
修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。
d)合理的算法使用:
根据上面已提到的
sql优化技术和ASE Tuning手册中的
sql优化
内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的
方法。具体可用ASE调优命令:set statistics io on,set statistics time on,set showplan on 等 .