• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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.
3 Solutions
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

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.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now