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 = 'E:\05_DataBase_bakup\'   
--数据库服务器
SET @dbServer = '.'   
--数据库用户名        
SET @dbUserName = 'sa'  
--数据库用户密码         
SET @dbUserPwd = '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)   
--数据库备份文件存放路径
SET @path = 'E:\05_DataBase_bakup\' 
--数据库还原后的文件存放路径  
SET @restorePath = 'E:\db2008\'   
--还原到的数据库服务器
SET @dbServer = '192.168.0.7,1501\SQL2008'   
--还原到的数据库用户名       
SET @dbUserName = 'netuser'  
--还原到的数据库用户密码        
SET @dbUserPwd = 'netuser2008'        
DECLARE cursors     CURSOR  
FOR
    --查询集合
    --注意:这里将数据库文件的物理路径替换掉了一部分,只保留数据库名称    
    SELECT [name],
           REPLACE  (REPLACE(FILENAME, 'E:\01.数据库\', ''), '.mdf', '') AS 
           [fileName]
    FROM   [sysdatabases]
    WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb')  
             
 OPEN cursors                       
 FETCH NEXT FROM cursors INTO @name,@fileName                                                                 
WHILE @@fetch_status = 0
BEGIN
    --遍历集合
    PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd +
    ' -Q "RESTORE DATABASE [' + @name + '] FROM DISK = ''' + @path + @name +
    '.bak'' WITH  FILE = 1,  MOVE N''' + @fileName + ''' TO N''' + @restorePath 
    + @name
    + '.mdf'',  MOVE N''' + @fileName + '_log'' TO N''' + @restorePath + @name +
    '.LDF'', NOUNLOAD,  REPLACE,STATS = 10' 
    FETCH NEXT FROM cursors INTO @name,@fileName
END
 CLOSE cursors                
 DEALLOCATE cursors 




目录
相关文章
|
5天前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量:数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中,变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”,用于存储程序运行时的动态数据,通过`DECLARE`定义,可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”,一旦设定值便保持不变,用`CONSTANT`声明,提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性,可以更高效地管理和控制变量与常量,提升代码质量。善用两者,能优化PL/SQL程序的结构和性能。
|
24天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点:** 1. **批量插入**提高效率,一次性建议不超过500条。 2. **手动事务**减少开销,多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂,提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**,减少不必要的磁盘操作。 6. **选择合适主键类型**,避免UUID或长主键导致的性能问题。 7. **避免主键修改**,保持索引稳定。 这些技巧能优化数据库操作,提升系统性能。
219 4
一文搞懂SQL优化——如何高效添加数据
|
1月前
|
SQL 数据可视化 数据处理
使用SQL和Python处理Excel文件数据
使用SQL和Python处理Excel文件数据
54 0
|
5天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
5天前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句:数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句(LOOP、EXIT WHEN、FOR、WHILE)是处理数据的关键工具,用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环,可通过EXIT WHEN设定退出条件;FOR循环适用于固定次数迭代,WHILE循环基于条件判断执行。有效使用循环能提高效率,但需注意避免无限循环和优化大数据处理性能。掌握循环语句,将使数据处理更加高效和便捷。
|
5天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL条件控制:数据的“红绿灯”与“分岔路”
【4月更文挑战第19天】在Oracle PL/SQL中,IF语句与CASE语句扮演着数据流程控制的关键角色。IF语句如红绿灯,依据条件决定程序执行路径;ELSE和ELSIF提供多分支逻辑。CASE语句则是分岔路,按表达式值选择执行路径。这些条件控制语句在数据验证、错误处理和业务逻辑中不可或缺,通过巧妙运用能实现高效程序逻辑,保障数据正确流转,支持企业业务发展。理解并熟练掌握这些语句的使用是成为合格数据管理员的重要一环。
|
5天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式:数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式,体验数据的魔法公式。表达式结合常量、变量、运算符和函数,用于数据运算与转换。算术运算符处理数值计算,比较运算符执行数据比较,内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序,助你驾驭数据,揭示其背后的规律与秘密,成为数据魔法师。
|
27天前
|
SQL 关系型数据库 MySQL
SQL INSERT INTO order_record SELECT * FROM 从一张表查出数据插入到另一张表
SQL INSERT INTO order_record SELECT * FROM 从一张表查出数据插入到另一张表
14 0
|
1月前
|
SQL 安全 数据库
第三章用sql语句操作数据
第三章用sql语句操作数据
10 0
|
1月前
|
SQL 存储 数据库
数据安全无忧,SQL Server 2014数据库定时备份解密
数据安全无忧,SQL Server 2014数据库定时备份解密