如何用一个备份文件恢复生成四个不同名的数据库（结构完全一样）谢谢！（在线等待）

#1

USE master
GO

IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spForceRestoreDB]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spForceRestoreDB]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE PROCEDURE spForceRestoreDB
    @DatabaseName varchar(50),      --要恢复的数据库名
    @BackupFile varchar(255)        --备份文件路径
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @exists int

IF NOT EXISTS( select name from master.dbo.sysdatabases WHERE name = @DatabaseName)
BEGIN
PRINT ' Database ' + @DatabaseName + ' not found '
PRINT ' Enter valid Datbase name'
RETURN
END

EXEC master.dbo.xp_fileexist @BackupFile , @exists OUTPUT
if (@exists = 0)
BEGIN
PRINT ' File ' + @BackupFile + ' Does bot Exist'
PRINT ' Database cannot be restored'
PRINT ' Enter the valid Backup File'
RETURN
END

     -- Cursor for all the spids running against this database
DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid = ( SELECT dbid FROM master.dbo.sysdatabases
   WHERE name = @DatabaseName)

DECLARE @SysProcId smallint

-- Opens the Cursor
OPEN SysProc

-- Fetch the Process ID into the cursor
FETCH NEXT FROM SysProc INTO @SysProcId

DECLARE @KillStatement char(30)

WHILE @@FETCH_STATUS = 0
BEGIN
SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))

-- Kills the processes running against the database
EXEC (@KillStatement)

FETCH NEXT FROM SysProc INTO @SysProcId
END

WAITFOR DELAY '000:00:01'

DECLARE @strSql varchar(2000)

SET @strSql = 'RESTORE DATABASE '
SET @strSql = @strSql + QUOTENAME(@DatabaseName)
SET @strSql = @strSql + 'FROM  DISK = N'+ '''' + @BackupFile + ''''
SET @strSql = @strSql + ' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY ,  REPLACE'

--PRINT @strSql

-- Restore the Database
EXEC (@strSql)

SET NOCOUNT OFF
END
GO

#2

create database db1
create database db2
create database db3
create database db4
go

RESTORE DATABASE db1
   FROM DISK = 'c:\Sz_Stock.bak'
   WITH MOVE 'db1' TO 'c:\test\db1.mdf',
   MOVE 'db1_log' TO 'c:\test\db1.ldf'

RESTORE DATABASE db2
   FROM DISK = 'c:\Sz_Stock.bak'
   WITH MOVE 'db2' TO 'c:\test\db2.mdf',
   MOVE 'db2_log' TO 'c:\test\db2.ldf'

RESTORE DATABASE db1
   FROM DISK = 'c:\Sz_Stock.bak'
   WITH MOVE 'db3' TO 'c:\test\db3.mdf',
   MOVE 'db3_log' TO 'c:\test\db3.ldf'

RESTORE DATABASE db1
   FROM DISK = 'c:\Sz_Stock.bak'
   WITH MOVE 'db4' TO 'c:\test\db4.mdf',
   MOVE 'db4_log' TO 'c:\test\db4.ldf'
go

#3

USE master
GO

IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spForceRestoreDB]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spForceRestoreDB]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE PROCEDURE spForceRestoreDB
    @DatabaseName varchar(50),       --要恢复的数据库名
    @BackupFile varchar(255),        --备份文件路径
    @NewDataFilePath  varchar(255),  --新物理文件位置
    @NewLogFilePath  varchar(255)    --新日志文件位置
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @exists int

IF NOT EXISTS( select name from master.dbo.sysdatabases WHERE name = @DatabaseName)
BEGIN
PRINT ' Database ' + @DatabaseName + ' not found '
PRINT ' Enter valid Datbase name'
RETURN
END

EXEC master.dbo.xp_fileexist @BackupFile , @exists OUTPUT
if (@exists = 0)
BEGIN
PRINT ' File ' + @BackupFile + ' Does bot Exist'
PRINT ' Database cannot be restored'
PRINT ' Enter the valid Backup File'
RETURN
END

     -- Cursor for all the spids running against this database
DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid = ( SELECT dbid FROM master.dbo.sysdatabases
   WHERE name = @DatabaseName)

