MSSQL数据还原

最新推荐文章于 2023-12-01 16:46:59 发布

angelior

最新推荐文章于 2023-12-01 16:46:59 发布

阅读量648

点赞数 1

文章标签：文档 disk database 数据库 table insert

本文链接：https://blog.csdn.net/angelior/article/details/2669373

版权

---------------------------------------------------------------------
--还原数据库 v2.0 只要指定数据库，定到数库据，及备份文件路径就可以还原 angelior 2008-6-25

/*创建临时表,保存由备份集内包含的数据库和日志文档列表组成的结果集*/
--drop table #tem

---还原
Declare @BackFile varchar(1000)
Declare @DBName varchar(50)

use HQS ---(先到指定的库)
set @DBName='HQS'
set @BackFile = 'D:/DJ01001/HQS0807130950.bak'

set nocount on
create table #tem(
logicalname nvarchar(128), /*文档的逻辑名称*/
physicalname nvarchar(260) , /*文档的物理名称或操作系统名称*/
type char(1), /*数据文档 (d) 或日志文档 (l)*/
filegroupname nvarchar(128), /*包含文档的文档组名称*/
[size] numeric(20,0), /*当前大小（以字节为单位）*/
[maxsize] numeric(20,0) /*允许的最大大小（以字节为单位）*/
)

insert into #tem
execute('restore filelistonly from disk=''' + @BackFile +'''')

Declare @MDF_LGName varchar(50) --逻辑文件名
Declare @LDF_LGName varchar(50) --逻辑文件名

select @MDF_LGName=''''+ logicalname +'''' from #tem where rtrim(physicalname) like '%.MDF'
select @LDF_LGName=''''+ logicalname +'''' from #tem where rtrim(physicalname) like '%.LDF'

--print @MDF_LGName
--print @LDF_LGName

drop table #tem

Declare @DBMDFFile varchar(1000)
Declare @DBLOGFile varchar(1000)

select @DBMDFFile=''''+ rtrim(filename) +'''' from sysfiles where rtrim(filename) like '%.MDF'
select @DBLOGFile=''''+ rtrim(filename)+ '''' from sysfiles where rtrim(filename) like '%.LDF'

--print @DBMDFFile
--print @DBLOGFile
use master;

--断开所有用户使用
declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id(@DBName) exec sp_msforeach_worker '?'

set nocount off

--RESTORE filelistonly from disk='D:/Ftp/POS200806221905.bak' with file=2
--RESTORE LABELONLY from disk='D:/Ftp/POS200806221905.bak'
--RESTORE HEADERONLY from disk='D:/Ftp/POS200806221905.bak'

--还原
Declare @OutSql varchar(1000)

SET @BackFile= '''' + @BackFile +''''

set @OutSql = 'RESTORE DATABASE ' + @DBName +'
   FROM DISK = ' +@BackFile +'
    WITH REPLACE,
    move ' + @MDF_LGName + ' to ' + @DBMDFFile +',
    move ' + @LDF_LGName + ' to ' + @DBLOGFile

--PRINT @BackFile
execute(@OutSql)

PRINT ''
PRINT ''
print @OutSql

/*
RESTORE DATABASE XTPOS
   FROM DISK = @BackFile
    WITH REPLACE,
    move @MDF_LGName to @DBMDFFile ,
    move @LDF_LGName to @DBLOGFile
*/
---------------------------------------------------------------------