环境:
os:windows 2003
db:mssql 2008
--备份sql数据库--------
1，完整备份hxl数据库
复制代码 代码示例:
backup database [hxl] to disk = n'd:mssql_bakhxl_bak20121210.bak' with noformat, noinit, name = n'hxl-完整 数据库 备份', skip, norewind, nounload, stats = 10
go
2，差异备份hxl
复制代码 代码示例:
backup database [hxl] to disk = n'd:mssql_bakhxl_bak20121210.bak' with differential , noformat, noinit, name = n'hxl-差异 数据库 备份', skip, norewind, nounload, stats = 10
go
3，脚本备份所有的数据库
复制代码 代码示例:
--sql备份所有数据库脚本
declare @currentdatabasename nvarchar(100)
declare @currentbackfolder nvarchar(200)
declare @currentbackstring nvarchar(2000)
set @currentbackfolder='d:mssql_bak'--这里是备份的目录,所有数据库都备份到这个目录
--查询所有数据库名称
--select * from master..sysdatabases
declare tb cursor local for select name from master..sysdatabases where name <>'tempdb';
open tb
fetch next from tb into @currentdatabasename
while @@fetch_status=0
begin
--备份当前查询到的数据库到指定目录
set @currentbackstring='
use [master]
backup database ['+@currentdatabasename+'] to disk = '''+ @currentbackfolder+''+@currentdatabasename+convert(varchar(50),getdate(),112)+'.bak'' with noformat, noinit,name='''+@currentdatabasename+'-完整 数据库 备份'',skip, norewind, nounload;';
print @currentbackstring;
exec sp_executesql @currentbackstring;
print '备份数据库'+@currentdatabasename +'完成';
fetch next from tb into @currentdatabasename
end
close tb
deallocate tb
print '备份所有数据库完成'
--还原sql数据库------
1，恢复hxl数据库
复制代码 代码示例:
use master;
drop database hxl;
restore database [hxl] from disk = n'd:mssql_bakhxl20121209.bak' with file = 1, move n'hxl_log' to n'd:program filesmicrosoft sql servermssql10_50.mssqlservermssqldatahxl_1.ldf', norecovery, nounload, stats = 10
go
