MSSQL备份和恢复

环境:
OS:Windows 2003
DB:MSSQL 2008
 
----------------------------------备份-----------------------------------------
1.完整备份hxl数据库
BACKUP DATABASE [hxl] TO  DISK = N'D:\mssql_bak\hxl_bak20121210.bak' WITH NOFORMAT, NOINIT,  NAME = N'hxl-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
2.差异备份hxl
BACKUP DATABASE [hxl] TO  DISK = N'D:\mssql_bak\hxl_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 '备份所有数据库完成'
 
-------------------------------还原----------------------------------------
1.恢复hxl数据库
RESTORE DATABASE [hxl] FROM  DISK = N'D:\mssql_bak\hxl20121209.bak' WITH  FILE = 1,  MOVE N'hxl_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\hxl_1.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 10
GO