*--备份数据库
/*--调用示例--备份当前数据库
EXEC procBackupDB @byvBkPath='c:\',@byvBkFileName='\DBNAME\_\DATE\_db.bak'--差异备份当前数据库
EXEC procBackupDB @byvBkPath='c:\',@byvBkFileName='db_\DATE\_df.bak',@byvBkType='DF'--备份当前数据库日志
EXEC procBackupDB @byvBkPath='c:\',@byvBkFileName='db_\DATE\_log.bak',@byvBkType='LOG'--*/IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procBackupDB]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[procBackupDB]
GOCREATE PROC procBackupDB
@byvDbName sysname='', --要备份的数据库名称,不指定则备份当前数据库
@byvBkPath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@byvBkFileName nvarchar(260)='', --文件名,可用\DBNAME\表数据库,\DATE\表日期,\TIME\表时间
@byvBkType nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@byvBkSign bit=1 --追加/覆盖备份文件
AS
DECLARE @vchSql varchar(8000)
IF ISNULL(@byvDbName,'')='' SET @byvDbName=db_name()IF ISNULL(@byvBkPath,'')='' BEGIN
SELECT @byvBkPath=RTRIM(REVERSE(filename)) FROM master..sysfiles WHERE name='master'
SELECT @byvBkPath=SUBSTRING(@byvBkPath,CHARINDEX('\',@byvBkPath)+1,4000)
,@byvBkPath=REVERSE(SUBSTRING(@byvBkPath,CHARINDEX('\',@byvBkPath),4000))+'BACKUP\'
END
IF ISNULL(@byvBkFileName,'')='' SET @byvBkFileName='\DBNAME\_\DATE\_\TIME\.BAK'SET @byvBkFileName=REPLACE(REPLACE(REPLACE(@byvBkFileName,'\DBNAME\',@byvDbName)
,'\DATE\',CONVERT(varchar,GETDATE(),112))
,'\TIME\',REPLACE(CONVERT(varchar,GETDATE(),108),':',''))SET @vchSql='backup '+CASE @byvBkType WHEN 'LOG' THEN 'log ' ELSE 'database ' END +@byvDbName
+' to disk='''+@byvBkPath+@byvBkFileName
+''' with '+CASE @byvBkType WHEN 'DF' THEN 'DIFFERENTIAL,' ELSE '' END
+CASE @byvBkSign WHEN 1 THEN 'NOINIT' ELSE 'INIT' ENDEXEC(@vchSql)GO
/*--调用示例--备份当前数据库
EXEC procBackupDB @byvBkPath='c:\',@byvBkFileName='\DBNAME\_\DATE\_db.bak'--差异备份当前数据库
EXEC procBackupDB @byvBkPath='c:\',@byvBkFileName='db_\DATE\_df.bak',@byvBkType='DF'--备份当前数据库日志
EXEC procBackupDB @byvBkPath='c:\',@byvBkFileName='db_\DATE\_log.bak',@byvBkType='LOG'--*/IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procBackupDB]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[procBackupDB]
GOCREATE PROC procBackupDB
@byvDbName sysname='', --要备份的数据库名称,不指定则备份当前数据库
@byvBkPath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@byvBkFileName nvarchar(260)='', --文件名,可用\DBNAME\表数据库,\DATE\表日期,\TIME\表时间
@byvBkType nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@byvBkSign bit=1 --追加/覆盖备份文件
AS
DECLARE @vchSql varchar(8000)
IF ISNULL(@byvDbName,'')='' SET @byvDbName=db_name()IF ISNULL(@byvBkPath,'')='' BEGIN
SELECT @byvBkPath=RTRIM(REVERSE(filename)) FROM master..sysfiles WHERE name='master'
SELECT @byvBkPath=SUBSTRING(@byvBkPath,CHARINDEX('\',@byvBkPath)+1,4000)
,@byvBkPath=REVERSE(SUBSTRING(@byvBkPath,CHARINDEX('\',@byvBkPath),4000))+'BACKUP\'
END
IF ISNULL(@byvBkFileName,'')='' SET @byvBkFileName='\DBNAME\_\DATE\_\TIME\.BAK'SET @byvBkFileName=REPLACE(REPLACE(REPLACE(@byvBkFileName,'\DBNAME\',@byvDbName)
,'\DATE\',CONVERT(varchar,GETDATE(),112))
,'\TIME\',REPLACE(CONVERT(varchar,GETDATE(),108),':',''))SET @vchSql='backup '+CASE @byvBkType WHEN 'LOG' THEN 'log ' ELSE 'database ' END +@byvDbName
+' to disk='''+@byvBkPath+@byvBkFileName
+''' with '+CASE @byvBkType WHEN 'DF' THEN 'DIFFERENTIAL,' ELSE '' END
+CASE @byvBkSign WHEN 1 THEN 'NOINIT' ELSE 'INIT' ENDEXEC(@vchSql)GO
解决方案 »
- 请问这样的数据表应该怎么设计?(冰天雪地裸体跪求!达人显灵吧!)
- 导出删除问题的sql语句
- 请问datetime类型如何查询,like '%2005-2-25%'失败
- 开机后没做任何操作,sqlserver进程就长期站用cpu时间!导致就像死机了一样!是中毒了吗?怎么解决?
- 请教一条sql语句
- 复杂的出库触发器,请高手解决
- 很简单的问题,可是我不会,那位大侠能帮帮我呢?关于存储过程的!!!
- 有没有这样的数据库,它的后缀名是.hdb?如果有,是什么呢?
- 用表中的一个字段更新另一个字段
- 优化根据ip查询ip段
- 跪求高手回答:计算机是如何判别中的“数据与指令”一下子好像还真想不明白
- 实现分页显示的sql语句怎么写呀????????
--完整恢复数据库
EXEC procRestoreDb @byvBkFile='c:\db_20031015_db.bak',@byvBbName='db'--差异备份恢复
EXEC procRestoreDb @byvBkFile='c:\db_20031015_db.bak',@byvBbName='db',@byvType='DBNOR'
EXEC p_backupdb @byvBkFile='c:\db_20031015_df.bak',@byvBbName='db',@byvType='DF'--日志备份恢复
EXEC procRestoreDb @byvBkFile='c:\db_20031015_db.bak',@byvBbName='db',@byvType='DBNOR'
EXEC p_backupdb @byvBkFile='c:\db_20031015_log.bak',@byvBbName='db',@byvType='LOG'--*/IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[procRestoreDb]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROC [dbo].[procRestoreDb]
GOCREATE PROC procRestoreDb
@byvBkFile nvarchar(1000), --定义要恢复的备份文件名
@byvBbName sysname='', --定义恢复后的数据库名,默认为备份的文件名
@byvBbPath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@byvType nvarchar(10)='DB', --恢复类型:'DB'完整,'DBNOR' 为差异,日志恢复进行完整恢复, DF' 差异备份的恢复,'LOG' 日志恢复
@byvFileNo int=1, --恢复的文件号
@byvOverSign bit=1, --是否覆盖已经存在的数据库,仅@byvType为'DB'/'DBNOR'是有效
@byvKillSign bit=1 --是否关闭用户使用进程,仅@byvOverSign=1时有效
AS
DECLARE @vchSql varchar(8000)--得到恢复后的数据库名
IF ISNULL(@byvBbName,'')=''
SELECT @vchSql=REVERSE(@byvBkFile)
,@vchSql= CASE WHEN CHARINDEX('.',@vchSql)=0 THEN @vchSql
ELSE SUBSTRING(@vchSql,CHARINDEX('.',@vchSql)+1,1000) END
,@vchSql= CASE WHEN CHARINDEX('\',@vchSql)=0 THEN @vchSql
ELSE LEFT(@vchSql,CHARINDEX('\',@vchSql)-1) END
,@byvBbName=REVERSE(@vchSql)--得到恢复后的数据库存放目录
IF ISNULL(@byvBbPath,'')=''
BEGIN
SELECT @byvBbPath=rtrim(REVERSE(filename)) FROM master..sysfiles WHERE name='master'
SELECT @byvBbPath=REVERSE(substring(@byvBbPath,charindex('\',@byvBbPath),4000))
END--生成数据库恢复语句
SET @vchSql='restore '+ CASE @byvType WHEN 'LOG' THEN 'log ' ELSE 'database ' END+@byvBbName
+' FROM disk='''+@byvBkFile+''''
+' with file='+CAST(@byvFileNo as varchar)
+ CASE WHEN @byvOverSign=1 AND @byvType in('DB','DBNOR') THEN ',replace' ELSE '' END
+ CASE @byvType WHEN 'DBNOR' THEN ',NORECOVERY' ELSE ',RECOVERY' END--添加移动逻辑文件的处理
IF @byvType='DB' or @byvType='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='''+@byvBkFile+'''')
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 @vchSql=@vchSql+',move '''+@lfn+''' to '''+@byvBbPath+@byvBbName+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 @byvOverSign=1 AND @byvKillSign=1
BEGIN
DECLARE @spid varchar(20)
DECLARE #spid cursor for
SELECT spid=cast(spid as varchar(20)) FROM master..sysprocesses WHERE dbid=db_id(@byvBbName)
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(@vchSql)GO
1.停止SQL服务;
2.复制DB1数据库;
3.附加数据库,目录选中复制的DB1数据库文件;
4.附加为"DB2"
5.ok!
你的方法失败!
用备份,还原 可以!
1.备份数据库DB1;
2.还原数据库备份为DB2;
这样总可以吧.