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¡¢¶¨ÒåÖ¸Á£ºusing System.Data.SqlClient;
2¡¢¶¨ÒåsqlÁ¬½Ó£º SqlConnection conn= new SqlConnection("server=(local);database=colorring;uid=sa;pwd=;");
3¡¢´ò¿ªsqlÁ¬½Ó£º conn.Open();
4¡¢¶¨ÒåsqlÓï¾ä£ºstring sqlstr = "insert into test values (123321)";
5¡¢×é×°sqlÓï¾äºÍÁ¬½Ó£ºSqlCommand cmd = new SqlCo ......
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 ......
Ê×ÏÈÔÚsqlserverµÄ°²×°Â·¾¶Ï£¬È磺D:\Program Files\Microsoft SQL
Server\MSSQL\£¬ÕÒµ½ÎļþÃûÊÇscptxfr.exeµÄÎļþ,ÀûÓÃÃüÁîÐй¤¾ß£º¾ßÌåÓ÷¨ÈçÏ£º
D:\PROGRA~1\MICROS~2\MSSQL\>scptxfr/?
ÃüÁîÐÐÓï·¨:
SCPTXFR /s
<·þÎñÆ÷> /d <Êý¾Ý¿â> {[/I] | [/P <ÃÜÂë>]}
&n ......
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 ......