环境: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