sqlserver实现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

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

@echo off


echo 开始还原数据库


net start "mssqlserver"


osql -U sa -P 123456 -i c:\backup\sqlserverrestore.sql -o c:\backup\sqlserverrestore.out


echo 还原数据库完成


pause

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