Do as I Say, Not as I Do

healthyapricotMechanics

Nov 5, 2013 (3 years and 7 months ago)

74 views

Do as I Say, Not as I Do

Robert L Davis

Who am I?

No one of
consequence.

Time is short!


Please hold all
questions until the next
person’s session.

xp_createsubdir

Exec
xp_createsubdir

'c:
\
bak
'
;


Creates the specified directory path


Doesn’t overwrite existing directories or files


No error if directories already exist


Error only returned if insufficient permissions

xp_fileexist

Exec
xp_fileexist

'c:
\
bak
'
;

Exec
xp_fileexist

'c:
\
bak
\
db.bak
'
;


Checks for existence of file or folder


File Exists = 1 if a file and exists


File is a Directory = 1 if a folder and exists

xp_instance_regxxx

xp_instance_regread
;

xp_instance_regenumvalues
;

xp_instance_regenumkeys
;

xp_instance_reg
write
;

xp_instance_reg
add
multistring
;

xp_instance_reg
remove
multistring
;

xp_instance_reg
delete
value
;

xp_instance_reg
delete
key
;

Declare

@
BakDir

nvarchar
(
4000
),


@
DefBakDir

nvarchar
(
4000
);


Set

@Instance
=

IsNull
(
'
\
'

+

Cast
(
ServerProperty
(
'
InstanceName
'
)

as

sysname
),

''
);

Set

@DefBakDir
=

'E:
\
mssql
\
bak'

+

@
Instance;


--

Check default backup location

Exec

xp_instance_regread

N'HKEY_LOCAL_MACHINE'
,



N'Software
\
Microsoft
\
MSSQLServer
\
MSSQLServer
'
,



N'BackupDirectory
'
,



@
BakDir

output
,



'
no_output

;


--

If default setting null or <> to desired path, set

If

IsNull
(
@
BakDir
,

''
)

<>

@
DefBakDir


Begin


Exec

xp_instance_regwrite

N'HKEY_LOCAL_MACHINE'
,




N'Software
\
Microsoft
\
MSSQLServer
\
MSSQLServer
'
,



N'BackupDirectory
'
,



REG_SZ
,



@
DefBakDir
;


End


--

Make sure path exists (DUH!)

Exec

xp_create_subdir

@
BakDir
;

xp_enumerrorlogs

xp_readerrorlogs

[#]

xp_dirtree

'c
:'

xp_subdirs

'c:
\
bak
'

xp_fixeddrives

Trace flag 3604

DBCC
TraceOn
(3604);


Redirects output from the error log to
the console


Use with DBCC PAGE

DBCC
Ind
()

DBCC
Ind

(
'DB
'
,

'
Table'
,

Index ID);


Returns internal info about an index

DBCC

Ind


(
'AdventureWorksDW2008R2
'
,

'
DimAccount
'
,

1
);

DBCC Page()

DBCC Page (
'DB
'
,
File #,

Page
#,

Option #);


Returns internal info about a page


Use with trace flag 3604


Use with DBCC
Ind


PageFID

= File number


PagePID

= Page #

DBCC

Page

(
'AdventureWorksDW2008R2
'
,

1
,

3458
,

3
);

DBCC Page (
'DB
'
,
File #,

Page
#,

Option #);

DBCC

TraceOn

(
3604
);


--

PageFID

= File
Number,
PagePID

= Page Number

DBCC

Page

(
'AdventureWorksDW2008R2'
,

1
,

3458
,

3
)

;

Do as I Say, Not as I Do

Q&A

Do as I Say, Not as I Do

Thank You!


The PowerPoint slide
-
deck will be available on
my website:


http://www.sqlsoldier.com




Microsoft Certified Master