MS SQL数据批量备份还原(适用于MS SQL 2005+)

简介: 原文:MS SQL数据批量备份还原(适用于MS SQL 2005+) 我们知道通过Sql代理,可以实现数据库的定时备份功能;当数据库里的数据库很多时,备份一个数据库需要建立对应的定时作业,相对来说比较麻烦; 还好,微软自带的osql工具,比较实用,通过在命令行里里输入命令,也能实现数据库备份的功能;如果能通过sql语句来生成一个批处理文件,那就方便了; 下面即是生成批处理文件的sql脚本,通过它可以实现:运行一个批处理文件,备份数据库服务器上的所有数据库到指定目录。

原文:MS SQL数据批量备份还原(适用于MS SQL 2005+)

我们知道通过Sql代理,可以实现数据库的定时备份功能;当数据库里的数据库很多时,备份一个数据库需要建立对应的定时作业,相对来说比较麻烦;

还好,微软自带的osql工具,比较实用,通过在命令行里里输入命令,也能实现数据库备份的功能;如果能通过sql语句来生成一个批处理文件,那就方便了;

下面即是生成批处理文件的sql脚本,通过它可以实现:运行一个批处理文件,备份数据库服务器上的所有数据库到指定目录。

生成批量备份脚本:

/************************************************************
 * 数据备份脚本:
 * 1.运行前,请根据实际配置修改相应账户信息;
 * 2.请将脚本执行结果另存为批处理文件,执行批处理文件即可;
 * 时间: 2015/3/10 14:21:15
 ************************************************************/

DECLARE @name NVARCHAR(200);
DECLARE @path NVARCHAR(500);
DECLARE @dbServer NVARCHAR(500);
DECLARE @dbUserName NVARCHAR(500);
DECLARE @dbUserPwd NVARCHAR(500);
--备份文件存放路径
SET @path = N'E:\05_DataBase_bakup\';
--数据库服务器
SET @dbServer = N'.';
--数据库用户名        
SET @dbUserName = N'sa';
--数据库用户密码         
SET @dbUserPwd = N'123';
DECLARE cursors CURSOR FOR
--查询集合
SELECT [name]
FROM [sysdatabases]
WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb' );

OPEN cursors;
FETCH NEXT FROM cursors
INTO @name;
WHILE @@fetch_status = 0
BEGIN
    --遍历集合 
    PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd + ' -Q "BACKUP DATABASE [' + @name
          + '] TO DISK = ''' + @path + '[%date:~0,4%%date:~5,2%%date:~8,2%]' + @name + '.bak''"';
    FETCH NEXT FROM cursors
    INTO @name;
END;
CLOSE cursors;
DEALLOCATE cursors;

 

生成批量还原脚本:

 

/************************************************************
 * 生成批量还原数据库脚本
 * Time: 2015/4/19 18:30:05
 ************************************************************/
