Verifying a Backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Verifying a Backup

Author

Topic

cowzz
Starting Member

4 Posts

Posted - 2006-06-27 : 10:25:15

Hey Folks...
Very new to SQL Server. I've inherited numorous SQL Servers and wanted to know how I can tell if a backup is scheduled and how I can tell if it completed successfully? Thanks in advance....

Mike D.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 10:26:51

There are Maintenance plans for automatic backups. There can also be scheduled jobs to take care of that. Jobs have history log.

Peter Larsson
Helsingborg, Sweden

cowzz
Starting Member

4 Posts

Posted - 2006-06-27 : 10:47:30

Thanks Peter, but how do I view an active maintenance plan, the scheduled jobs and the history log? I'm poking around in EM and clicked on Database Maintenance Planner, but it's a wizard that I'm assuming wants me to create a plan.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-06-27 : 12:03:29

In SQL 2000 go under Management - Database maintanace plans

For the scheduled jobs go under Management - SQL Server Agent - Jobs

Jim
Users <> Logic

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 12:38:23

>> how I can tell if it completed successfully?
You can tell if it thinks it has completed successfully but the only way to tell if you have a good backup is to restore it.

Don't rely on the integrity of any backups without a restore. On critical systems I do a test restore of every full backup.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-06-27 : 14:34:19

SQL stores a "log" of all backups made, so that may be useful to know what has been backed up, and when, but restoring to a separate machine and doing a DBCC CHECKDB is the only sure way to check that the backup is failsafe.


SELECT TOP 100
	database_name,
	backup_set_id,
	backup_start_date,
	backup_finish_date,
	type,
	name,
	user_name,
	first_lsn,
	last_lsn,
	database_backup_lsn
--	, * 
FROM	msdb.dbo.backupset
WHERE	    database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
-- 	AND type='D' 
ORDER BY backup_start_date DESC

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources