数据库的备份和恢复
来源:互联网 发布:部落冲突数据 编辑:程序博客网 时间:2024/04/30 07:24
备份与恢复
1. 备份策略
2. 操作系统级备份:
让数据库脱机
USE master
GO
ALTER DATABASE [MySQLServer]
SET OFFLINE
GO
让数据库联机
USE master
GO
ALTER DATABASE [MySQLServer]
SET ONLINE
GO
3. 数据库全备份
BACKUP DATABASE [MySQLServer]
TO DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak'
WITH DESCRIPTION = N'2008-03-11',
NOFORMAT, INIT,
NAME = N'MySQLServer-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'MySQLServer'
and backup_set_id=
(select max(backup_set_id) from msdb..backupset where database_name=N'MySQLServer' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''MySQLServer'' not found.', 16, 1)
end
RESTORE VERIFYONLY
FROM DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak'
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
4. 数据库增量备份
BACKUP DATABASE [MySQLServer]
TO DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'MySQLServer-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MySQLServer' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MySQLServer' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MySQLServer'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
5. 事务日志备份
BACKUP LOG [MySQLServer]
TO DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak'
WITH NOFORMAT, NOINIT, NAME = N'MySQLServer-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
6. 数据库恢复:
RESTORE DATABASE [MySQLServer]
FROM DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak'
WITH FILE = 6, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [MySQLServer]
FROM DISK = N'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMySQLServer.bak'
WITH RESTRICTED_USER, FILE = 6,
MOVE N'MySQLServer_PRI01' TO N'E:MySQL_PRI01.mdf',
MOVE N'MySQLServer_log' TO N'E:MySQL2_Log.ldf',
MOVE N'MySQLServer_SEC01' TO N'E:MySQL2_SEC01.ndf',
MOVE N'MySQLServer_SEC02' TO N'E:MySQL2_SEC02.ndf',
KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 10
GO
7. 分离与附加
例:数据库分离:
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MySQLServer', @keepfulltextindexfile=N'true'
GO
例:数据库附加:
CREATE DATABASE MySQLServer2
ON ( FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataMySQLServer2.MDF' )
FOR ATTACH
GO
- 数据库的备份和恢复
- 数据库的备份和恢复
- 数据库的备份和恢复
- 数据库的备份和恢复
- 数据库的备份和恢复
- 数据库的备份和恢复
- 数据库的备份和恢复
- 数据库备份和恢复
- 数据库备份和恢复
- 数据库备份和恢复
- SqlServer数据库的备份和恢复措施
- MySQL 数据库的备份和恢复
- SqlServer数据库的备份和恢复措施
- c#中数据库的备份和恢复
- MySQL 数据库的备份和恢复(1)
- MySQL 数据库的备份和恢复(2)
- c# 实现数据库的备份和恢复
- MySQL 数据库的备份和恢复
- ios 开发,常见报错及修复
- 漫谈iOS Crash收集框架
- Android官方开发文档Training系列课程中文版:分享简单数据之从其它APP接收简单数据
- 22.4-2简单路径条数
- 在SourceInsight中使用SourceMonitor对C语言程序进行度量,以及Kiviat图说明
- 数据库的备份和恢复
- 反调试与反反调试
- Using Multiple Android Studio Versions
- Android 自定义View高级特效,神奇的贝塞尔曲线
- 融银p2p系统时间,时区说明
- 用事件适配器来实现窗口的关闭
- php mvc 框架演示
- 引用和指针
- 多线程:使用ImageView下载图片(模仿 SDWebImage)