1 / 23

SQL Server Backup and Error Management Guide

Learn about creating directories, checking file existence, managing registry values, error logs, tracing, and database internals in SQL Server.

chibale
Download Presentation

SQL Server Backup and Error Management Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Do as I Say, Not as I Do Robert L Davis

  2. Who am I? No one of consequence.

  3. Time is short!Please hold all questions until the next person’s session.

  4. xp_createsubdir

  5. 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

  6. xp_fileexist

  7. 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

  8. xp_instance_regxxx

  9. xp_instance_regread;xp_instance_regenumvalues;xp_instance_regenumkeys;xp_instance_regread;xp_instance_regenumvalues;xp_instance_regenumkeys;

  10. xp_instance_regwrite;xp_instance_regaddmultistring;

  11. xp_instance_regremovemultistring;xp_instance_regdeletevalue;xp_instance_regdeletekey;xp_instance_regremovemultistring;xp_instance_regdeletevalue;xp_instance_regdeletekey;

  12. Declare @BakDirnvarchar(4000), @DefBakDirnvarchar(4000);Set @Instance =IsNull('\'+Cast(ServerProperty('InstanceName')assysname),'');Set @DefBakDir ='E:\mssql\bak'+ @Instance;-- Check default backup locationExecxp_instance_regreadN'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @BakDiroutput,'no_output‘;-- If default setting null or <> to desired path, setIfIsNull(@BakDir,'')<> @DefBakDirBeginExecxp_instance_regwriteN'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', REG_SZ, @DefBakDir;End-- Make sure path exists (DUH!)Execxp_create_subdir@BakDir;

  13. xp_enumerrorlogsxp_readerrorlogs [#]xp_dirtree'c:'xp_subdirs'c:\bak'xp_fixeddrives

  14. Trace flag 3604

  15. DBCC TraceOn(3604); • Redirects output from the error log to the console • Use with DBCC PAGE

  16. DBCC Ind()

  17. DBCC Ind ('DB','Table',Index ID); • Returns internal info about an index • DBCCInd • ('AdventureWorksDW2008R2', • 'DimAccount', • 1);

  18. DBCC Page()

  19. 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 # • DBCCPage • ('AdventureWorksDW2008R2', 1, 3458, 3);

  20. DBCC Page ('DB', File #,Page #,Option #); DBCCTraceOn(3604); -- PageFID = File Number, PagePID= Page Number DBCC Page('AdventureWorksDW2008R2', 1, 3458, 3) ;

  21. Do as I Say, Not as I Do Q&A

  22. Do as I Say, Not as I Do Thank You! • The PowerPoint slide-deck will be available on my website: • http://www.sqlsoldier.com

  23. Microsoft Certified Master

More Related