SQL Server2019数据库备份与还原脚本,数据库可批量备份

前言最近公司服务器到期,需要进行数据迁移,而数据库属于多而繁琐,通过图形化界面一个一个备份所需时间成本很大,所以想着写一个sql脚本来执行。开始

数据库单个备份

数据库批量备份

数据库还原

数据库还原报错问题记录

总结

1.数据库单个备份图形化界面备份这里就不展示了,可以自行百度,下面直接贴代码

USE MASTER
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE BackupDataProc
go
create proc BackupDataProc
@FullName Varchar(200)--入参(数据库名)
as
begin
Declare @FileFlag varchar(50)
Set @FileFlag='C:\myfile\database\'+@FullName+'.bak'--备份到哪个路径(C:\myfile\database\)根据自己需求来定
BackUp DataBase @FullName To Disk=@FileFlag with init--核心代码
end

exec BackupDataProc xxx

执行成功后便会生成一个.bak文件到指定文件夹中,如图

2.数据库批量备份(时间有点长,请等待)

USE MASTER
if exists(SELECT * FROM sys.types WHERE name = 'AllDatabasesNameType')
drop type AllDatabasesNameType
go
create type AllDatabasesNameType as table--自定义表类型用于存储数据库名称
(
rowNum int ,
name nvarchar(60),
filename nvarchar(300)
)
go
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BachBackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE BachBackupDataProc
go
create proc BachBackupDataProc
@filePath nvarchar(300)--入参,备份时的目标路径
as
begin
Declare @AllDatabasesName as AllDatabasesNameType --用于存储系统中的数据库名
Declare @i int --循环变量
insert into @AllDatabasesName(name,filename,rowNum) select name,filename,ROW_NUMBER() over(order by name) as rowNum from sysdatabases where name not in('master','tempdb','model','msdb') --赋值
set @i =1
--循环备份数据库
while @i <= (select COUNT(*) from @AllDatabasesName)
begin
Declare @FileFlag varchar(500)
Declare @FullName varchar(50)
Select @FullName =name from @AllDatabasesName where rowNum = @i
Set @FileFlag=@filePath+@FullName+'.bak'
BackUp DataBase @FullName To Disk=@FileFlag with init
set @i = @i + 1
end
end

exec BachBackupDataProc 'C:\myfile\database\'

执行结果效果如下图:

3.数据库还原

IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[ReductionProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE ReductionProc
go
create proc ReductionProc
@Name nvarchar(200)--入参 数据库名称
as
begin
Declare @DiskName nvarchar(500)
Declare @FileLogName nvarchar(100)
Declare @FileFlagData nvarchar(500)
Declare @FileFlagLog nvarchar(500)
Set @FileLogName = @Name + '_log'
Set @DiskName = 'C:\myfile\database\'+@Name+'.bak' ---(源)备份文件路径
Set @FileFlagData='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@Name+'.mdf'---(目标)指定数据文件路径
Set @FileFlagLog='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@FileLogName+'.ldf'---目标)指定日志文件路径
RESTORE DATABASE @Name --为待还原库名
FROM DISK = @DiskName ---备份文件名
WITH MOVE @Name TO @FileFlagData, ---指定数据文件路径
MOVE @FileLogName TO @FileFlagLog, ---指定日志文件路径
STATS = 10, REPLACE
end
go

exec ReductionProc xxx

执行后便能还原库(我是拿这三个库做测试,截的图可能没什么变化,你们可以尝试下)

4.数据库还原报错问题记录

当然还原的过程可能会遇到一些问题,比如:

1.版本不一样
2.SQL Sql 逻辑文件'XXXXX ' 不是数据库'YYY'的一部分。请使用 RESTORE FILELISTONLY 来列出逻辑文件名。

版本的话我试过了,高版本可以向下兼容,但是低版本不能向上兼容,可以统一版本来解决(如有更好的解决方案欢迎打扰)
第二个问题呢就是脚本中‘MOVE' 他只能跟逻辑名,而有些数据库的逻辑名并不是数据库名称,所以需要替换一下,

下面是查询数据库逻辑名的sql语句:
USE MASTER
restore filelistonly from disk='D:\sql201database\Sence.bak'--根据自己的需求要变更路径

如图,

对于这些逻辑名与数据库名称不一致的情况可以单独拿出来重新执行一下即可:

USE MASTER --这里注意要使用MASTER,以免出现待还原库被占用的情况
RESTORE DATABASE Sence --为待还原库名
FROM DISK = 'D:\sql201database\Sence.bak' ---备份文件名
WITH MOVE 'Sence_Guangxi' TO 'D:\Database\Data\Sence.mdf', ---指定数据文件路径
MOVE 'Sence_Guangxi_log' TO 'D:\Database\Data\Sence_log.ldf', ---指定日志文件路径
STATS = 10, REPLACE
GO

5.总结

数据是无价的,对数据库操作时备份是必须的。
数据是无价的,对数据库操作时备份是必须的。
数据是无价的,对数据库操作时备份是必须的。(重要的事说三遍)

到此这篇关于SQL Server2019数据库备份与还原脚本,数据库可批量备份的文章就介绍到这了,更多相关SQL Server2019数据库备份与还原脚本,数据库可批量备份内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • SQL Server2019数据库备份与还原脚本,数据库可批量备份

    前言最近公司服务器到期,需要进行数据迁移,而数据库属于多而繁琐,通过图形化界面一个一个备份所需时间成本很大,所以想着写一个sql脚本来执行.开始 数据库单个备份 数据库批量备份 数据库还原 数据库还原报错问题记录 总结 1.数据库单个备份图形化界面备份这里就不展示了,可以自行百度,下面直接贴代码 USE MASTER IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BackupDataProc]') AND OBJECTP

  • SQL Server2019数据库备份与还原脚本(批量备份)

    前言最近公司服务器到期,需要进行数据迁移,而数据库属于多而繁琐,通过图形化界面一个一个备份所需时间成本很大,所以想着写一个sql脚本来执行. 开始 数据库单个备份 数据库批量备份 数据库还原 数据库还原报错问题记录 总结 1.数据库单个备份 图形化界面备份这里就不展示了,可以自行百度,下面直接贴代码 USE MASTER IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BackupDataProc]') AND OBJEC

  • MySQL数据备份、还原、数据库迁移以及表的导出和导入

    目录 前言 一.数据备份 1.使用mysqldump命令备份 2.直接复制整个数据库目录 3.使用mysqlhotcopy工具快速备份 二.数据还原 1.使用mysql命令还原 2.直接复制到数据库目录 三.数据库迁移 1.相同版本的MySQL数据库之间的迁移 2.不同版本的MySQL数据库之间的迁移 四.表的导出和导入 1.用select…into outfile导出文本文件 2.用mysqldump命令导出文本文件 3.用mysql命令导出文本文件 4.用load data infile方式

  • SQL Server通过重建方式还原master数据库

    SQL Server通过重建方式还原master数据库,具体内容如下 1.备份master数据库 2.停止服务,直接删除master数据文件 3.用安装程序重建master数据文件 控制台下进入安装目录就不说了 D:\SetUp\sqlserver2012>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=sqlserver2012 /SQLSYSADMINACCOUNTS=UserName /SAPWD= *** 4.单用户模式启动SQL S

  • 命令行模式下备份、还原 MySQL 数据库的语句小结

    为了安全起见,需要经常对数据库作备份,或者还原.对于 MySQL 而言,最方便的方法可能就是用 phpMyAdmin 的导出.导入功能了,但如果你的数据库体积比较大,作为 Web 应用的 phpMyAdmin 可能会遭遇"超时"而操作失败.所以,学会在命令行模式下备份.还原数据库,还是很有必要的. 1.备份数据库 在 Linux 命令行模式下备份 MySQL 数据库,用的是 mysqldump 命令: 复制代码 代码如下: mysqldump -u mysqluser -p test_

  • SQL Server 数据库备份和还原认识和总结(二)

    通过<SQL Server 数据库备份和还原认识和总结(一)>,相信您对数据备份和还原有了一个更深入的认识,在上文中我没有对事务日志做剖析,在此推荐宋沄剑的文章,对事务日志做了比较详细的讲解:http://www.jb51.net/article/31038.htm.本文将针对上文继续进行数据备份和还原讲解,主要讲解备份和还原的一些关键选项. 数据库备份选项 备份数据库时,有几个备份选项需要了解一下,覆盖介质.事务日志等.谈到覆盖介质时,必须先对这个概念有所了解,不然无从谈起. ● 介质集 (

  • MySQL使用命令备份和还原数据库

    数据库在使用当中都会有数据库备份工作,当数据库发生严重错误无法启动,或者数据丢失时可以及时有效地恢复数据.文章简单介绍如何备份和还原MySQL数据库. 备份数据库 使用mysqldump命令备份数据库 复制代码 代码如下: # 如果要将game数据库进行备份: mysqldump -u root -p game > game_backup.sql # 如果希望备份所有的数据库: mysqldump -u root -p --all-databases > all_backup.sql 还原数据

  • windows环境下mysql的解压安装及备份和还原

    系统环境为server2012 1.下载mysql解压版,解压安装包到指定目录 2.在以上目录中,复制一份my-default.ini文件,重命名为my.ini,进行如下修改(按照需要): [mysqld] # 服务端的编码方式 character_set_server=utf8 # These are commonly set, remove the # and set as required. #数据文件存放目录 basedir = C:\Program Files\mysql #数据文件存

  • MySQL笔记之数据备份与还原的使用详解

    数据很重要,这点用脚趾头想都知道,为了保证数据的安全,因此需要定期对数据备份 下面来写一点关于数据备份与还原相关的笔记 数据备份 使用mysqldump命令备份一个数据库 mysqldump -u username -p dbname table1 table2...>BackupName.sql 其中,dbname表示数据库名称,table1和table2表示表的名称,没有该参数则备份整个数据库 BackupName.sql参数表示备份文件的名称,文件名前面可以加上一个绝对路径 需要注意的是,

  • SQL Server 数据库备份和还原认识和总结 (一)

    可能许多同学对SQL Server的备份和还原有一些了解,也可能经常使用备份和还原功能,我相信除DBA之外我们大部分开发员队伍对备份和还原只使用最基础的功能,对它也只有一个大概的认识,如果对它有更深入的认识,了解它更全面的功能岂不是更好,到用时会得心应手.因为经常有中小型客户公司管理人员对数据库不了解或掌握不牢,会请我们技术人员出马找回丢失的数据或硬件损坏移动数据的现象,或其它情况的发生. 首先从数据库[恢复模式]说起,因为数据库如果恢复模式设置不正确,会导致数据无法还原. SQL Server

随机推荐