sql备份还原代码 

 1 use master   
 2  go   
 3 --在硬盘上创建备份设备   
 4 exec sp_addumpdevice 'disk',mybackupfile,   
 5 'c:\Mybackupfile.bak'  
 6 --删除备份设备   
 7 exec sp_dropdevice mybackupfile   
 8   
 9 --备份数据库数据到临时设备   
10 backup database northwind   
11 to disk = 'c:\northwind.bak'  
12   
13 --完全备份数据库到备份设备   
14 backup database northwind   
15 to mybackupfile   
16   
17 --执行差异备份   
18 backup database northwind   
19 to mybackupfile   
20 with differential   
21   
22 -- 备份日志文件到备份设备   
23 exec sp_addumpdevice 'disk',mybackupLog,   
24 'c:\MybackupLog.bak'  
25 backup log northwind   
26 to mybackupLog   
27 -- 备份日志文件到临时备份设备   
28 backup log northwind   
29 to disk = 'c:\mybackupLog.bak'  
30 --从备份设备还原数据库   
31 restore database northwind   
32 from mybackupfile   
33   
34 --从备份文件还原数据库   
35 backup database northwind   
36 to disk = 'c:\northwindback.bak'  
37 backup database northwind   
38 to disk = 'c:\northwindback.bak'  
39 with differential   
40 restore database northwind   
41 from disk = 'c:\northwindback.bak'  
42 with file = 1,recovery   
43 restore database northwind   
44 from disk = 'c:\northwindback.bak'  
45 with file = 2,recovery   
46 --事务日志还原   
47 restore database northwind   
48 from disk = 'c:\northwindback.bak'  
49 with file = 1,norecovery   
50 restore log northwind   
51 from disk = 'c:\northwindLog.bak'  
52 with file = 1,recovery   
53 --时点还原   
54 backup database northwind   
55 to disk = 'c:\northwind.bak'  
56 backup log northwind   
57 to disk = 'c:\mybackupLog.bak'  
58 restore database northwind   
59 from disk = 'c:\northwind.bak'  
60 with file = 1,norecovery   
61 restore log northwind   
62 from disk = 'c:\mybackupLog.bak'  
63 with file = 1 ,stopat = '2006-4-7 9:54:00' ,recovery

 

posted @ 2010-04-26 11:17  hzucmj  阅读(410)  评论(0)    收藏  举报