Scheduling Backups in SQL Server 2005/2008 Express Edition

Express editions of both SQL Server 2005 and 2008 do not include the SQL Agent service (even though it is there in an un-enablable disabled state in the SQL Server 2008 edition). This makes it a challenge when one wants to perform a scheduled backup of an Express edition database.

I had to perform such a task today, and here is the solution shared for your benefit. I must warn you however, that this is but a simple solution in order to satisfy one of our simple in-house tasks. You can of course expand upon it to do more wonderful things.

The steps I performed were:

1. Wrote a stored procedure which would create a backup.
2. Wrote T-SQL code to execute the stored procedure and put it in a .sql file
3. Wrote a batch file to call the .sql file.
4. Schedule the batch file using the Windows scheduler.

Take a look at the Code Sample.

If you would like to schedule much more complex maintenance tasks on SQL Server 2005/2008 Express edition databases, here is something cool: ExpressMaint.

 

>Date of Latest Backup/Restore

>

My colleague who is a developer usually comes up with requests for little bits and pieces of database related information. This time, it was a request for the latest date that a particular database had been restored from a backup. T’was nothing but 10 minutes of BOL and coding, but lot of people out there might find it quite useful on some occasions. So here we go, the first in the series of Not-So-Hip-Snippets-Yet-Useful-At-Times…

A list of database on your instance are stored in the sys.databases table in the master database, while the backup and restore information is stored in the msdb database on several tables:

• For backups: dbo.backupset, dbo.backupmediaset, dbo.backupmediafamily
• For restores: dbo.restorehistory, dbo.restorefile, dbo.restorefilegroup

The database ID is not maintained in the listed tables, hence we would need to join the the sys.databases table with the required tables using the database names. This is the code to get a list of the latest backups performed against your databases:

SELECT
d.[name] AS 'Database Name',
ISNULL(CONVERT(varchar, MAX(b.[backup_finish_date]), 109), 'No backups') AS 'Last Backed up Date/Time',
ISNULL(b.[user_name], 'N/A') AS 'User', ISNULL(b.[type], '-') AS 'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY
d.[name], b.[user_name], b.[type]

While this is the code to get a list of the latest restore operations performed against your databases:

SELECT
d.[name],
ISNULL(CONVERT(varchar, MAX(r.[restore_date]), 109), 'No restores') AS 'Last Restored up Date/Time',
ISNULL(r.[user_name], 'N/A') AS 'User',
ISNULL(r.[restore_type], '-') AS  'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory r ON d.[name] = r.[destination_database_name]
GROUP BY
d.[name], r.[user_name], r.[restore_type] 

You could stuff more details into this code, from the tables listed above to suit your requirements.