B*****g
发帖数: 34098 | 1 曾哥度假也要回答技术问题
【 以下文字转载自 Database 讨论区 】
发信人: zenny (素能), 信区: Database
标 题: Re: 各位各位，江湖救急 -- 数据被删
发信站: BBS 未名空间站 (Tue May 1 08:44:27 2012, 美东)
Please run this script first. Check the database properties first to get the
create date of the database
select * from sysdatabases
the crdate(?) is the create date for that database.
Then run the following script to find out available backups.
I'm on vacation now. So cannot verify the following script. If there's typo
or grammar error, please correct it y... 阅读全帖 |
|
z***y
发帖数: 7151 | 2 Please run this script first. Check the database properties first to get the
create date of the database
select * from sysdatabases
the crdate(?) is the create date for that database.
Then run the following script to find out available backups.
I'm on vacation now. So cannot verify the following script. If there's typo
or grammar error, please correct it yourself:
SELECT
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
... 阅读全帖 |
|
c*****d
发帖数: 6045 | 3 我没有太理解你的意思
sql server中的msdb.sysjobs内容和oracle中的dbms_jobs类似
但是oracle中没有sql server中的msdb.sysjobhistory |
|
m******9
发帖数: 104 | 4 I am new to MSSQL. Can anyone tell me how to backup a single table? It is
system table msdb. I'll need to backup few table from msdb.
Thank you |
|
g***l
发帖数: 18555 | 5 COPY过去，DETTACH ATTACH是比较保险的。如果出问题还可以SWITCH BACK。如果能
LIVE接上新SAN的话，调试好CLUSTER，就是DETACH-COPY-ATTACH的时间，不过要注意你
的MASTER, MSDB ,TEMPDB在什么盘上，如果旧SAN还保留的话就是个PRODUCTION DB，应
该很容易。如果旧SAN要关掉，你的MASTER TEMDB MSDB MODEL都要移走，就很麻烦了。
SAN的CLONE也可以，我觉得风险很高，但愿PROGRAM没装在SAN上，要CLONE的话，要随时
准备不WORK，SWITCH BACK，而且在线CLONE的话，会损失数据，离线CLONE，又有DOWNTIME，
其实不如连SERVER也换了得了，另起炉灶，调试好了，搞个RENAME SERVER得了。而且现在
都是64BIT的操作系统，不如升级全做了，WINDOWS 2008+SQL SERVER 2008，一次到位，
用个BACKUP测试好了全换掉，老系统就当DEV好了。单换个STORAGE，风险高，就是硬盘大点，
新SAN可能IO PERFORMANC... 阅读全帖 |
|
p*********r
发帖数: 1440 | 6 but how do you do failover?
let's say note 1 has 2012 installed, while note 2 has 2008 installed. both
notes use san storage for data and log files.
the agent jobs created on notes 1 is stored in 2012 version msdb database.
note 1 fails, the instance will run on note 2. how will the agent jobs run
on note 2, which has 2008 version msdb?
thanks. |
|
n********6
发帖数: 1511 | 7 前两周刚做完bcp，所以比较熟悉。
例子：table varchar(9)
1。如果用format，一旦有不符合format，就报错。
2。如果不用format，自己写validation，要注意table里面的varchar长度限制。
3。超过长度限制的，bcp报错，但是shell不传到数据库。
4。bcp会truncate(忽略)该条记录。读取下一条记录。注意：假如record是10位，不会
读取前9位。
5。bcp会计数上述4的错误次数。默认允许最大错误次数是10。可以修改默认值。
6。达到最大默认值，shell命令报错，job agent知道。
7。我当时没用format,用，EXEC msdb.dbo.sp_send_dbmail，把validation的信息发信
给business group。DBA不需要管数据是否合理。只要job run就可以了。
8。send_dbmail可以送2个附件。一个来自于file directory。另一个来自于query。
file? |
|
m*****h
发帖数: 18 | 8 Thanks for the reply. should I create a separate maintenance plan for Full
backup, differential backup and transaction log backup? then add a schedule
for each backup? When backuping a user db, should i select the master, msdb database too? Thanks |
|
gy
发帖数: 620 | 9 这个也试过了, 不是我要的那种.
在MS SQL里, 我可以直接query msdb.dbo.sysjobs, 把server上的job都提出来看
status. 在Oracle上怎么就没这么个table or view的呢?? |
|
w*******e
发帖数: 1622 | 10 ft....google了一下, 原来是Master Server(MSX) 和Target Server(TSX)的关系.
According to microsoft: "To use multiserver job processing, the SQL Server
Agent service account must be a member of the msdb database role
TargetServersRole on the master server."
注意事项: Each target server reports to only one master server. You must
defect a target server from one master server before you can enlist it into
a different one......等等...
A |
|
i****a
发帖数: 36252 | 11 easiest way is to export the data into a file.
or copy the table into another table or database by
select *
into anotherDatabase..
from msdb.. |
|
z***y
发帖数: 7151 | 12 这个不奇怪啊。
你可以看一看那个procedure, 你看里面有几个对temp table 的改变， 这些数据的改
变都会触发
recompilation。
我这个版本是2008 SP2的。
create procedure sys.sp_replmonitorrefreshagentdata
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@agent_id2 int
,@publisher_id int
,@xact_seqno varbinary(16)
,@logreader_latency int
,@publisher_db sysname
,@publication sysname
-- security check
-- Has to b... 阅读全帖 |
|
i****a
发帖数: 36252 | 13 you need the last full backup.
look at your msdb.dbo.backupset table, check the LSN (log sequence number).
you need to have all the backup files that covers the LSN from the full
backup to the lastest diff backup. |
|
B*********L
发帖数: 700 | 14 下面的，是现在的code。其中A column，希望变成Hyperlink，怎么改比较简洁？谢谢
了。
DECLARE @SQLString nvarchar(max)
SET @SQLString = N'
SET NOCOUNT ON;
SELECT A,B,C FROM TABLE1
SELECT A,E,F FROM TABLE2
SELECT A,H,K FROM TABLE3
SELECT A,X,Y FROM TABLE4
'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'yyy'
, @recipients = 'x*[email protected]'
, @subject = 'TEST'
, @query = @SQLString
, @exclude_query_output = 1
, @query_no_truncate = 0
, @query_result_header = 1
, @append_query_error=1 |
|
B*********L
发帖数: 700 | 15 总算可以了：
DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML =
N'
SET NOCOUNT ON;
PRINT ''name NAME_html
''
SELECT [name]
,''http://xxxx.com/Report.aspx?ID='' + [name] + ''>'' + [name] + ''''
,''
''
FROM table WITH (NOLOCK)
' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'RF_Performance'
, @recipients = 'l*****[email protected]'
, @subject = 'test'
, @importance = 'High'
, @query = @tableHTML
, @exclude_query_output = 1
, @query_no_truncate = 0
,... 阅读全帖 |
|
z***y
发帖数: 7151 | 16 欢迎大家补充。
T-SQL
● Tools for the DBA
● Development
○ SPs
○ Triggers
○ Functions
○ CTEs
○ Temporary objects
Installations and patching
● Standalone
● Cluster
SQL Server Upgrades
● Standalone
● Cluster
Configuration Best Practices
● Database files placement
● Tempdb configuration
● Lock PAGES in memory
● Memory configurations
Performance tuning high level (how to)
● Indexes
● Execution plans
● DMVs
● Performance Counters
● Database design
● Parallelism
● Compilation/
● recompilations
Security
● Login... 阅读全帖 |
|
f********y
发帖数: 696 | 17 我又建立了一个测试SSIS Package 传输数据从MYSQL 到MSSQL。 在VS BIDS里运行没有
问题，所有的数据都传过来了。Import 这个Package 到Intergration Service下的
MSDB，执行这个Package，所有的数据也成功地传输过来了。我的问题是我需要建立一
个JOB定期执行它。我建立了credential，建立了proxy account，改变了Package
Protection Level as Rely on server storage and roles for access control，选
择了Use 32 bit runtime in EXECUTINON OPTION，但仍然没有数据传输过来，它报告
THE JOB SUCCEEDED， 但是Data Flow objects cannot be loaded. Check if
Microsoft.SqlServer.PipelineXml.dll is properly registered
Description: Data Flow objects cann... 阅读全帖 |
|
s**********o
发帖数: 14359 | 18 我一个SERVER上有很多SSIS PACKAGE STORED IN MSDB，
如何找出CONNECTION MANAGER用IP ADDRESS的呢
我的一个FILE SERVER要MIGRATION，用IP的话就不WORK,
假设有300个PACKAGES要SEARCH |
|
