July 29, 2009 at 9:34 am
can any one send the script of hoe to know the path where the backup of all databases are stored when a back up job completes through maintainence pln (full, differential and log backups)
July 29, 2009 at 9:41 am
Open the maintainence plan and click on view T-SQL to see where it's storing the backup files..
July 29, 2009 at 9:44 am
i want to make a report of the paths where all the database backups stored .i want ascript to find where the backups are reside after finished
July 29, 2009 at 9:53 am
query the backupfile table in msdb database and see physical_name column if it gives the backup destination(not sure)... i cant test it right now coz of some issues....
July 29, 2009 at 1:44 pm
ps (7/29/2009)
query the backupfile table in msdb database and see physical_name column if it gives the backup destination(not sure)... i cant test it right now coz of some issues....
There is no such table by the name backupfile in msdb database.
Ram
Please query by joining these tables backupset and backupmediafamily and you can get the information that you are looking for.
July 29, 2009 at 7:52 pm
murthykalyani (7/29/2009)
There is no such table by the name backupfile in msdb database.
Ram[/QUOTE]
The table is present.
Please query by joining these tables backupset and backupmediafamily and you can get the information that you are looking for.
Yes, the backupmediafamily table does contain the path of the file where backup was taken.
July 30, 2009 at 12:45 am
Hi
Ram,
Use the following script .
Use msdb
SELECT backupset.backup_set_id, backupset.database_name, backupset.type, backupset.backup_finish_date, backupset.backup_size,
backupset.server_name, backupmediafamily.physical_device_name
FROM backupset INNER JOIN
backupfile ON backupset.backup_set_id = backupfile.backup_set_id INNER JOIN
backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE (backupset.database_name = 'Animal_1')
ORDER BY backupset.backup_finish_date DESC
-----Create a job and put this sql in the job step and use report back option in the Job scheduled it will write out put in text file on specified path.
Thanks and regards.
Ashwin VP
CSC India ...
July 30, 2009 at 1:47 am
------Use this Script
USE [msdb]
GO
/****** Object: Job [GET_Backup_report] Script Date: 07/30/2009 12:58:14 ******/
--------Owner Ashwin v p (ashwin4all1@gmail.com)
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 07/30/2009 12:58:15 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'GET_Backup_report',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'To get Backup Report',
@category_name=N'Database Maintenance',
@owner_login_name=N'SA', @job_id = @jobId OUTPUT
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
/****** Object: Step [Backup_Step] Script Date: 07/30/2009 12:58:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup_Step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SELECT backupset.backup_set_id, backupset.database_name, backupset.type, backupset.backup_finish_date, backupset.backup_size,
backupset.server_name, backupmediafamily.physical_device_name
FROM backupset INNER JOIN
backupfile ON backupset.backup_set_id = backupfile.backup_set_id INNER JOIN
backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE (backupset.database_name in (Select name from master.dbo.sysdatabases where Dbid >4))
ORDER BY Database_name,backupset.backup_finish_date DESC
',
@database_name=N'msdb',
@output_file_name=N'C:\Backup_details.txt',
@flags=0
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-------------Hi ram use this script.Actually the defualt path of out put script is C: drive ,if you want to change the out put path Just change the @output_file_name Parameter in this sql .
Thanks and regards
ashwin vp
Ashwin VP
CSC India ...
July 30, 2009 at 8:13 am
ps (7/29/2009)
murthykalyani (7/29/2009)
There is no such table by the name backupfile in msdb database.
Ram[/QUOTE]
The table is present.
Please query by joining these tables backupset and backupmediafamily and you can get the information that you are looking for.
Yes, the backupmediafamily table does contain the path of the file where backup was taken.
You are right it's there.
Sorry for that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply