SQL Service Database BACKUP & RESTORE

 1 USE master;
 2 ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
 3 GO
 4 -- Back up the AdventureWorks2012 database to new media set (backup set 1).
 5 BACKUP DATABASE AdventureWorks2012
 6   TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak' 
 7   WITH FORMAT;
 8 GO
 9 --Create a routine log backup (backup set 2).
10 BACKUP LOG AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak';
11 GO

View Code

2. 完整数据库还原（完整恢复模式）

 1 USE master;
 2 --Create tail-log backup.
 3 BACKUP LOG AdventureWorks2012 
 4 TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'  
 5    WITH NORECOVERY; 
 6 GO
 7 --Restore the full database backup (from backup set 1).
 8 RESTORE DATABASE AdventureWorks2012 
 9   FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' 
10   WITH FILE=1, 
11     NORECOVERY;
12 
13 --Restore the regular log backup (from backup set 2).
14 RESTORE LOG AdventureWorks2012 
15   FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' 
16   WITH FILE=2, 
17     NORECOVERY;
18 
19 --Restore the tail-log backup (from backup set 3).
20 RESTORE LOG AdventureWorks2012 
21   FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
22   WITH FILE=3, 
23     NORECOVERY;
24 GO
25 --recover the database:
26 RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
27 GO

View Code

3. 根据数据库生成相应的SQL 脚本

SELECT Name ,

N'ALTER DATABASE '+Name+'  SET RECOVERY FULL;
GO
BACKUP DATABASE '+Name+'  TO DISK = ''C:\Databases_Backup\'+Name+'_FullRM.bak''  WITH FORMAT;
GO
BACKUP LOG '+Name+'  TO DISK = ''C:\Databases_Backup\'+Name+'_FullRM.bak'';
GO' as [BACKUP SQL] ,

N'RESTORE DATABASE '+Name+'
FROM DISK = ''C:\Databases_Backup\'+Name+'_FullRM.bak'' 
WITH FILE=1, 
NORECOVERY;

RESTORE LOG AdventureWorks2012 
FROM DISK = ''C:\Databases_Backup\'+Name+'_FullRM.bak'' 
WITH FILE=2, 
NORECOVERY;

RESTORE LOG AdventureWorks2012 
FROM DISK = ''C:\Databases_Backup\'+Name+'_FullRM.bak''
WITH FILE=3, 
NORECOVERY;
GO
RESTORE DATABASE '+Name+' WITH RECOVERY;
GO' as [RESTORE SQL] 

FROM Master..SysDatabases ORDER BY Name

View Code

posted on 2015-12-10 10:42 QZB 阅读(246) 评论(0) 编辑收藏举报