sql备份还原.docx - 冰点文库

资源描述

sql备份还原.docx

《sql备份还原.docx》由会员分享，可在线阅读，更多相关《sql备份还原.docx（22页珍藏版）》请在冰点文库上搜索。

sql备份还原.docx

sql备份还原

SQL脚本实现定时备份恢复\检查数据库完整性[SQL]

2008-04-1712:

昨天做项目的一个大二的人问我怎么实现MSSQLServer的自动定时备份，恢复数据库，显示指定表和索引碎片信息，以及检查数据库存储空间分配一致性，我就写了如下的一个SQL脚本给他。

Task.sql代码如下：

WAITFORTIME'16:

00' '16:

00的时候开始检查工具

BackupDATABASEgoods 'goods是商品的数据库名

TodiskWithDifferential,Format 'Differential说明是差异备份方式，Format是重写媒体头

disk='backup.bak'

下面要检查数据库一致性，显示指定表和索引碎片信息

USEgoods

DBCCCHECKALLOC（'goods'） '检查碎片

DECLARE@idint,@indidint '申明变量

SET@id=OBJECT_ID（'price'） '取表的ID

SELECT@indid=indid

FROMsysindexes

WHEREid=@id

ANDname='biweilun'

DBCCSHOWCONFIG（@id,@indid） '检查一致性

WAITFORTIME'5:

00' '5点还原goods数据库

RestoreDATABASEgoodsfromdisk='backup.bak'

学习完毕SQL数据库，发现什么都很简单，特别是T-SQL的修习完成之后，看网上的那些SQL注入语句，真是小儿科哈~~

sqlserver数据库模型备份恢复总结备份脚本

2008-04-2911:

42:

　标签：

备份恢复sqlserver数据库模型　　　[推送到技术圈]

事务日志是可以基于时间点恢复的，必须在full或bulk_logged模式下

Alterdatabase[DBName]setrecoverbulk_logged,thenthefollowingoperationwillnotbelogged:

*SELECTINTO

*BULKCOPYandBulkCopyProgram（BCP）

*CREATEINDEX

*特定文字操作

差异备份的数据文件不和数据备份的文件用一个文件，尽管可以

每一种备份模式下，备份的同时要备份master和msdb数据库

数据备份和清空日志没有关系，但清空日志要发生在事务日志备份之后，在这个之间

模式设置：

alterdatabaseCACDB_S1000setrecoverybulk_logged

数据备份：

backupdatabaseCACDB_S1000todisk='E:

\backup\data\CACDB_S1000_200801031245.data'

差异备份：

backupdatabaseCACDB_S1000todisk='E:

\backup\diff\CACDB_S1000_200801031245.diff'withDIFFERENTIAL

清空日志：

DUMPTRANSACTIONCACDB_S1000WITHNO_LOG

BACKUPLOGCACDB_S1000WITHNO_LOG

DBCCSHRINKDATABASE（CACDB_S1000）

事务日志备份：

BACKUPLOGCACDB_S1000todisk='E:

\backup\log\CACDB_S1000_200801031245.log'

还原：

RESTOREDATABASECACDB_S1000FROMDISK='E:

\backup\data\CACDB_S1000_200801031245.data'withNORECOVERY

RESTORELOGCACDB_S1000fromdisk='E:

\backup\log\CACDB_S1000_200801031250.log'

备份脚本：

declare@sqlvarchar（8000）,@namevarchar（255）,@typevarchar（255）,@sqlTnvarchar（4000）

declarexcursorforselectnamefrommaster.dbo.sysdatabases

openx

fetchnextfromxinto@name

while（@@fetch_status=0）

begin

if（@name<>'tempdb'）

begin

print@name

set@sql='backupdatabase'+@name+'todisk=''D:

\backup_20080421\'

set@sql=@sql+@name+'_20080421117.data'''

exec（@sql）

