ÈçºÎ²éѯSQL Server±¸·Ý»¹ÔÀúÊ·¼Ç¼
SQL ServerÔÚmsdbÊý¾ÝÖÐά»¤ÁËһϵÁÐ±í£¬ÓÃÀ´´æ´¢Ö´ÐÐËùÓб¸·ÝºÍ»¹ÔµÄϸ½ÚÐÅÏ¢¡£¼´Ê¹ÄãÕýÔÚʹÓõÚÈý·½µÄ±¸·ÝÓ¦ÓóÌÐò£¬Ö»ÒªÕâ¸öÓ¦ÓóÌÐòʹÓÃSQL ServerµÄÐéÄâÉ豸½Ó¿Ú(Virtual Device Interface---VDI)À´Ö´Ðб¸·ÝºÍ»¹ÔÖ´ÐУ¬ÄÇôִÐÐϸ½ÚÒÀÈ»±»´æ´¢ÔÚÕâһϵÁбíÖС£
´æ´¢Ï¸½ÚµÄ±í°üÀ¨£º
backupset
backupfile
backupfilegroup (SQL Server 2005 upwards)
backupmediaset
backupmediafamily
restorehistory
restorefile
restorefilegroup
logmarkhistory
suspect_pages (SQL Server 2005 upwards)
Äã¿ÉÒÔÔÚBooks OnlineÀïÃæÕÒµ½ÉÏÃæÕâЩ±íµÄ¾ßÌå˵Ã÷¡£
ÏÂÃæÕâ¸ö½Å±¾¿ÉÒÔ°ïÄãÕÒ³öÿ¸öÊý¾Ý¿â½üÆÚµÄ±¸·ÝÐÅÏ¢£º
SELECT b.name, a.type, MAX(a.backup_finish_date) lastbackup
from msdb..backupset a
INNER JOIN master..sysdatabases b ON a.database_name COLLATE DATABASE_DEFAULT = b.name COLLATE DATABASE_DEFAULT
GROUP BY b.name, a.type
ORDER BY b.name, a.type
Ö¸¶¨Êý¾Ý¿â×îºó20ÌõÊÂÎñÈÕÖ¾±¸·ÝÐÅÏ¢£º
SELECT TOP 20 b.physical_device_name, a.backup_start_date, a.first_lsn, a.user_name from msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.type = 'L'
ORDER BY a.backup_finish_date DESC
Ö¸¶¨Ê±¼ä¶ÎµÄÊÂÎñÈÕÖ¾±¸·ÝÐÅÏ¢£º
SELECT b.physical_device_name, a.backup_set_id, b.family_sequence_number, a.position, a.backup_start_date, a.backup_finish_date
from msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = 'AdventureWorks'
AND a.type = 'L'
AND a.backup_start_date > '10-Jan-2007'
AND a.backup_finish_date < '16-Jan-2009 3:30'
ORDER BY a.backup_start_date, b.family_sequence_number
ɾ³ý±¸·ÝÈÕÖ¾µÄÁ½¸ö´æ´¢¹ý³Ì£º
EXEC msdb..sp_delete_backuphistory '1-Jan-2005'
EXEC msdb..sp_delete_database_backuphistory 'AdventureWorks'
±¾ÎÄ·Òë×Ôsqlbackuprestore£¬¸ü¶à¾«²ÊÄÚÈÝÇëä¯ÀÀhttp://www.sqlbackuprestore.com
Ïà¹ØÎĵµ£º
SQL Server´´½¨ÁÙʱ±í£º
´´½¨ÁÙʱ±í
·½·¨Ò»£º
create table #ÁÙʱ±íÃû(×Ö¶Î1 Ô¼ÊøÌõ¼þ,
×Ö¶Î2 Ô¼ÊøÌõ¼þ,
.....)
create table ##ÁÙʱ±íÃû(×Ö¶Î1 Ô¼ÊøÌõ¼þ,
×Ö¶Î2 Ô¼ÊøÌõ¼þ,
.....)
·½·¨¶þ£º
select * into #ÁÙʱ±íÃû from ÄãµÄ±í;
select * into ##ÁÙʱ±íÃû from ÄãµÄ±í;
×¢£ºÒÔÉϵÄ#´ú±í¾Ö²¿ÁÙʱ±í£¬##´ú±íÈ«¾ ......
Çë½Ì¸ßÊÖÒ»¸öÎÊÌ⣬ÎÊÌâÃèÊöÈçÏ£º
A±íÊǸ÷¸öµ¥Î»µÄÃû³Æ±í ×Ö¶ÎΪ org_id ºÍ org_name
B±íÊÇÕâЩµ¥Î»µÄµç»°ºÅÂë±í ×Ö¶ÎΪ org_idºÍ tel
A±í B±í¹ØÁª·½Ê½ÎªA.ORG_ID=B.ORG_ID
org_idÊǸ÷¸öµ¥Î»µÄ´úÂë
org_nameÊǸ÷¸öµ¥Î»µÄÃû³Æ
telÊǸ÷¸öµ¥Î»µÄµç»°ºÅÂë
±¾ÈËÏÖÔÚÏëÕë¶Ôÿ¸öµ¥Î»£¨Ã¿Ìõorg_id£©È¡Æä10¸öºÅÂë
Ç ......
ÎÊÌâÃèÊö£º
Express°æÓÃsaÕÊ»§µÇ¼ʱʧ°Ü
½â¾ö·½°¸£º
µÚÒ»²½£ºÓÃWindowsÉí·ÝµÇ¼Management Studio£¬ÓÒ»÷·þÎñÆ÷Ñ¡ÔñÊôÐÔ£¬½«°²È«ÐÔÑ¡Ïî ¸ü¸ÄΪ:SQL Server ºÍ Windows
Éí·ÝÑéÖ¤
& ......
oracleÖÐsqlÓï¾äÖÐselect * from t_Test t where t.Id in(1,2,3......)/*ÊýÁ¿²»ÄÜ´óÓÚ1000¸ö*/
½â¾ö·½·¨ ·Ö¸î³É¶à´Îin È»ºóÔÙ»òÉÏ Èç select * from t_Test t where t.Id in(1,2,3......800) or t.Id in(801,802,803......1300)
ÔÚʹÓÃÖÐ×îºÃÄܲ»Ê¹ÓÃÆäËûÌõ¼þÀ´´úÌæin
......
Ò»¡¢sqlÓï¾äµÄÖ´Ðв½Ö裺
1£©Óï·¨·ÖÎö£¬·ÖÎöÓï¾äµÄÓï·¨ÊÇ·ñ·ûºÏ¹æ·¶£¬ºâÁ¿Óï¾äÖи÷±í´ïʽµÄÒâÒå¡£
2£© ÓïÒå·ÖÎö£¬¼ì²éÓï¾äÖÐÉæ¼°µÄËùÓÐÊý¾Ý¿â¶ÔÏóÊÇ·ñ´æÔÚ£¬ÇÒÓû§ÓÐÏàÓ¦µÄȨÏÞ¡£
3£©ÊÓͼת»»£¬½«Éæ¼°ÊÓͼµÄ²éѯÓï¾äת»»ÎªÏàÓ¦µÄ¶Ô»ù±í²éѯÓï¾ä¡£
4£©±í´ïʽת»»£¬ ½«¸´Ô SQL ±í´ïʽת»»Îª½Ï¼òµ¥µÄµÈЧÁ¬½Ó±í´ïʽ ......