sqlserver 一键备份，异机还原脚本

REM +---------------------------------------------------------------------------------+
REM |desc AUTO backup sqlserver database and recovery to focdev env ervery week       |
REM |auther: monkeybron 38339332@qq.com                                            |
REM |NOTE :  xp_cmdshell must ON   V2.0                                               |
REM +---------------------------------------------------------------------------------+
REM +--------------------------------------------------------------------------+
REM | VALIDATE ENVIRONMENT VARIABLES                                           |
REM +--------------------------------------------------------------------------+

set YYYYmmdd=%date:~0,4%%date:~5,2%%date:~8,2%
set hhmiss=%time:~0,2%%time:~3,2%%time:~6,2%
set BACKUPTEMP=D:\dbbackup
set BACKUPLOG=%BACKUPTEMP%\backup.log
SET RECOVERYLOG=%BACKUPTEMP%\recovery.log
set GRANTLOG=%BACKUPTEMP%\grant.log
set LOGFILE=D:\dba_scripts\autobackup_%YYYYmmdd%_%hhmiss%.log
set BAKCMDFILE=%BACKUPTEMP%\backup.rcv

SET REMOTEUSER=sa
set REMOTEPASSWD=xxxxSET RECOVERYFILE=%BACKUPTEMP%\recovery.rcv
set GRANTFILE=%BACKUPTEMP%\GRANTFILE.rcv



REM +--------------------------------------------------------------------------+
REM | REMOVE OLD LOG AND COMMAND FILES AND FILES                               |
REM +--------------------------------------------------------------------------+

del /q %BACKUPTEMP%
md %BACKUPTEMP%


REM +--------------------------------------------------------------------------+
REM | WRITE BACKUP COMMAND SCRIPT.                                             |
REM +--------------------------------------------------------------------------+
 
> %BAKCMDFILE% echo exec master.sys.xp_cmdshell ‘net use g: \\172.31.40.41\dbbackup "xxxxx" /user:172.31.40.41\administrator‘,NO_OUTPUT
>> %BAKCMDFILE% echo go
>> %BAKCMDFILE% echo declare @s nvarchar(200),@del nvarchar(200)
>> %BAKCMDFILE% echo select @s=‘‘,@del=‘‘
>> %BAKCMDFILE% echo declare datebak cursor for
>> %BAKCMDFILE% echo select
>> %BAKCMDFILE% echo [bak]=‘backup database  ‘+quotename(Name)+‘  to disk =‘‘g:\‘+Name+‘_‘+convert(varchar(8),getdate(),112)+‘.bak‘‘ with init,format,compression,buffercount=50,maxtransfersize=4194304;‘,
>> %BAKCMDFILE% echo [del]=‘exec master..xp_cmdshell ‘‘ del g:‘+Name+‘_‘+convert(varchar(8),getdate()-7,112)+‘.bak‘‘, no_output‘
>> %BAKCMDFILE% echo from master..sysdatabases where dbid=db_id(‘isa‘)
>> %BAKCMDFILE% echo open datebak
>> %BAKCMDFILE% echo fetch next from datebak into @s,@del
>> %BAKCMDFILE% echo while @@fetch_status=0
>> %BAKCMDFILE% echo begin
>> %BAKCMDFILE% echo  exec(@del)
>> %BAKCMDFILE% echo  exec(@s)
>> %BAKCMDFILE% echo  fetch next from datebak into @s,@del
>> %BAKCMDFILE% echo  end
>> %BAKCMDFILE% echo close datebak
>> %BAKCMDFILE% echo deallocate datebak
>> %BAKCMDFILE% echo go
>> %BAKCMDFILE% echo exec master..xp_cmdshell ‘net use g: /delete‘
>> %BAKCMDFILE% echo go


REM +--------------------------------------------------------------------------+
REM | PERFORM remote SQL BACKUP.                                                      |
REM +--------------------------------------------------------------------------+

sqlcmd -S 172.31.86.56 -U%REMOTEUSER% -P%REMOTEPASSWD% -i %BAKCMDFILE% -o %BACKUPLOG%

REM +--------------------------------------------------------------------------+
REM | WRITE RECOVERY COMMAND SCRIPT.                                           |
REM +--------------------------------------------------------------------------+



