mssql数据库怎样还原
发布网友
共5个回答
懂视网
SQL Server通用还原脚本，只需修改第二步中，需要还原的数据库名称和路径
执行完脚本后会生成对应的还原命令，直接新建查询后执行即可
-- 2 - Initialize variables
SET @dbName = ‘Customer‘
SET @backupPath = ‘D:SQLBackups‘
脚本如下：
--open-- xp_cmdshell
sp_configure ‘show advanced options‘,1
reconfigure
go
sp_configure ‘xp_cmdshell‘,1
reconfigure
go
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = ‘Customer‘
SET @backupPath = ‘D:SQLBackups‘
-- 3 - get list of files
SET @cmd = ‘DIR /b ‘ + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE ‘%.BAK‘
AND backupFile LIKE @dbName + ‘%‘
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ FROM DISK = ‘‘‘
+ @backupPath + @lastFullBackup + ‘‘‘ WITH NORECOVERY, REPLACE‘
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE ‘%.DIF‘
AND backupFile LIKE @dbName + ‘%‘
AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ FROM DISK = ‘‘‘
+ @backupPath + @lastDiffBackup + ‘‘‘ WITH NORECOVERY‘
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE ‘%.TRN‘
AND backupFile LIKE @dbName + ‘%‘
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘RESTORE LOG ‘ + @dbName + ‘ FROM DISK = ‘‘‘
+ @backupPath + @backupFile + ‘‘‘ WITH NORECOVERY‘
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ WITH RECOVERY‘
PRINT @cmd
本文出自 “计算机网络技术” 博客，请务必保留此出处http://370220760.blog.51cto.com/69179/1855856
MSSQL通用还原脚本
标签：mssql还原脚本
热心网友
分两种情况，一个是，对已经存在的数据进行替换，直接选bak文件，还原就可以了。
第二个是，数据库名字不一样的还原，这种你学要选中bak后，有一个log的地方，把文件名字改成修改后的数据库文件，要不会报错的。
热心网友
右键，还原数据库，按步骤下来就可以了
热心网友
有数据脚本直接执行脚本就行了
热心网友
您是空间？还是服务器？
