sqlserver实现bat自动备份和恢复

Myth丶恋晨 2022-01-07 07:21 450阅读 0赞

**备份数据库.bat**

@echo off
    set path=%path%;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn
    echo 数据库备份开始
    osql.exe -S 127.0.0.1 -U sa -P 123456 -i sqlserverbackup.sql -o c:\backup\sqlserverbackup.out
    echo 数据库备份完成
    pause

**sqlserverbackup.sql**

DECLARE @name varchar(50)
    DECLARE @datetime char(14)
    DECLARE @path varchar(255)
    DECLARE @bakfile varchar(255)
    set @name='DataSample'
    set @datetime=CONVERT(char(8),getdate(),112) + REPLACE(CONVERT(char(8),getdate(),108),':','')
    set @path='c:\backup\'
    set @bakfile=@path+''+@name+'_'+'bak_'+@datetime+'.BAK'
    backup database @name to disk=@bakfile with name=@name
    go

**还原数据库.bat**

@echo off
    echo 开始还原数据库
    net start "mssqlserver"
    osql -U sa -P 123456 -i c:\backup\sqlserverrestore.sql -o c:\backup\sqlserverrestore.out
    echo 还原数据库完成
    pause

**sqlserverrestore.sql**

declare @dumpfile varchar(50)
    declare @msg varchar(70)
       select @dumpfile = 'c:\backup\DataSample_bak_20170718145556.BAK'
       select @msg=convert(char(26),getdate(),9)
       print @msg
     
    restore DATABASE DataSample from disk=@dumpfile
    if (@@ERROR <> 0 )
    begin
       select @msg=convert(char(26),getdate(),9)+'-----还原数据失败或出现异常'
       print @msg
    end
    else
    begin
       select @msg=convert(char(26),getdate(),9)+'-----数据库还原完毕'
       print @msg
    end

declare @dumpfile varchar(50)
    declare @msg varchar(70)
       select @dumpfile = 'c:\backup\DataSample_bak_20170718161443.BAK'
       select @msg=convert(char(26),getdate(),9)
       print @msg
    
    ----同一个备份文件还原成不同名称数据库  
    RESTORE DATABASE DataSample1
       FROM disk=@dumpfile
       WITH RECOVERY,
       MOVE 'DataSample' TO 'D:\MyData\DataSample1.mdf', 
       MOVE 'DataSample_Log' TO 'D:\MyData\DataSample1_Log.ldf'
    
    if (@@ERROR <> 0 )
    begin
       select @msg=convert(char(26),getdate(),9)+'-----还原数据失败或出现异常'
       print @msg
    end
    else
    begin
       select @msg=convert(char(26),getdate(),9)+'-----数据库还原完毕'
       print @msg
    end