备份数据库.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