Darren Ferguon - Weblog

Wednesday, October 01, 2008

Umbraco tip: Quick SQL server 2005 backup/restore

Filed under: Umbraco - by Darren Ferguson @ 12:44 PM

This only works if you are using a version of SQL server and have access to a command prompt on your server. I was looking for a way to source control DB backups and quickly copy my database between development, QA and production environments.

Start with the following in a file called backup.sql - obviously change the database name.

use master
   go
   sp_detach_db 'databasename'
   go

In the same folder create a file backup2.sql - again the database name and paths to your SQL server install are key.

use master
  go
  sp_attach_db 'databasename',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\databasename.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\databasename_log.ldf'
  go

Next create a file called backup.bat - In this file replace the target of the copy with a path of your choice. I am just copying the files to my SVN repository for a later commit.

sqlcmd -ic:backup.sql

copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\databasename*.*"
C:\repositories\df_com\trunk\db

sqlcmd -ic:backup2.sql

For sqlcmd to execute, you will need to have the SQL server bin directory in your PATH. You may also want to stop IIS prior to the backup and start it again after using net stop|start w3svc.

Now you have your SQL server data file detached and copied to a new location. To restore simply create restore.bat.

sqlcmd -ic:backup.sql

copy C:\repositories\df_com\trunk\db\databasename*.* 
"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"

sqlcmd -ic:backup2.sql

Exactly the same process but copying the data files in the opposite direction.

So why is this useful? Well, so long as your database names and SQL server paths are the same across servers you can simply copy your data files between servers and restore the database in any environment.

This all came about as part of a larger project to put umbraco under source control which I'll write a better article on at some point later on. .