Darren Ferguson - Blog

09 October 2008 at 14:46

Update on SQL server 2005 backup/restore

Tags: Microsoft Windows , SQL , Technology Internet
Author: Darren Ferguson

This post is a response to a comment on my last blog post by Wouter.

Wouter correctly pointed out that my method for backing up the database involved taking the database temporarily off-line. By using the 'backup database' command in SQL server you can backup and restore and keep the database alive. I'd previously had problems with this, but finally sat down and worked through all of these issues so I now have a solution to share.

The backup database command is simple:

backup database umbraco to disk = 'D:\umbraco.bak';

Note: I discovered that if you run this command from within management studio express it doesn't run as you, so watch out for permissions issues.

The restore command is a little more involved.

restore database umbraco from disk = 'd:\umbraco.bak' 
with replace, 
move 'umbraco_Data' to 'd:\sqldata\umbraco.mdf', 
move 'umbraco_Log' to 'd:\sqldata\umbraco_log.ldf'

The 'with replace' part allows you to overwrite existing data files. The move statements allow you to place your data and log files in new locations if the SQL server install directory is different from the server where the backup was taken.

If you are unsure of the names of the original data files which are provided as arguments to 'move' then you can run the following SQL against you db backup.

restore filelistonly from disk = 'd:\umbraco.bak'

The value you are interested in is the Logical Name column.

I'm now at a state where this site gets daily automated backups using the method above combined with Windows scheduled tasks and SlikSVN to push my backups to a hosted SVN repository over at beanstalk.

Next up, an article on version controlling Umbraco during development!

Written by: Darren Ferguson