SQL Server数据库的备份和还原(留着慢慢看)
Jun 07, 2016 pm 03:17 PMSQL语句里有. 备份 backup database [数据库名] to disk=[磁盘路径] 例如 backup database data to disk='D:/1.bak' 恢复 restore database [数据库名] from disk=[磁盘路径] 例如 restore database data from disk='D:/1.bak' create PROCEDURE GY_DBBak @b
SQL语句里有.
备份
backup database [数据库名] to disk=[磁盘路径]
例如
backup database data to disk='D:/1.bak'
恢复
restore database [数据库名] from disk=[磁盘路径]
例如
restore database data from disk='D:/1.bak'
create PROCEDURE GY_DBBak
@bakequip int, -- 备份设备:磁盘&磁带
@bakpath varchar(50), -- 带全路径的备份文件名
@baktype int, -- 完全备份&增量备份
@baklog int, -- ‘0’备份日志
@bakdb int, -- ‘0’备份数据库
@kind varchar(7), --备份还是恢复
@retmsg varchar(20) output --返回信息
AS
DECLARE @DevName_data varchar(50)
DECLARE @DevName_log varchar(50)
declare @db_path varchar(100)
declare @log_path varchar(100)
DECLARE @RC INT
SELECT @db_path = @bakpath + '.dat'
SELECT @log_path = @bakpath + 'log.dat'
SELECT @RC=0
DBCC CHECKDB(Northwind)
/***********************************************************
** CREATE BACKUP AND RESTORE DEVICES
************************************************************/
IF @RC=0
BEGIN
EXEC sp_addumpdevice 'disk', @DevName_data,@db_path
exec sp_addumpdevice 'disk', @DevName_log,@log_path
select @rc=@@error
IF @RC0
begin
EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
SELECT @RC=-1000
return @rc
end
END
IF @kind='backup'
BEGIN
IF @bakequip=0
BEGIN
IF @baktype=0
BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@Devname_data
WITH INIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH INIT,NO_TRUNCATE
END
END
ELSE BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@DevName_data
WITH NOINIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH NOINIT,NO_TRUNCATE
END
END
END
SELECT @retmsg='数据库备份成功!'
END
IF @kind='restore'
BEGIN
RESTORE DATABASE Northwind FROM DISK= @DevName_data WITH REPLACE
SELECT @retmsg='恢复数据库成功!'
END
RETURN 0
增量备份的操作步骤大概是
1、截断当前日志dump tran db_name with truncate_only
2、做数据库全备份dump database db_name to "/data/db.dmp"
3、做增量备份dump tran db_name to "/data/db_tran1.dmp"

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

What is the difference between HQL and SQL in Hibernate framework?

How does Go language implement the addition, deletion, modification and query operations of the database?

How to use MySQL backup and restore in PHP?

Detailed tutorial on establishing a database connection using MySQLi in PHP

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos

How does Hibernate implement polymorphic mapping?

An in-depth analysis of how HTML reads the database

Analysis of the basic principles of MySQL database management system
