沈阳凯文数据恢复中心 服务器数据恢复 各类数据库修复 小型机数据恢复 13386848847 024-31065488 地址:沈阳市和平区三好街同方广场A座10楼1012写字间

MSSQL备份和恢复

 环境:OS:Windows 2003DB: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 = 10GO 2.差异备份hxlBACKUP DATABASE [hxl] TO  DISK = N'D:\mssql_bak\hxl_bak20121210.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'hxl-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO 3.脚本备份所有的数据库--SQL备份所有数据库脚本declare @CurrentDataBaseName nvarchar(100)declare @CurrentBackFolder nvarchar(200)declare @CurrentBackString nvarchar(2000)set @CurrentBackFolder='D:\mssql_bak'--这里是备份的目录,所有数据库都备份到这个目录--查询所有数据库名称--select * from   master..sysdatabasesdeclare tb cursor local for select name from master..sysdatabases where name <>'tempdb';open tbfetch next from tb into @CurrentDataBaseNamewhile @@fetch_status=0begin --备份当前查询到的数据库到指定目录 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 @CurrentDataBaseNameendclose tbdeallocate tbprint '备份所有数据库完成' -------------------------------还原----------------------------------------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 = 10GO