Home Database Mysql Tutorial SQL Server数据库的备份和还原(留着慢慢看)

SQL Server数据库的备份和还原(留着慢慢看)

Jun 07, 2016 pm 03:17 PM
server sql backup database reduction

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 @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"
 

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is the difference between HQL and SQL in Hibernate framework? What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

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 does Go language implement the addition, deletion, modification and query operations of the database? Mar 27, 2024 pm 09:39 PM

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

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

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 iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

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

How does Hibernate implement polymorphic mapping? How does Hibernate implement polymorphic mapping? Apr 17, 2024 pm 12:09 PM

How does Hibernate implement polymorphic mapping?

An in-depth analysis of how HTML reads the database An in-depth analysis of how HTML reads the database Apr 09, 2024 pm 12:36 PM

An in-depth analysis of how HTML reads the database

Analysis of the basic principles of MySQL database management system Analysis of the basic principles of MySQL database management system Mar 25, 2024 pm 12:42 PM

Analysis of the basic principles of MySQL database management system

See all articles