set@sqlT='SELECTDATABASEPROPERTYEX（'''+@name+''',''Recovery''）'

execsp_executesql@sqlT,N'@typevarchar（255）out',@typeout

if（@type<>'SIMPLE'）

begin

set@sql='backuplog'+@name+'todisk=''D:

\backup_20080421\'

set@sql=@sql+@name+'_20080421117.log'''

exec（@sql）

end

fetchnextfromxinto@name

end

closex

deallocatex

备份再还原时遇到设备激活错误之类的问题，解决方案：

（原因，同名数据库的文件逻辑名不一样）

sqlserver手动创建的数据库，例dbtest，会带上_Data，结果文件逻辑名为dbtest_Data

程序自动创建的，如果照sqlserver手册写，文件逻辑名为dbtest_dat

手动创建时，没有指定参数，文件逻辑名为dbtest

这样备份后再还原时，就会出错，因为'被还原的库的文件逻辑名'与'备份文件中的文件逻辑名'不对应

用RESTOREFILELISTONLY来显示文件逻辑名和物理文件名的对应关系

用

alterdatabase数据库名

modifyfile（

name=逻辑名

newname=新逻辑名

）

来改变文件逻辑名

如果遇到文件路径的问题，可以restoredatabase的时候，带上withmove/,move参数

/*--备份数据库--*/

/*--调用示例

--备份当前数据库

exec p_backupdb @bkpath='c:

\',@bkfname='db_\DATE\_db.bak'

--差异备份当前数据库

exec p_backupdb @bkpath='c:

\',@bkfname='db_\DATE\_df.bak',@bktype='DF'

--备份当前数据库日志

exec p_backupdb @bkpath='c:

\',@bkfname='db_\DATE\_log.bak',@bktype='LOG'

--*/

if exists（select * from dbo.sysobjects where id = object_id（N'[dbo].[p_backupdb]'） and OBJECTPROPERTY（id, N'IsProcedure'） = 1）

drop procedure [dbo].[p_backupdb]

create proc p_backupdb

@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库

@bkpath nvarchar（260）='', --备份文件的存放目录,不指定则使用SQL默认的备份目录

@bkfname nvarchar（260）='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间

@bktype nvarchar（10）='DB', --备份类型:

'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份

@appendfile bit=1 --追加/覆盖备份文件

declare @sql varchar（8000）

if isnull（@dbname,''）='' set @dbname=db_name（）

if isnull（@bkpath,''）='' set @bkpath=dbo.f_getdbpath（null）

if isnull（@bkfname,''）='' set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK'

set @bkfname=replace（replace（replace（@bkfname,'\DBNAME\',@dbname）

,'\DATE\',convert（varchar,getdate（）,112））

,'\TIME\',replace（convert（varchar,getdate（）,108）,':

',''））

set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname

+' to disk='''+@bkpath+@bkfname

+''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end

+case @appendfile when 1 then 'NOINIT' else 'INIT' end

print @sql

exec（@sql）

----------------------------------------------------------------------

/*--恢复数据库--*/

/*--调用示例

--完整恢复数据库

exec p_RestoreDb @bkfile='c:

\db_20031015_db.bak',@dbname='db'

--差异备份恢复

exec p_RestoreDb @bkfile='c:

\db_20031015_db.bak',@dbname='db',@retype='DBNOR'

exec p_backupdb @bkfile='c:

\db_20031015_df.bak',@dbname='db',@retype='DF'

--日志备份恢复

exec p_RestoreDb @bkfile='c:

\db_20031015_db.bak',@dbname='db',@retype='DBNOR'

exec p_backupdb @bkfile='c:

\db_20031015_log.bak',@dbname='db',@retype='LOG'

--*/

if exists （select * from dbo.sysobjects where id = object_id（N'[dbo].[p_RestoreDb]'） and OBJECTPROPERTY（id, N'IsProcedure'） = 1）

drop procedure [dbo].[p_RestoreDb]

create proc p_RestoreDb

@bkfile nvarchar（1000）, --定义要恢复的备份文件名