DECLARE @SysProcId smallint

-- Opens the Cursor
OPEN SysProc

-- Fetch the Process ID into the cursor
FETCH NEXT FROM SysProc INTO @SysProcId

DECLARE @KillStatement char(30)

WHILE @@FETCH_STATUS = 0
BEGIN
SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))

-- Kills the processes running against the database
EXEC (@KillStatement)

FETCH NEXT FROM SysProc INTO @SysProcId
END

WAITFOR DELAY '000:00:01'

        create table #filelist
       (LogicalName varchar(255),
        PhysicalName varchar(255),
        Type varchar(20),
        FileGroupName varchar(255),
        Size varchar(20),
        MaxSize varchar(20) )
        declare @cmd varchar(200)
                ,@DataLogicName varchar(20)
                ,@logLogicName varchar(20)
        select @cmd = 'RESTORE FILELISTONLY FROM disk = '''+ @BackupFile + ''''
        insert # exec(@cmd)

        select @DataLogicName=LogicalName from # where Type='D'
        select @logLogicName=LogicalName from # where Type='L'
        drop table #

DECLARE @strSql varchar(2000)

SET @strSql = 'RESTORE DATABASE '
SET @strSql = @strSql + QUOTENAME(@DatabaseName)
SET @strSql = @strSql + 'FROM  DISK = N'+ '''' + @BackupFile + ''''
SET @strSql = @strSql + ' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY ,  REPLACE'
        SET @strSql = @strSql + ',Move '''+@DataLogicName+''' to '''+@NewDataFilePath+''''
        SET @strSql = @strSql + ',Move '''+@logLogicName+''' to '''+@NewLogFilePath+''''

--PRINT @strSql
-- Restore the Database
EXEC (@strSql)

SET NOCOUNT OFF
END
GO

#4

vivianfdlpw兄，你写的不错，把 # 写完全 #filelist 即可完成，不知能不能做得更智能化一点，即我传一个要新建的 database 名和要恢复的备份文件名即可呢？自动帮我建DATABASE，再自动找到对应的SQL 路径，再恢复即可，我即在VB里执行这个带参数的存储过程即可，应为客户的机子可能路径是变动的，太谢谢你了！

#5

手动最简单的方法：
1、新建数据库BeiJing_Stock、SH_Stock、SZ_Stock和Gz_Stock
2、Sz_Stock_Data.mdf、Sz_Stock_Log.ldf拷贝四份下来，发到其他地方，分别改为：BeiJing_Stock_Data.mdf、BeiJing_Stock_Log.ldf；
SH_Stock_Data.mdf、SH_Stock_Log.ldf；
SZ_Stock_Data.mdf、SZ_Stock_Log.ldf；
Gz_Stock_Data.mdf、Gz_Stock_Log.ldf。
3、关闭sqlserver2k，用更改过得.mdf、.ldf文件覆盖原有的.mdf、.ldf文件。重新打开sqlserver2k即可

#6

参考下面的SQL，把@ver改掉重复4次
USE master
GO
  declare @spid int,@sql varchar(2000),@ver varchar(6),@oldbak varchar(255)
  set nocount on
  set @ver = 'F262'
  set @oldbak='d:\version\ECM_2.X\F261\DB\DB_F261.bak'
  /** 强行kill使用@ver数据库的所有用户连接 **/
  create table #t
  (
    spid int,
    ecid int ,
    status varchar(30),
    loginame varchar(32),
    hostname varchar(32),
    blk int,
    dbname varchar(20),
    cmd varchar(60)
  )

  insert into #t execute sp_who

  declare cur_spid insensitive cursor for
    select spid from #t where dbname = @ver

  open cur_spid
  fetch next from cur_spid into @spid
  while @@fetch_status <> -1
  begin
    select @sql='kill '+convert(varchar,@spid)
    execute(@sql)
    fetch next from cur_spid into @spid
  end
  close cur_spid
  deallocate cur_spid
  drop table #t
  /*** 恢复数据库 **/
  set @sql='RESTORE DATABASE '+@ver+' FROM DISK = '''+@oldbak+''' WITH  MOVE '
            +'''F250_Data'' TO ''d:\db\'+@ver+'.mdf'','
            +' MOVE ''F250_log'' TO ''d:\db\'+@ver+'_log.ldf'''
            +',  REPLACE'
  set @sql = @sql+char(13)+'USE '+@ver+char(13)+'execute sp_updatestats'
  print @sql
  execute (@sql)
GO

#7

在企业管理器中操作：
1、新建数据库BeiJing_Stock、SH_Stock、SZ_Stock和Gz_Stock
2、用备份文件分别在新建的数据库上还原数据库
3、刷新，ok

#8

按照你的需求改了一下：

USE master
GO

IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spForceRestoreDB]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spForceRestoreDB]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE PROCEDURE spForceRestoreDB
    @DatabaseName varchar(50),            --要恢复的数据库名
    @BackupFile varchar(255),             --备份文件路径
    @NewDataFilePath  varchar(255)=null,  --新物理文件位置
    @NewLogFilePath  varchar(255)=null    --新日志文件位置
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @exists int

IF NOT EXISTS( select name from master.dbo.sysdatabases WHERE name = @DatabaseName)
BEGIN
PRINT ' Database ' + @DatabaseName + ' not found '
PRINT ' Enter valid Datbase name'
RETURN
END

EXEC master.dbo.xp_fileexist @BackupFile , @exists OUTPUT
if (@exists = 0)
BEGIN
PRINT ' File ' + @BackupFile + ' Does bot Exist'
PRINT ' Database cannot be restored'
PRINT ' Enter the valid Backup File'
RETURN
END

     -- Cursor for all the spids running against this database
DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid = ( SELECT dbid FROM master.dbo.sysdatabases
   WHERE name = @DatabaseName)

DECLARE @SysProcId smallint

-- Opens the Cursor
OPEN SysProc

-- Fetch the Process ID into the cursor
FETCH NEXT FROM SysProc INTO @SysProcId

DECLARE @KillStatement char(30)

WHILE @@FETCH_STATUS = 0
BEGIN
SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))

-- Kills the processes running against the database
EXEC (@KillStatement)

FETCH NEXT FROM SysProc INTO @SysProcId
END

WAITFOR DELAY '000:00:01'

        create table #filelist
       (LogicalName varchar(255),
        PhysicalName varchar(255),
        Type varchar(20),
        FileGroupName varchar(255),
        Size varchar(20),
        MaxSize varchar(20) )
        declare @cmd varchar(200)
                ,@DataLogicName varchar(20)
                ,@logLogicName varchar(20)
        select @cmd = 'RESTORE FILELISTONLY FROM disk = '''+ @BackupFile + ''''
        insert # exec(@cmd)

        select @DataLogicName=LogicalName from # where Type='D'
        select @logLogicName=LogicalName from # where Type='L'
        drop table #

        if @NewDataFilePath is null
        begin
           select @NewDataFilePath=replace(filename,'master.mdf','') from master..sysdatabases
           where dbid=db_id('master')
           select @NewDataFilePath=@NewDataFilePath+@DatabaseName+'_Data.mdf'
        end

        if @NewLogFilePath is null
        begin
           select @NewLogFilePath=replace(filename,'master.mdf','') from master..sysdatabases
           where dbid=db_id('master')
           select @NewLogFilePath=@NewLogFilePath+@DatabaseName+'_Log.ldf'
        end

DECLARE @strSql varchar(2000)

SET @strSql = 'RESTORE DATABASE '
SET @strSql = @strSql + QUOTENAME(@DatabaseName)
SET @strSql = @strSql + 'FROM  DISK = N'+ '''' + @BackupFile + ''''
SET @strSql = @strSql + ' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY ,  REPLACE'
        SET @strSql = @strSql + ',Move '''+@DataLogicName+''' to '''+@NewDataFilePath+''''
        SET @strSql = @strSql + ',Move '''+@logLogicName+''' to '''+@NewLogFilePath+''''

--PRINT @strSql
-- Restore the Database
EXEC (@strSql)

SET NOCOUNT OFF
END
GO

#9

不好意思，忘了这个版本#和#filelist的问题，下面是最新的,在我的blog上：

http://blog.csdn.net/vivianfdlpw/archive/2005/09/28/491043.aspx

USE master
GO
IF EXISTS (SELECT * FROM sysobjects
       WHERE id = object_id(N'[dbo].[spForceRestoreDB]')
      AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spForceRestoreDB]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/*
   强制还原已存在的数据库，基本原理是Kill掉数据库的所有会话
   原贴在http://www.sqlservercentral.com/scripts/contributions/827.asp
   在此基础上添加了with move选项,如果不提供新文件物理位置，则默认为SQL Server
   安装目录

   调用；
   use master
   go
   exec master..spForceRestoreDB 'test2'
                                 ,'c:\test.bak'
                                 ,'c:\db\test_data.mdf'
                                 ,'c:\db\test_log.mdf'

   vivianfdlpw 2005.9 引用请保留此信息
*/

CREATE PROCEDURE spForceRestoreDB
    @DatabaseName varchar(50),            --要恢复的数据库名
    @BackupFile varchar(255),             --备份文件路径
    @NewDataFilePath  varchar(255)=null,  --新物理文件位置
    @NewLogFilePath  varchar(255)=null    --新日志文件位置
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @exists int

IF NOT EXISTS( select name from master.dbo.sysdatabases WHERE name = @DatabaseName)
BEGIN
PRINT '  Database ' + @DatabaseName + ' not found '
PRINT '  Enter valid Datbase name'
RETURN
END

EXEC master.dbo.xp_fileexist @BackupFile , @exists OUTPUT
if (@exists = 0)
BEGIN
PRINT '  File ' + @BackupFile + ' Does bot Exist'
PRINT '  Database cannot be restored'
PRINT '  Enter the valid Backup File'
RETURN
END

     -- Cursor for all the spids running against this database
DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid = ( SELECT dbid FROM master.dbo.sysdatabases
     WHERE name = @DatabaseName)

DECLARE @SysProcId smallint

-- Opens the Cursor
OPEN SysProc

-- Fetch the Process ID into the cursor
  FETCH NEXT FROM SysProc INTO @SysProcId

DECLARE @KillStatement char(30)

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))

  -- Kills the processes running against the database
  EXEC (@KillStatement)

  FETCH NEXT FROM SysProc INTO @SysProcId
END

WAITFOR DELAY '000:00:01'

        create table #
       (LogicalName varchar(255),
        PhysicalName varchar(255),
        Type varchar(20),
        FileGroupName varchar(255),
        Size varchar(20),
        MaxSize varchar(20) )
        declare @cmd varchar(200)
                ,@DataLogicName varchar(20)
                ,@logLogicName varchar(20)
        select @cmd = 'RESTORE FILELISTONLY FROM disk = '''+ @BackupFile + ''''
        insert # exec(@cmd)

        select @DataLogicName=LogicalName from # where Type='D'
        select @logLogicName=LogicalName from # where Type='L'
        drop table #

        if @NewDataFilePath is null
        begin
           select @NewDataFilePath=replace(filename,'master.mdf','') from master..sysdatabases
           where dbid=db_id('master')
           select @NewDataFilePath=@NewDataFilePath+@DatabaseName+'_Data.mdf'
        end

        if @NewLogFilePath is null
        begin
           select @NewLogFilePath=replace(filename,'master.mdf','') from master..sysdatabases
           where dbid=db_id('master')
           select @NewLogFilePath=@NewLogFilePath+@DatabaseName+'_Log.ldf'
        end

DECLARE @strSql varchar(2000)

SET @strSql = 'RESTORE DATABASE '
SET @strSql = @strSql + QUOTENAME(@DatabaseName)
SET @strSql = @strSql + 'FROM  DISK = N'+ '''' + @BackupFile + ''''
SET @strSql = @strSql + ' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY ,  REPLACE'
        SET @strSql = @strSql + ',Move '''+@DataLogicName+''' to '''+@NewDataFilePath+''''
        SET @strSql = @strSql + ',Move '''+@logLogicName+''' to '''+@NewLogFilePath+''''

--PRINT @strSql
-- Restore the Database
EXEC (@strSql)

SET NOCOUNT OFF
END
GO

#10

给个VB的过程给你吧，也一样的
'********************************
'
'功能：还原数据库(从数据库复本)
'输入参数：
'--Conn:对数据库连接的引用
'--ComDlg:对公共对话框的引用
'--DataBaseName:数据库名
'--DBFileMove：数据库文件移动到,默认不移动(为“”时表不移动)
'--LogFileName:数据库日志移动到，默认不移动（为“”时表不移动）
'返回值：
'--RestoreDataBase:返回数据库还原的状态，1为成功，0为失败
'
'********************************

Function RestoreDataBase(ByRef Conn As ADODB.Connection, ByRef FileName As String, ByVal DataBaseName As String, _
                         Optional DBFileMove As String = "", Optional LogFileMove As String) As Boolean
    Dim Rstemp As ADODB.Recordset
    Dim MoveStr As String               '移动字符串
    Dim Sqlstr As String
    Dim i As Integer                    '临时用计数

    '文件名是否存在
    If Trim(FileName) = "" Or (Trim(FileName) <> "" And Dir(FileName) = "") Then
        MsgBox "输入数据库文件不存在，请检查！", vbInformation, "文件不存在"
        Set Rstemp = Nothing
        RestoreDataBase = False
        Exit Function
    End If

    On Error GoTo ErrHandle
    If DBFileMove = "" Or LogFileMove = "" Then
        Set Rstemp = Conn.Execute("select filename from master..sysdatabases where name='" & DataBaseName & "'")
        '数据库文件移动位置为空时，
        If DBFileMove = "" Then
            DBFileMove = Rstemp!FileName
        End If

        '日志文件移动位置为空时
        If LogFileMove = "" Then
            LogFileMove = Left$(Trim(Rstemp!FileName), Len(Trim(Rstemp!FileName)) - 3) & "LDF"
        End If
    End If

    '取得备份文件的数据库文件名,并得到数据库移动字符串
    Set Rstemp = Conn.Execute("RESTORE FILELISTONLY FROM DISK = '" & FileName & "'")
    MoveStr = "With Move '" & Rstemp("LogicalName") & "' To '" & DBFileMove & "'" + vbCrLf
    Rstemp.MoveNext
    '如果前面不移动数据库文件则
    MoveStr = MoveStr & ",Move '" & Rstemp("LogicalName") & "' To '" & LogFileMove & "'" + vbCrLf

    '构造还原语句
    Sqlstr = "RESTORE DATABASE " & DataBaseName & " FROM DISK = '" & FileName & "'" + vbCrLf
    Sqlstr = Sqlstr & MoveStr & ",replace"

    '把数据库相关的进程关闭
    Set Rstemp = Conn.Execute("sp_who")
    Rstemp.Filter = "dbname='" & Trim(DataBaseName) & "'"

    '数据库正在使用，提示
    If Not (Rstemp.EOF Or Rstemp.BOF) Then
        If MsgBox("某个过程或用户正在使用数据库，是否强制还原？", vbOKCancel + vbQuestion) = vbCancel Then
            Set Rstemp = Nothing
            RestoreDataBase = False
            Exit Function
        End If

        '关闭打开的进程
        Rstemp.MoveFirst
        Do While Not (Rstemp.EOF Or Rstemp.BOF)
            Conn.Execute ("Kill " & Rstemp("spid"))
            Rstemp.MoveNext
        Loop
    End If

    '开始还原数据库
    Conn.Execute (Sqlstr)

    MsgBox "还原数据库" & DataBaseName & "成功!", vbInformation, "提示"
    RestoreDataBase = True
    Set Rstemp = Nothing
    Exit Function
ErrHandle:
    Set Rstemp = Nothing
    RestoreDataBase = False
    MsgBox Err.Source & ":" & Err.Description, vbCritical, "出错"
    mComCtrl.WriteErrLog Err, "OpDbCtrl", "RestoreDataBase"
End Function

#11

mark

秒客网

如何用一个备份文件恢复生成四个不同名的数据库（结构完全一样）谢谢！（在线等待）

11 个解决方案

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

相关文章