alter database zygl3_data set emergency
Ò»¡¢¸½¼ÓSQL2005Êý¾Ý¿âʧ°Ü½â¾ö·½·¨
²Î¿¼»Ö¸´·½·¨£º
1¡¢Í£Ö¹Êý¾Ý¿â·þÎñ¡£
2¡¢½«ÐèÒª»Ö¸´µÄÊý¾Ý¿âÎļþ¸´ÖƵ½ÁíÍâµÄλÖá£
3¡¢Æô¶¯Êý¾Ý¿â·þÎñ¡£
4¡¢È·ÈÏÒª»Ö¸´µÄÊý¾Ý¿âÎļþÒѾ³É¹¦¸´ÖƵ½ÁíÍâµÄλÖã¬È»ºóÔÚSQL Server Management StudioÖÐɾ³ýÒª»Ö¸´µÄÊý¾Ý¿â¡£
5¡¢Ð½¨Í¬ÃûµÄÊý¾Ý¿â(Êý¾Ý¿âÎļþÃûÒ²ÒªÏàͬ)¡£
6¡¢Í£Ö¹Êý¾Ý¿â·þÎñ¡£
7¡¢ÓõÚ2²½Öб¸·ÝµÄ.mdfÎļþ¸²¸ÇÐÂÊý¾Ý¿âµÄͬÃûÎļþ¡£
8¡¢Æô¶¯Êý¾Ý¿â·þÎñ¡£
9¡¢ÔËÐÐalter database dbname set emergency£¬½«Êý¾Ý¿âÉèÖÃΪemergency mode
10¡¢ÔËÐÐÏÂÃæµÄÃüÁî¾Í¿ÉÒÔ»Ö¸´Êý¾Ý¿â£º
use master
declare @databasename varchar(255)
set @databasename=''''ÄãµÄÊý¾Ý¿âÃû''''
exec sp_dboption @databasename, N''''single'''', N''''true''''½«Ä¿±êÊý¾Ý¿âÖÃΪµ¥Óû§×´Ì¬
dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)
dbcc checkdb(@databasename,REPAIR_REBUILD)
exec sp_dboption @databasename, N''''single'''', N''''false''''½«Ä¿±êÊý¾Ý¿âÖÃΪ¶àÓû§×´Ì¬
Èç¹ûÄãÒ»Çж¼Ë³µÃµÄ»°¾ÍÈçÉϵIJ½Öè¾Í¿ÉÒԳɹ¦ÁË£¬ÏÂÃæÎÒÃǽ«¶ÔÌØÊâµÄ״̬½øÐзÖÎö
1¡¢Ò»¸ö»ò¶à¸öÎļþÓëÊý¾Ý¿âµÄÖ÷Îļþ²»Æ¥Åä¡£Èç¹ûÊdz¢ÊÔ¸½¼ÓÊý¾Ý¿â£¬ÇëʹÓÃÕýÈ·µÄÎļþÖØÊԸòÙ×÷¡£Èç¹ûÕâÊÇÏÖÓÐÊý¾Ý¿â£¬ÔòÎļþ¿ÉÄÜÒÑË𻵣¬Ó¦¸Ã´Ó±¸·Ý½øÐл¹Ô¡£
ÈÕÖ¾Îļþ ''''E:/Program Files/Microsoft SQL Server 2005/MSSQL.1/MSSQL/DATA/dnt2_db_log.ldf'''' ÓëÖ÷Îļþ²»Æ¥Åä¡£¸ÃÎļþ¿ÉÄÜÀ´×ÔÁíÒ»Êý¾Ý¿â£¬»òÕß¿ÉÄÜÒÔÇ°ÖØÐÂÉú³ÉÁËÈÕÖ¾¡£
ÏûÏ¢ 5123£¬¼¶±ð 16£¬×´Ì¬ 1£¬µÚ 5 ÐÐ
³¢ÊÔ´ò¿ª»ò´´½¨ÎïÀíÎļþ ''''F:/ÏîÄ¿/°®ÉçÇø/Êý¾Ý¿â/dnt2_db_log.ldf'''' ʱ£¬CREATE FILE Óöµ½²Ù×÷ϵͳ´íÎó 3(ϵͳÕÒ²»µ½Ö¸¶¨µÄ·¾¶¡£)¡£
ÏûÏ¢ 5024£¬¼¶±ð 16£¬×´Ì¬ 2£¬µÚ 5 ÐÐ
ÔÚ sysfiles1 ÖÐÕÒ²»µ½Ö÷ÈÕÖ¾ÎļþËù¶ÔÓ¦µÄÌõÄ¿¡£ÎÞ·¨Öؽ¨ÈÕÖ¾¡£
ÏûÏ¢ 5028£¬¼¶±ð 16£¬×´Ì¬ 2£¬µÚ 5 ÐÐ
ϵͳÎÞ·¨¼¤»î×ã¹»µÄÊý¾Ý¿âÀ´Öؽ¨ÈÕÖ¾¡£
½â¾ö·½°¸:ÊÖ¶¯´´½¨E:/Program Files/Microsoft SQL Server 2005/MSSQL.1/MSSQL/DATAĿ¼¾Í¿ÉÒÔÁË,È»ºóÔÙÖ´ÐÐ
ÉÏÊöµÚ10²½²½Öè,¾Í»áÔÚE:/Program Files/Microsoft SQL Server 2005/MSSQL.1/MSSQL/DATA²úÉúÒ»¸öÈÕÖ¾Îļþ¡£Êý¾Ý¿â¾Í¿ÉÒÔÕý³£Ê¹ÓÃÁË
2¡¢ÁíÍâÖµµÃ˵˵µÄ¾ÍÊÇ£¬Èç¹û¸ÃÊý¾ÝÔÚ±¸·Ýʱ·¢Éú
System.Data.SqlClient.SqlError: ÔÚ´¦Àí ''''BackupMetadata'''' ÔªÊý¾Ý(Êý¾Ý¿â ID 6£¬Îļþ ID 65537)ʱ³ö´í¡£ (Microsoft.SqlServer.Smo)£¬ËµÃ÷ÔÚ¸½¼Ó¹ý³ÌÖÐûÓвúÉúÈ«ÎÄË÷ÒýÎļþ
½â¾ö·½·¨:
ÓÒ¼üÊý¾Ý¿â£>ÊôÐÔ£>Îļþ£>Ñ¡ÔñʹÓÃÈ«ÎÄË÷Òý
Èç¹ûÖ´ÐÐÕý³£ÄÇô¾Í¿ÉÒÔÖ±½Ó½øÐб¸·Ý²Ù×÷ÁË¡£
1¡¢ËµÃ÷£º¸´ÖƱí(Ö»¸´Öƽṹ,Ô´±íÃû£ºa бíÃû£ºb) (Access¿ÉÓÃ)
·¨Ò»£ºselect  into b from a where 11
·¨¶þ£ºselect top 0  into b from a
Ò²¿ÉÒÔSELECT * INTO  ÐÂ±ê  FROM Ôʼ±í  where 1=2
2¡¢ËµÃ÷£º¿½±´±í(¿½±´Êý¾Ý,Ô´±íÃû£ºa Ä¿±ê±íÃû£ºb) (Access¿ÉÓÃ)
insert into b(a, b, c) select d,e,f from b
3¡¢ËµÃ÷£º¿çÊý¾Ý¿âÖ®¼ä±íµÄ¿½±´(¾ßÌåÊý¾ÝʹÓþø¶Ô·¾¶) (Access¿ÉÓÃ)
insert into b(a, b, c) select d,e,f from b in ¡®¾ßÌåÊý¾Ý¿â¡¯    where Ìõ¼þ
Àý×Ó£º..from b in ''''&Server.MapPath(.)&data.mdb &'''' where..
4¡¢ËµÃ÷£º×Ó²éѯ(±íÃû1£ºa ±íÃû2£ºb)
select a,b,c from a where a IN (select d from b )    »òÕß    select a,b,c from a where a IN (1,2,3)
5¡¢ËµÃ÷£ºÏÔʾÎÄÕ¡¢Ìá½»È˺Í×îºó»Ø¸´Ê±¼ä
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where
table.title=a.title) b
6¡¢ËµÃ÷£ºÍâÁ¬½Ó²éѯ(±íÃû1£ºa ±íÃû2£ºb)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7¡¢ËµÃ÷£ºÔÚÏßÊÓͼ²éѯ(±íÃû1£ºa )
select  from (SELECT a,b,c FROM a) T where t.a  1
8¡¢ËµÃ÷£ºbetweenµÄÓ÷¨,betweenÏÞÖƲéѯÊý¾Ý·¶Î§Ê±°üÀ¨Á˱߽çÖµ,not between²»°üÀ¨
select  from table1 where time between time1 and time2
select a,b,c, from table1 where a not between ÊýÖµ1 and ÊýÖµ2
9¡¢ËµÃ÷£ºin µÄʹÓ÷½·¨
select  from table1 where a [not] in (¡®Öµ1¡¯,¡¯Öµ2¡¯,¡¯Öµ4¡¯,¡¯Öµ6¡¯)
10¡¢ËµÃ÷£ºÁ½ÕŹØÁª±í£¬É¾³ýÖ÷±íÖÐÒѾÔÚ¸±±íÖÐûÓеÄÐÅÏ¢
delete from table1 where not exists ( select  from table2 where table1.field1=table2.field1 )
11¡¢ËµÃ÷£ºËıíÁª²éÎÊÌ⣺
select  from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where
.....
12¡¢ËµÃ÷£ºÈճ̰²ÅÅÌáÇ°Îå·ÖÖÓÌáÐÑ
SQL select  from Èճ̰²ÅÅ where datediff(''''minute'''',f¿ªÊ¼Ê±¼ä,getdate())5
13¡¢ËµÃ÷£ºÒ»Ìõsql Óï¾ä¸ã¶¨Êý¾Ý¿â·ÖÒ³
select top 10 b. from (select top 20 Ö÷¼ü×Ö¶Î,ÅÅÐò×ֶΠfrom ±íÃû order by ÅÅÐò×ֶΠdesc) a,±íÃû b where
b.Ö÷¼ü×ֶΠ= a.Ö÷¼ü×ֶΠorder by a.ÅÅÐò×Ö¶Î
14¡¢ËµÃ÷£ºÇ°10Ìõ¼Ç¼
select top 10  form table1 where ·¶Î§
15¡¢ËµÃ÷£ºÑ¡ÔñÔÚÿһ×ébÖµÏàͬµÄÊý¾ÝÖжÔÓ¦µÄa×î´óµÄ¼Ç¼µÄËùÓÐÐÅÏ¢(ÀàËÆÕâÑùµÄÓ÷¨¿ÉÒÔÓÃÓÚÂÛ̳ÿÔÂÅÅÐаñ,
ÿÔÂÈÈÏú²úÆ··ÖÎö,°´¿ÆÄ¿³É¼¨ÅÅÃû,µÈµÈ.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16¡¢ËµÃ÷£º°üÀ¨ËùÓÐÔÚ TableA Öе«²»ÔÚ TableBºÍTableC ÖеÄÐв¢Ïû³ýËùÓÐÖظ´ÐжøÅÉÉú³öÒ»¸ö½á¹û±í
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17¡¢ËµÃ÷£ºËæ»úÈ¡³ö10ÌõÊý¾Ý
select top 10  from tablename order by newid()
18¡¢ËµÃ÷£ºËæ»úÑ¡Ôñ¼Ç¼
select newid()
19¡¢ËµÃ÷£ºÉ¾³ýÖظ´¼Ç¼
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20¡¢ËµÃ÷£ºÁгöÊý¾Ý¿âÀïËùÓеıíÃû
select name from sysobjects where type=''''U''''
21¡¢ËµÃ÷£ºÁгö±íÀïµÄËùÓеÄ
select name from syscolumns where id=object_id(''''TableName'''')
22¡¢ËµÃ÷£ºÁÐʾtype¡¢vender¡¢pcs×ֶΣ¬ÒÔtype×Ö¶ÎÅÅÁУ¬case¿ÉÒÔ·½±ãµØʵÏÖ¶àÖØÑ¡Ôñ£¬ÀàËÆselect ÖеÄcase¡£
select type,sum(case vender when ''''A'''' then pcs else 0 end),sum(case vender when ''''C'''' then pcs else 0
end),sum(case vender when ''''B'''' then pcs else 0 end) FROM tablename group by type
ÏÔʾ½á¹û£º
type    vender     pcs
µçÄÔ     A          1
µçÄÔ     A          1
¹âÅÌ     B          2
¹âÅÌ     A          2
ÊÖ»ú     B          3
ÊÖ»ú     C          3
23¡¢ËµÃ÷£º³õʼ»¯±ítable1
TRUNCATE TABLE table1
24¡¢ËµÃ÷£ºÑ¡Ôñ´Ó10µ½15µÄ¼Ç¼
select top 5  from (select top 15  from table order by id asc) table_±ðÃû order by id desc
25,²é¿´µ±Ç°²Ù×÷µÄÊý¾Ý¿â
¡¡¡¡select db_name(dbid) from master.dbo.sysprocess where spid=@@spid
Trackback httptb.blog.csdn.NETTrackBack.aspxPostId=379405