>%RECOVERYFILE%  echo declare @i int declare cur cursor for select spid from sysprocesses where db_name(dbid)= ‘isa_test‘ open cur fetch next from cur into @i while @@fetch_status=0 begin exec(‘kill ‘+@i) fetch next from cur into @i end close cur deallocate cur;
>>%RECOVERYFILE% echo drop database isa_test;
>>%RECOVERYFILE% echo declare @res nvarchar(200)
>>%RECOVERYFILE% echo select @res=‘‘
>>%RECOVERYFILE% echo declare datebak cursor for
>>%RECOVERYFILE% echo select
>>%RECOVERYFILE% echo [res]=‘restore database isa_test from disk =‘‘d:\dbbackup\‘+‘isa‘+‘_‘+convert(varchar(8),getdate(),112)+‘.bak‘‘‘
>>%RECOVERYFILE% echo + ‘ with move ‘‘ISA_Data‘‘ to ‘‘D:\DATA\isa_test.mdf‘‘,move‘‘ISA_Log‘‘ to ‘‘D:\DATA\isa_test.ldf‘‘;‘
>>%RECOVERYFILE% echo from master..sysdatabases where dbid=db_id(‘master‘)
>>%RECOVERYFILE% echo open datebak
>>%RECOVERYFILE% echo fetch next from datebak into @res
>>%RECOVERYFILE% echo while @@fetch_status=0
>>%RECOVERYFILE% echo     begin
>>%RECOVERYFILE% echo         exec(@res)
>>%RECOVERYFILE% echo         fetch next from datebak into @res
>>%RECOVERYFILE% echo     end
>>%RECOVERYFILE% echo close datebak
>>%RECOVERYFILE% echo deallocate datebak
>>%RECOVERYFILE% echo go


REM +--------------------------------------------------------------------------+
REM |  RECOVERY to dev hosts.                                                  |
REM +--------------------------------------------------------------------------+

sqlcmd  -i %RECOVERYFILE% -o %RECOVERYLOG%



REM +--------------------------------------------------------------------------+
REM | WRITE GRANT PRIVILEGES COMMAND SCRIPT.                                   |
REM +--------------------------------------------------------------------------+

>%GRANTFILE%  echo USE [master]
>>%GRANTFILE% echo GO
>>%GRANTFILE% echo ALTER DATABASE [isa_test] SET RECOVERY SIMPLE WITH NO_WAIT
>>%GRANTFILE% echo go
>>%GRANTFILE% echo use isa_test
>>%GRANTFILE% echo go
>>%GRANTFILE% echo use isa_test
>>%GRANTFILE% echo go
>>%GRANTFILE% echo create user isa_test for login isa_test with default_schema=dbo
>>%GRANTFILE% echo exec sp_addrolemember ‘db_owner‘, ‘isa_test‘
>>%GRANTFILE% echo go
>>%GRANTFILE% echo create user isa_r for login isa_r with default_schema=dbo
>>%GRANTFILE% echo ALTER ROLE [db_datareader] ADD MEMBER [liujing]
>>%GRANTFILE% echo go

>>%GRANTFILE% echo grant select on OCR_factorS to isa_r;
>>%GRANTFILE% echo grant select on OCR_factor_Rick to isa_r;
>>%GRANTFILE% echo grant select on AIRLINES_ROUTES  to isa_r;
>>%GRANTFILE% echo grant select on AIRLINES_ROUTES_PTP  to isa_r;
>>%GRANTFILE% echo go

REM +--------------------------------------------------------------------------+
REM |  grant privileges to dev hosts.                                          |
REM +--------------------------------------------------------------------------+

sqlcmd  -i %GRANTFILE% -o %GRANTLOG%

REM +--------------------------------------------------------------------------+
REM |  merge log operation                                                     |
REM +--------------------------------------------------------------------------+
echo  -------------  > %LOGFILE%
type  %BACKUPLOG% >> %LOGFILE%
echo  -------------  >> %LOGFILE%
type  %RECOVERYLOG% >> %LOGFILE%
echo  -------------  >> %LOGFILE%
type  %GRANTLOG% >> %LOGFILE%
echo  -------------  >> %LOGFILE%
echo  complete!  >> %LOGFILE%