Solved

backups files and documentation

Posted on 2007-04-03
6
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

635 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