sqlserver实现bat自动备份和恢复

备份数据库.bat

@echo off
set path=%path%;C:Program Files (x86)Microsoft SQL Server80ToolsBinn
echo 数据库备份开始
osql.exe -S 127.0.0.1 -U sa -P 123456 -i sqlserverbackup.sql -o c:ackupsqlserverbackup.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:ackup
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:ackupsqlserverrestore.sql -o c:ackupsqlserverrestore.out
echo 还原数据库完成
pause

sqlserverrestore.sql

declare @dumpfile varchar(50)
declare @msg varchar(70)
   select @dumpfile = c:ackupDataSample_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:ackupDataSample_bak_20170718161443.BAK
   select @msg=convert(char(26),getdate(),9)
   print @msg

----同一个备份文件还原成不同名称数据库  
RESTORE DATABASE DataSample1
   FROM disk=@dumpfile
   WITH RECOVERY,
   MOVE DataSample TO D:MyDataDataSample1.mdf, 
   MOVE DataSample_Log TO D:MyDataDataSample1_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