SQL Server×Ô¶¯ÒìµØ±¸·Ý·½·¨ (¾«Ñ¡)
SQL×Ô¶¯ÒìµØ±¸·Ý·½·¨
·½·¨Ò»£º
mssqlÊý¾Ý¿âÔ¶³Ì±¸·ÝµÄjob
/*ÔÚÔ¶³Ì»úÆ÷²Ù×÷ϵͳµÄ¼ÆËã»ú¹ÜÀíÀィÁ¢Ò»¸öÓû§ÃûΪkyleµÄÓû§£¬ÃÜÂëΪ1234£¬Í¬Ê±ÔÚÄÇ̨»úÆ÷µÄ·ÇϵͳÅÌÀィһ¸öÃûΪbackupµÄ¹²ÏíÎļþ¼Ð£¬ÎªÁË°²È«ÁíÍâÉèÖÃÕâ¸öÎļþ¼ÐÖ»ÓÐÕâ¸ökyleÓû§¿ÉÒÔ·ÃÎÊ¡£*/
declare @sql varchar(500)
select @sql='\\10.2.0.12\backup\dbname'+'_db_'+convert(varchar(10),getdate(),112) +
substring(convert(varchar(10),getdate(),108),1,2) +'.bak'
exec master..xp_cmdshell 'net use \\10.2.0.12\backup 1234 /user:remotehost\kyle'
backup database dbname to disk=@sql --±¸·ÝÊý¾Ý¿â£¬ÕâÀïµÄ10.2.0.12ΪԶ³Ì»úÆ÷µÄip£¬remotehostΪԶ³Ì»úÆ÷µÄ»úÆ÷Ãû£¬dbnameΪ±¾µØsqlserver·þÎñÆ÷Òª±¸·ÝµÄÊý¾Ý¿â
go
declare @sql varchar(500)
select @sql='del '+'\\10.2.0.12\backup\dbname'+'_db_'+convert(varchar(10),dateadd(day,-7,getdate()),112) +
substring(convert(varchar(10),dateadd(day,-7,getdate()),108),1,2) +'.bak'
exec master..xp_cmdshell @sql --ɾ³ý7ÌìÇ°µÄ±¸·Ý£¬Ò²¾ÍÊÇÖ»±£Áô7¸ö×îб¸·Ý
go
·½·¨¶þ£º
ÓÃSQL×Ô´øµÄschedule¶¨Ê±°ÑÊý¾Ý¿âbackupÉúÎļþ£¬ÓÃwindowsµÄ¼Æ»®ÈÎÎñ¶¨Ê±copyÎļþµ½ÆäËü»úÆ÷£¬ÔÙÓÃSQL×Ô´øµÄschedule¶¨Ê±°ÑÎļþrestore³ÉÊý¾Ý¿â¡£
Èç¹û´ÓÊý¾ÝÔ´»úÆ÷µÄEnterprise ManagerÖпÉÒÔ¿´µ½»òÌí¼Ó×¢²áÄ¿±êÊý¾Ý¿âµÄ»°£¨¼´¿ª·ÅÁË1433¶Ë¿Ú£©£¬¿ÉÒÔÑ¡ÔñÓõ¼³ö¹¦ÄÜ£¬ÔÚDTS wizardÖÐÑ¡ºÃÔ´Êý¾Ý¿âºÍÄ¿±êÊý¾Ý¿â£¬Ñ¡ÖÐÔ´Êý¾Ý¿âÖÐËùÓÐ±í£¬²¢¼ÇµÃÅäÖÃÿ¸ö±íµÄtransformÏîΪ“Çå¿ÕÄ¿±ê±í”£¬ÓÐ×ÔÔö³¤×ֶεĻ°ÒªÑ¡“ÔÊÐíÌí¼Ó×ÔÔö³¤×ֶΔ£¬½ÓÏÂÀ´ÒªÑ¡¶¨ÆÚÖ´Ðв¢Ñ¡ºÃ¼Æ»®£¬¾ÍOKÁË¡£
all taskÖÐÑ¡backupµÄʱºò£¬×îÏÂÃæÓÐÒ»Ïî¾ÍÊÇschedule
ÓÒ¼üµã»÷ÄãÒª±¸·ÝµÄÊý¾Ý¿â£¬µ¯³ö²Ëµ¥ÖÐÑ¡“ËùÓÐÈÎÎñ”Öеē±¸·ÝÊý¾Ý¿â”£¬×îÏÂÃæÑ¡ÖГ¼Æ»®±í”È»ºó¾Í¿ÉÒÔÉèÖö¨ÆÚÔËÐеģ¬Èç¹ûÄãµÄÊý¾Ý¿âÓõÄÊÇȱʡµÄÍêÈ«»Ö¸´Ä£Ê½£¬Ñ¡²îÒ챸·ÝÓ¦¸ÃÊDZȽϺÏÀíµÄÑ¡Ôñ£¬ÕâÑù±¸·ÝËÙ¶È×î¿ì¡£
·½·¨Èý£º
ÏÈ˵һÏÂ˼·£¬¾ÍÊÇÔÚ·þÎñÆ÷Éϱ¸·ÝÉú³ÉÎļþºó£¬ÔÙͨ¹ýDOSÃüÁ´ËÎļþCOPYµ½Áíһ̨µçÄÔ¡£
ÏÈÔÚ·þÎñÆ÷ÉÏÔËÐÐÊý¾Ý¿â±¸·Ý¡£
È»ºóÔÚÒìµØ»úÉϹ²ÏíÒ»¸öÎļþ¼Ð£¬×öΪ·þÎñÆ÷ÉϵÄÒ»¸öÓ³ÉäÍøÂçÇý¶¯Æ÷¡£
½«ÎļþCOPYµ½Õâ¸öÎļþ¼Ð¾ÍËãÍêÊÂÁË£¬µ«ÊÇÒªÔõôʵÏÖ×Ô¶¯²¢ÇÒ¶¨Ê±COPYÄØ£¿
¾ÍÓÃDOSÃüÁîÀ´ÊµÏÖ×
Ïà¹ØÎĵµ£º
1.ÈçºÎ½«Ò»¸öSQL Server±íµÄ½á¹¹¸´ÖƵ½ÁíÒ»¸öн¨µÄ±íÖÐ
select * into бí from ¾É±í where 0 = 1
2.ÐÞ¸Ä×Ö¶ÎĬÈÏÖµ
alter table ±íÃû add default ĬÈÏÖµ for ×Ö¶ÎÃû
3. È¡µÃÊý¾Ý¿â½á¹¹
SELECT name from sysdatabases
4.Ôö¼ÓÁÐ
......
Create PROCEDURE [dbo].[ChangeObjectOwner]
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner'&n ......
/******* µ¼³öµ½excel
EXEC master..xp_cmdshell ’bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""’
/*********** µ¼ÈëExcel
SELECT *
from OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source="c:\test.xls";User ......
http://inthirties.com:90/thread-1202-1-1.html
Ç°ÃæÎÄÕ ”v$sql,v$sqlarea,v$sqltextÓÐʲôÇø±ð“ ¼û http://blog.csdn.net/inthirties/archive/2010/03/10/5364777.aspx
ÕâÀï²¹³äÒ»ÏÂ
²¹³ä£º
1¡¢²éÒ»ÏÂÕâЩÊÓͼµÄ¶¨ÒåÄã¾ÍÄÜÀí½â£¬ËüÃǵÄÔ´¶¼ÊÇÒ»¸ö¡£
SELECT view_definition from v$fixed_view_definiti ......
1. ¼òÊö private¡¢ protected¡¢ public¡¢ internal ÐÞÊηûµÄ·ÃÎÊȨÏÞ¡£
´ð . private : ˽ÓгÉÔ±, ÔÚÀàµÄÄÚ²¿²Å¿ÉÒÔ·ÃÎÊ¡£
protected : ±£»¤³ÉÔ±£¬¸ÃÀàÄÚ²¿ºÍ¼Ì³ÐÀàÖпÉÒÔ·ÃÎÊ¡£
public : ¹«¹²³ÉÔ±£¬ÍêÈ«¹«¿ª£¬Ã»ÓзÃÎÊÏÞÖÆ¡£
internal: ÔÚÍ¬Ò ......