原文:批处理(bat)实现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

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