DECLARE @name NVARCHAR(200);
DECLARE @fileName NVARCHAR(200);
DECLARE @path NVARCHAR(500);
DECLARE @restorePath NVARCHAR(500);
DECLARE @dbServer NVARCHAR(500);
DECLARE @dbUserName NVARCHAR(500);
DECLARE @dbUserPwd NVARCHAR(500);
DECLARE @mdfName NVARCHAR(500);
DECLARE @ldfName NVARCHAR(500);
--数据库备份文件存放路径
SET @path = N'd:\DBBAK\';
--数据库还原后的文件存放路径  
SET @restorePath = N'D:\PMSDB\';
--还原到的数据库服务器
SET @dbServer = N'127.0.0.1';
--还原到的数据库用户名       
SET @dbUserName = N'sa';
--还原到的数据库用户密码        
SET @dbUserPwd = N'xxxxxxx';
DECLARE cursors CURSOR FOR
--查询集合
--注意:这里将数据库文件的物理路径替换掉了一部分,只保留数据库名称    
SELECT [name],
       REPLACE(REPLACE(filename, 'D:\DBBAK\', ''), '.mdf', '') AS [fileName]
FROM [sysdatabases]
WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb', 'DWDiagnostics', 'DWConfiguration', 'DWQueue', 'BIReport',
                    'distribution', 'ReportServer', 'ReportServerTempDB'
                  )
ORDER BY name;
OPEN cursors;
FETCH NEXT FROM cursors
INTO @name,
     @fileName;
WHILE @@fetch_status = 0
BEGIN
    SELECT @mdfName = f.name
    FROM sysaltfiles f
        INNER JOIN sysdatabases d
            ON (f.dbid = d.dbid)
               AND d.name = @name
               AND CHARINDEX('MDF', f.filename) > 0;
    SELECT @ldfName = f.name
    FROM sysaltfiles f
        INNER JOIN sysdatabases d
            ON (f.dbid = d.dbid)
               AND d.name = @name
               AND CHARINDEX('LDF', f.filename) > 0;
    --遍历集合
    PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd + ' -Q "RESTORE DATABASE [' + @name
          + '] FROM DISK = ''' + @path + '[20170720]' + @name + '.bak'' WITH  FILE = 1,  MOVE N''' + @mdfName
          + ''' TO N''' + @restorePath + @name + '.mdf'',  MOVE N''' + @ldfName + ''' TO N''' + @restorePath + @name
          + '.LDF'', NOUNLOAD,  REPLACE,STATS = 10';
    FETCH NEXT FROM cursors
    INTO @name,
         @fileName;
END;
CLOSE cursors;
DEALLOCATE cursors;

 

  

 

 

 

 


 

目录
相关文章
|
13天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点:** 1. **批量插入**提高效率,一次性建议不超过500条。 2. **手动事务**减少开销,多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂,提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**,减少不必要的磁盘操作。 6. **选择合适主键类型**,避免UUID或长主键导致的性能问题。 7. **避免主键修改**,保持索引稳定。 这些技巧能优化数据库操作,提升系统性能。
205 4
一文搞懂SQL优化——如何高效添加数据
|
30天前
|
SQL 数据可视化 数据处理
使用SQL和Python处理Excel文件数据
使用SQL和Python处理Excel文件数据
51 0
|
23天前
|
SQL 安全 数据库
第三章用sql语句操作数据
第三章用sql语句操作数据
9 0
|
29天前
|
SQL 存储 数据库
数据安全无忧,SQL Server 2014数据库定时备份解密
数据安全无忧,SQL Server 2014数据库定时备份解密
|
1月前
|
SQL 数据库 数据库管理
SQL中如何添加数据:基础指南
SQL中如何添加数据:基础指南
24 2
|
2月前
|
SQL 数据库 数据安全/隐私保护
sql注入碰到加密数据怎么办
sql注入碰到加密数据怎么办
17 1
|
2月前
|
分布式计算 资源调度 Hadoop
Flink报错问题之Sql往kafka表写聚合数据报错如何解决
Apache Flink是由Apache软件基金会开发的开源流处理框架,其核心是用Java和Scala编写的分布式流数据流引擎。本合集提供有关Apache Flink相关技术、使用技巧和最佳实践的资源。
|
2月前
|
SQL 消息中间件 Kafka
Flink sql 问题之主动使数据延时一段时间如何解决
Apache Flink是由Apache软件基金会开发的开源流处理框架,其核心是用Java和Scala编写的分布式流数据流引擎。本合集提供有关Apache Flink相关技术、使用技巧和最佳实践的资源。
49 2
|
2月前
|
SQL Oracle 关系型数据库
Flink SQL 问题之看不到数据如何解决
Flink SQL报错通常指在使用Apache Flink的SQL接口执行数据处理任务时遇到的问题;本合集将收集常见的Flink SQL报错情况及其解决方法,帮助用户迅速恢复数据处理流程。
49 3
|
2月前
|
SQL 存储 数据库
SQL数据开发
SQL数据开发