首页 / MSSQL / MSSQL通用还原脚本
MSSQL通用还原脚本
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MSSQL通用还原脚本,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2445字,纯文字阅读大概需要4分钟。
内容图文
![MSSQL通用还原脚本](/upload/InfoBanner/zyjiaocheng/1317/7bcf419f46d54ce09f5e53c82a139b26.jpg)
MSSQL还原脚本
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/6469179/1855856
原文:http://370220760.blog.51cto.com/6469179/1855856
内容总结
以上是互联网集市为您收集整理的MSSQL通用还原脚本全部内容,希望文章能够帮你解决MSSQL通用还原脚本所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。