jeudi 19 avril 2012 19:14
Hi all - I'd like to find out on the previous setup that my DB admin has done automating our database backups in MS SQL Management studio. I've logged on to the console as SA and didnt find this automation task process. I've also looked in the Task Scheduler and no luck there. Please advise. Cheers - DB
Toutes les réponses
jeudi 19 avril 2012 22:24
I would start by looking to see when the last backup for each database occurred. Can you see any Maintenance Plans in SSMS?
This will show you the last full database backups as it is using backupset.type=D. You can also look for differential and log backups via I and L.
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' GROUP BY msdb.dbo.backupset.database_name ORDER BY msdb.dbo.backupset.database_name
vendredi 20 avril 2012 03:41
Check out my script to get all the info related to the backups
vendredi 20 avril 2012 03:47It said I had last backup on 04-19-2012 for all of them. How can I view the task and modify or delete/stop the backup from re-occurring?
Cheers - DB
vendredi 20 avril 2012 15:27I just checked and backups are doing data dumps.BAK in each DB. How can I stop this?
vendredi 20 avril 2012 16:05
If you want to stop the current backup process you should will the session. Use sp_who2 to identify which session_id (spid) is the one that is executing the BACKUP and kill it.
Looks like you have a sql job that is scheduled to do automatic backups. If you cannot identify the process, take a look to below like and use it to find the strings "BACKUP" or "DUMPS.BAK"
vendredi 20 avril 2012 16:51Yes it seems it is a scheduled task in SQL that runs every night at 12midnight.
vendredi 20 avril 2012 17:41Im getting this error Could not find stored procedure 'sp_Find' when running the query.
vendredi 20 avril 2012 18:18
samedi 21 avril 2012 06:59
Yes I did download but got the error when running the query: Could not find stored procedure 'sp_Find'