Guide: Detaching Database for a 'quick restore' on SQL Server 7+

In my DBA roll at Journeys Insurance, I always found that the backup and restore process of SQLServer just takes far too long, especially when you have to make a 'quick restore' of certan data. The solution to this is to detach the database and then re-attach it when you need it under the same or different name. At Journeys this was often needed to fix problems with the data being corrupted through the Delphi front end, or users being idiots. Anyway, I thought I'd create a DTS Package that would run and take a copy of the databases over night after the normal SQLServer backups had run. It meant restoration from them was instant.

Here's how you can do it.

To detach the database run

-- Detach Database (explained below)

exec sp_detach_db 'databasename'

Then run the follow commands from the command prompt. I recommend creating a batch file

xcopy /Y/C [full-path-to-database_file].MDF [destination]

xcopy /Y/C [full-path-to-log^file].LDF [destination]

Then reattach the database.

Below you will find

-- reattach old database

EXEC sp_attach_db

@dbname=N'MyDatabase',

@filename1 = N'c:\example\data\datbasefile.MDF',

@filename2 = N'd:\example\data\databaselog.LDF'

If you create this as a DTS package, or even if you run it manually and there are connections to the database you will need to kill those.

Check back later for the FULL instructions on setting this up as a DTS package and the scripts that kill all the connections to that database

---