DBA常用操作.docx
《DBA常用操作.docx》由会员分享,可在线阅读,更多相关《DBA常用操作.docx(40页珍藏版)》请在冰点文库上搜索。
DBA常用操作
DBA常用操作
---try
sysaltfiles主数据库保存数据库的文件
syscharsets主数据库字符集与排序顺序
sysconfigures主数据库配置选项
syscurconfigs主数据库当前配置选项
sysdatabases主数据库服务器中的数据库
syslanguages主数据库语言
syslogins主数据库登陆帐号信息
sysoledbusers主数据库链接服务器登陆信息
sysprocesses主数据库进程
sysremotelogins主数据库远程登录帐号
syscolumns每个数据库列
sysconstrains每个数据库限制
sysfilegroups每个数据库文件组
sysfiles每个数据库文件
sysforeignkeys每个数据库外部关键字
sysindexs每个数据库索引
sysmembers每个数据库角色成员
sysobjects每个数据库所有数据库对象
syspermissions每个数据库权限
systypes每个数据库用户定义数据类型
sysusers每个数据库用户
/**//*********************************************************************************
*FielName:
backup.sql
*Function:
自动备份
*Author:
Yahong
*Date:
2005-5-102005-5-192006-8-12007-09-18
*Version:
00010203
*
*Remark:
*2006-08-01增加差异备份和完全备份两种情况,生成多个备份副本
*2008-09-18增加备份一个实例中的所有数据库的情况,并在备份后清除日志
*
*********************************************************************************/
usemaster
declare@DbNamevarchar(255),@dirvarchar(256),@dir_dbvarchar(256),
@verbvarchar(256),@cmdvarchar(256),
@backup_namevarchar(256),@dynamic_namevarchar(10),
@disk_namevarchar(256),@copynvarchar(100),
@todaydatetime,@weekdayint
--建立网络连接
execxp_cmdshell'netuseK:
/delete'
execxp_cmdshell'netuseI:
/delete'
execxp_cmdshell'netuseK:
\\193.254.40.118\backupbackup/user:
Web\backup'
execxp_cmdshell'netuseI:
\\172.16.8.48\databackupbackup/user:
QA-SERVER-TEST\backup'
--设定名字
set@today=getdate()
set@dynamic_name=convert(varchar(10),@today,120)
set@dir='K:
\'+@dynamic_name
set@dir_db=@dir+'Database'
set@verb='mkdir'
--建立目录
set@cmd=@verb+@dir_db
execxp_cmdshell@cmd
declarecur_databasecursorforward_onlyread_onlyfor
selectnamefromsysdatabases
wheredbid>4--系统数据库的dbid<=4
opencur_database
fetchnextfromcur_database
into@DbName
while@@fetch_status=0
begin
set@backup_name=@DbName+'_'+@dynamic_name
set@disk_name=@dir_db+'\'+@backup_name+'.bak'
--添加备份设备
EXECsp_addumpdevice'disk',@backup_name,@disk_name
set@weekday=datepart(dw,@today)
if(@weekday=6)--如果是周五,则进行完全备份
BACKUPDATABASE@DbNameTO@backup_name
else--其他时候进行差异备份
BACKUPDATABASE@DbNameTO@backup_namewithdifferential
--清理日志
backuplog@DbNamewithno_log
--释放设备
execsp_dropdevice@backup_name
--复制备份副本到其他地方
set@copy='copy'+@disk_name+'I:
'
execxp_cmdshell@copy
--备份下一个数据库
fetchnextfromcur_database
into@DbName
end
closecur_database
deallocatecur_database
--删除网络连接
execxp_cmdshell'netuseK:
/delete'
execxp_cmdshell'netuseI:
/delete'
/**//******************************************************************************
*
*FileName:
Restore.sql
*Function:
数据库还原
*Author:
Yahong
*Version:
00
*Date:
2007-09-18
*Remark:
*
*******************************************************************************/
usemaster
declare
@DbNamevarchar(255)--数据库的名字
@WholeFileNamevarchar(255)--完全备份的文件名
@DifferentFileNamevarchar(255)--差异备份的文件名
@MasterFileNamevarchar(255)--数据文件名,注意他们都是逻辑名称
@LogFileNamevarchar(255)--日志文件名
@TargetDirvarchar(255)--还原后数据库文件所在的路径,如果没有指定该参数,
--则必须存在与原数据库相同的路径
declare@WholeDeviceNamevarchar(255)
@DifferenctDeviceNamevarchar(255)
@TargetMasterFileNamevarchar(255)
@TargetLogFileNamevarchar(255)
--建立网络链接
execxp_cmdshell'netuseK:
\\172.16.8.48\200709backup/User:
qa-server-test\backup'
--在这里设置需要备份的文件等信息
set@DbName='CCTQA'--需要还原的数据库的名字,注意不要搞错了,否则
--覆盖了其他的数据库,可别说我没有提醒你
set@WholeFileName='CCTQA_2007-09-14.bak'--完全备份文件
--以下4行如果没有,不要指定,把他们注释掉就行了
set@DifferentFileName='CCTQA_2007-09-17.bak'--最后一次差异备份文件
set@MasterFileName='CCTQA_Data'--数据文件
set@LogFileName='CCTQA_Log'--日志文件
set@TargetDir='D:
\CCTQA\Databae'--目标路径
--设置目标路径
set@TargetMasterFileName=@TargetDir+'\'+@MasterFileName
set@TargetLogFileName=@TargetDir+'\'+@LogFileName
--添加还原设备
set@WholeDeviceName=@DbName+'WholeDevice'
set@WholeFileName='K:
\'+@WholeFileName
execsp_addumpdevice'disk',@WholeDeviceName,@WholeFileName
--开始备份
if(isnull(@DifferentFileName,'')<>'')--如果具有差异备份的还原
begin
--添加差异备份还原的设备
set@DifferenctDeviceName=@DbName+'DifferenctDevice'
set@DifferentFileName='K:
\'+@DifferentFileName
execsp_addumpdevice'disk',@DifferenctDeviceName,@DifferentFileName
--备份
if(isnull(@TargetDir,'')='')
restoredatabase@DbNamefrom@WholeDeviceName
withNORECOVERY
else--如果还原后的数据库文件的路径与备份前的路径不一致
restoredatabase@DbNamefrom@WholeDeviceName
withNORECOVERY,
move@MasterFileNameto@TargetMasterFileName,
move@LogFileNameto@TargetLogFileName
restoredatabase@DbNamefrom@DifferenctDeviceName
end
else
begin--只有完全备份的还原
if(isnull(@TargetDir,'')='')
restoredatabase@DbNamefrom@WholeFileName
else
restoredatabase@DbNamefrom@WholeFileName
withmove@MasterFileNameto@TargetMasterFileName,
move@LogFileNameto@TargetLogFileName
end
--释放备份设备
execsp_dropdevice@WholeDeviceName
if(isnull(@DifferentFileName,'')<>'')
execsp_dropdevice@DifferenctDeviceName
--删除网络链接
execxp_cmdshell'netuseK:
/delete'
/**//************************************************************************
*
*FileName:
ShrinkLog.sql
*Function:
收缩数据库的日志文件
*Author:
Yahong
*Version:
00
*Date:
2007-09-16
*Remark:
*
*************************************************************************/
--
--第一步:
设置需要收缩的数据库,找到需要收缩数据文件
--
usecctqa
selectSize/128Size,Namefromsysfiles
/**//*
declare@LogNamevarchar(255),@TargetSizeint
--
--
--第二步:
设置需要收缩的日志文件的逻辑名字和收缩后的大小
--千万不要搞错了,选错了文件,有可能会丢失数据,那时候
--哭都哭不回来了。
--
--
set@LogName='CCTQA_Log'
set@TargetSize=1
declare@strvarchar(300),@DatabaseNamevarchar(255)
set@DatabaseName=db_name()
if(notexists(select*fromsysfileswherename=@LogName))
begin
set@str='没有找到日志文件'+@LogName
raiserror(@str,0,1)
endelse
begin
declare@curSizeint,@maxTimeint
set@maxTime=10
set@curSize=(selectsizefromsysfileswherename=@LogName)/128
print'收缩之前的日志文件的大小是:
'+cast(@curSizeasvarchar(10))+'MB'
while(@curSize>@TargetSize)and(@maxTime>0)
begin
backuplog@DatabaseNamewithno_log
DBCCSHRINKFILE(@LogName,@TargetSize)
set@curSize=(selectsizefromsysfileswherename=@LogName)/128
set@maxTime=@maxTime-1
end
set@curSize=(selectsizefromsysfileswherename=@LogName)/128
print'收缩之后的日志文件的大小是:
'+cast(@curSizeasvarchar(10))+'MB'
end
*/
--ConfigureDistribution.sql
--ScriptingreplicationconfigurationforserverCA\SQLA.
--InstallingtheserverCA\SQLAasaDistributor.
usemaster
GO
execsp_adddistributor@distributor=N'CA\SQLA',@password=N''
GO
execsp_adddistributiondb@database=N'distribution'
@data_folder=N'C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data'
@data_file_size=4
@log_folder=N'C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data'
@log_file_size=2
@min_distretention=0
@max_distretention=72
@history_retention=48
@security_mode=1
GO
use[distribution]
if(notexists(select*fromsysobjectswherename='UIProperties'andtype='U'))
createtableUIProperties(idint)
if(exists(select*from:
:
fn_listextendedproperty
('SnapshotFolder','user','dbo','table','UIProperties',null,null)))
EXECsp_updateextendedpropertyN'SnapshotFolder',N'\\Ca\ReplData','user',
dbo,'table','UIProperties'
else
EXECsp_addextendedpropertyN'SnapshotFolder','\\Ca\ReplData','user',
dbo,'table','UIProperties'
GO
execsp_adddistpublisher@publisher=N'CA\SQLA'
@distribution_db=N'distribution'
@security_mode=1
@working_directory=N'\\Ca\ReplData'
@trusted=N'false'
@thirdparty_flag=0
@publisher_type=N'MSSQLSERVER'
GO
--CreatePublication.sql
use[TestDB]
execsp_replicationdboption@dbname=N'TestDB',@optname=N'publish',@value=N'true'
GO
--Addingthetransactionalpublication
use[TestDB]
execsp_addpublication@publication=N'TestDB'
@description=N'Transactionalpublicationofdatabase''TestDB''fromPublisher''CA\SQLA''.'
@sync_method=N'concurrent'
@retention=0
@allow_push=N'true'
@allow_pull=N'true'
@allow_anonymous=N'true'
@enabled_for_internet=N'false'
@snapshot_in_defaultfolder=N'true'
@compress_snapshot=N'false'
@ftp_port=21
@ftp_login=N'anonymous'
@allow_subscription_copy=N'false'
@add_to_active_directory=N'false'
@repl_freq=N'continuous'
@status=N'active'
@independent_agent=N'true'
@immediate_sync=N'true'
@allow_sync_tran=N'false'
@autogen_sync_procs=N'false'
@allow_queued_tran=N'false'
@allow_dts=N'false'
@replicate_ddl=1
@allow_initialize_from_backup=N'false'
@enabled_for_p2p=N'false'
@enabled_for_het_sub=N'false'
GO
execsp_addpublication_snapshot@publication=N'TestDB'
@frequency_type=1
@frequency_interval=0
@frequency_relative_interval=0
@frequency_recurrence_factor=0
@frequency_subday=0
@frequency_subday_interval=0
@active_start_time_of_day=0
@active_end_time_of_day=235959
@active_start_date=0
@active_end_date=0
@job_login=null
@job_password=null
@publisher_security_mode=0
@publisher_login=N'sa'
@publisher_password=N''
use[TestDB]
execsp_addarticle@publication=N'TestDB'
@article=N'Family'
@source_owner=N'dbo'
@source_object=N'Family'
@type=N'logbased'
@description=N''
@creation_script=null
@pre_creation_cmd=N'drop'
@schema_option=0x000000000803509F
@identityrangemanagementoption=N'manual'
@destination_table=N'Family'
@destination_owner=N'dbo'
@status=0
@vertical_partition=N'false'
@ins_cmd=N'CALL