setting up a backup for a database

Not being a SQL admin myself, but where exactly in SQL express would you see what the admin has configured in terms of a backup job for a specific database? Can you direct me? Is there a specific area of the admin consoles where you can see what backups they have configured on which databases? this is sql express 2005.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You can run this query to see backup history:

	CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
	CAST(DATEDIFF(second, s.backup_start_date,
	s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
	CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
	CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
	CASE s.[type]
	WHEN 'D' THEN 'Full'
	WHEN 'I' THEN 'Differential'
	WHEN 'L' THEN 'Transaction Log'
	END AS BackupType,
FROM msdb.dbo.backupset AS s
INNER JOIN msdb.dbo.backupmediafamily AS m
	ON s.media_set_id = m.media_set_id
ORDER BY s.backup_start_date DESC

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Haresh NikumbhSr. Tech leadCommented:
to check backup history for SQL database you need to SQL Server management studio installed on the server.. verify if its installed or not

or you can download from here

Microsoft SQL Server Management Studio Express

refer below link to get backup history for the database
pma111Author Commented:
Thats a script for backup history but I wondered if somewhere in the management GUI you could see what backup jobs the admin has scheduled, i.e. where you actually set the backup parameters.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Brian CroweDatabase AdministratorCommented:
Express doesn't have a SQL Server Agent service which is where you would look in a standard instance.  Check windows task scheduler for a .bat or other scripted task that might be performing the backup.  The script I provided should help you identify whether one's happening at all and when.
pma111Author Commented:
Thanks BriCrowe
pma111Author Commented:
Does your query work on all versions of MSSQL including express or enterprise?
Brian CroweDatabase AdministratorCommented:
I can verify that it works on a non-express version.  I recently upgraded our last express instance to standard so I don't have one handy to test against.  As long as the instance has an msdb database, and I believe all versions do, then you shouldn't have a problem with it.
pma111Author Commented:
Great stuff..
ste5anSenior DeveloperCommented:
BriCrow is right. There is no built-in scheduled process in SQL Server Express. But the query from him works also on SQL Server Express. Normally a backup for SQL Server Express is scheduled by using the Windows Task Scheduler, so it would look there for backup tasks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.