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.

 

Restoring a database to a lower edition – SQL Server 2008

>

SQL Server 2008 introduced a few Enterprise Edition-only features which changes the structure of a database when implemented. These features are:

• Partitioning
• Change data capture
• Transparent data encryption
• Data compression

Due to this, whenever you do a database restore or attach on an SQL Server 2008 instance with a lower edition, SQL Server would not allow you.

So what can you do when you have to move a database to a lower edition? Remove the offending features of course, there’s nothing else that you could do. But, how would you know which of the offending enterprise edition-only features are enabled on your database? Use the new dynamic management view sys.dm_db_persisted_sku_features. It’s a simple view which returns the feature you have enabled on your database along with its id. Hence, if the view shows any records, all you got to do now is disable the feature(s) before backing up.

Note: Of course partitioning was present in SQL Server 2005 as well, and even there you had to remove partitioning if you had to restore a database on a lower edition.

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