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数据库
use master;
drop database 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