Solved

backups files and documentation

Posted on 2007-04-03
6
379 Views
Last Modified: 2012-08-14
is there a way to get the list of the log file location from all the databases?

I am using sp_helpdb, but I have several database was wondering if there was a query I can use.  Can I also query for the location of .bak files?  thanks.

Also, I am trying to document a current backup process for a new position that I took, any recommendations/best practices for the documentation of a backup process?

Thanks!!
0
Comment
Question by:yanci1179
  • 4
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18844439
CREATE TABLE #temp(NAME VARCHAR(100), FileID INT, FileName VARCHAR(1000), fileGroup VARCHAR(100), size VARCHAR(100), MaxSize VARCHAR(1000),  growth VARCHAR(100), Usage VARCHAR(1000))
INSERT INTO #temp
EXEC sp_MSForEachDB "USE ?;exec sp_helpFile "

SELECT NAME,FileName FROM #temp WHERE Usage = 'Log Only'
0
 
LVL 9

Expert Comment

by:nito8300
ID: 18844479
to view backup info try table backupfile in msdb
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18844540
-- view backup file details
use msdb
select distinct f.device_type, f.physical_device_name, f.logical_device_name, b.database_name
from backupmediafamily f, backupset b  
where b.backup_finish_date = (select MAX(backup_finish_date) from backupmediafamily
                               INNER JOIN backupset ON backupmediafamily.media_set_id=backupset.media_set_id
                               where (backupmediafamily.device_type=5 or backupmediafamily.device_type=105))
and b.media_set_id = f.media_set_id
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18844561
sorry, try this script to view the backup details

use msdb
select  b.database_name,f.device_type, f.physical_device_name, f.logical_device_name, b.backup_finish_date
from backupmediafamily f, backupset b  
WHERE b.media_set_id = f.media_set_id
0
 

Author Comment

by:yanci1179
ID: 18844976
thanks aneeshattingal!!

Do you know how i can find out how the server and disks(hardware) are configured.  I would like to know if RAID is running and what kind.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18855598
Hardware Configurations, i am not sure about it. usually that will be part of OS and sql server just take assistance from it
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now