@dbname sysname='', --定义恢复后的数据库名,默认为备份的文件名

@dbpath nvarchar（260）='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录

@retype nvarchar（10）='DB', --恢复类型:

'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复

@filenumber int=1, --恢复的文件号

@overexist bit=1, --是否覆盖已经存在的数据库,仅@retype为

@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效

declare @sql varchar（8000）

--得到恢复后的数据库名

if isnull（@dbname,''）=''

select @sql=reverse（@bkfile）

,@sql=case when charindex（'.',@sql）=0 then @sql

else substring（@sql,charindex（'.',@sql）+1,1000） end

,@sql=case when charindex（'\',@sql）=0 then @sql

else left（@sql,charindex（'\',@sql）-1） end

,@dbname=reverse（@sql）

--得到恢复后的数据库存放目录

if isnull（@dbpath,''）='' set @dbpath=dbo.f_getdbpath（''）

--生成数据库恢复语句

set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname

+' from disk='''+@bkfile+''''

+' with file='+cast（@filenumber as varchar）

+case when @overexist=1 and @retype in（'DB','DBNOR'） then ',replace' else '' end

+case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end

print @sql

--添加移动逻辑文件的处理

if @retype='DB' or @retype='DBNOR'

begin

--从备份文件中获取逻辑文件名

declare @lfn nvarchar（128）,@tp char

（1）,@i int

--创建临时表,保存获取的信息

create table #tb（ln nvarchar（128）,pn nvarchar（260）,tp char

（1）,fgn nvarchar（128）,sz numeric（20,0）,Msz numeric（20,0））

--从备份文件中获取信息

insert into #tb exec（'restore filelistonly from disk='''+@bkfile+''''）

declare #f cursor for select ln,tp from #tb

open #f

fetch next from #f into @lfn,@tp

set @i=0

while @@fetch_status=0

begin

select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast（@i as varchar）

+case @tp when 'D' then '.mdf''' else '.ldf''' end

,@i=@i+1

fetch next from #f into @lfn,@tp

end

close #f

deallocate #f

end

--关闭用户进程处理

if @overexist=1 and @killuser=1

begin

declare @spid varchar（20）

declare #spid cursor for

select spid=cast（spid as varchar（20）） from master..sysprocesses where dbid=db_id（@dbname）

open #spid

fetch next from #spid into @spid

while @@fetch_status=0

begin

exec（'kill '+@spid）

fetch next from #spid into @spid

end

close #spid

deallocate #spid

end

--恢复数据库

exec（@sql）

/*--创建作业 --*/

/*--调用示例

--每月执行的作业

exec p_createjob @jobname='mm',@sql='select * from syscolumns',@freqtype='month'

--每周执行的作业

exec p_createjob @jobname='ww',@sql='select * from syscolumns',@freqtype='week'

--每日执行的作业

exec p_createjob @jobname='a',@sql='select * from syscolumns'

--每日执行的作业,每天隔4小时重复的作业

exec p_createjob @jobname='b',@sql='select * from syscolumns',@fsinterval=4

--*/

if exists （select * from dbo.sysobjects where id = object_id（N'[dbo].[p_createjob]'） and OBJECTPROPERTY（id, N'IsProcedure'） = 1）

drop procedure [dbo].[p_createjob]

create proc p_createjob

@jobname varchar（100）, --作业名称

@sql varchar（8000）, --要执行的命令

@dbname sysname='', --默认为当前的数据库名

@freqtype varchar（6）='day', --时间周期,month 月,week 周,day 日

@fsinterval int=1, --相对于每日的重复次数

@time int=170000 --开始执行时间,对于重复执行的作业,将从0点到23:

59分

if isnull（@dbname,''）='' set @dbname=db_name（）

--创建作业

exec msdb..sp_add_job @job_name=@jobname

--创建作业步骤

exec msdb..sp_add_jobstep @job_name=@jobn

展开阅读全文