Programmatically detach SQL Server database to copy mdf file

Question

I have a small SQL Server database that I need to copy on command -- I need to be able to take the mfd and ldf files at any given moment, copy them, zip them, and make them available to an end user.

Right now this is possible by manually:

1) Logging onto the SQL server via Remote Desktop

2) Detaching the database via SQL Management Studio. I have to fiddle around with a combination of setting the database to single_user and/or restarting the service so I can get it to detach since the app server is normally logged into it.

3) While detached I go through the file system and copy the mdf and ldf files.

4) I re-attach the database via SQL Management Studio

5) I zip the copied files, and I move them to an FTP server so the people who need them can get them.

It's a horrible, inefficient process. It's not just a matter of needing the schema, but rather a need for people to work with snapshots of real, production data on their own local machines for the purpose of destructive experimentation. Luckily the zipped database is very small -- maybe 30 megs with the log.

So ideally, I'd like to create a page in the ASP .NET web application that has a button the user can press to initiate the packaging of the current database into a zip file, and then I'd just provide the link to the file download.

Answer 1

Why not make a ordinary backup (easy to do with sqlcommand) and add a feature for the users to easy restore that backupfile with a click on a button?

• You can backup the database with sql-commands
• You can shell out and zip the backupfile with sql-commands
• You can also shell out and ftp the backupfile automagically to an webserver if you want.

What are the end users using to consume your db? A winform-program? Then it easy done to do everything with a button click for the user.

Here are some example code for that:

Declare @CustomerID int
declare @FileName nvarchar(40)
declare @ZipFileName nvarchar(40)
declare @ZipComand nvarchar(255)


set @CustomerID=20 --Get from database instead in real life application
SET @FileName='c:\backups\myback'+ cast(@customerID as nvarchar(10))+'.bak'
SET @ZipFileName='c:\backups\myback'+ cast(@customerID as nvarchar(10))+'.zip'

--Backup database northwind
backup database northwind to DISK=@FileName

--Zip the file, I got a commanddriven zip.exe from the net somewhere.
set @ZipComand= 'zip.exe -r '+@ZipFileName+' '+@FileName
EXEC xp_cmdshell @zipcomand,NO_output

--Execute the batfile that ftp:s the file to the server
exec xp_cmdshell 'c:\movetoftp.bat',no_output

--Done!

You have to have a movetoftp.bat that contains this (change ftp-server to your):
ftp -s:ftpcommands.txt ftp.myftp.net

And you have to have a ftpcommands.txt that contains this (You can have this file created dnamically with just the right zip-file by sqlcommands too, but I let you do that yourself):

ftpusername
ftppassword
binary
prompt n
mput c:\backups\*.zip
quit

Answer 2

Look at the dialogues you use in SQL Management Studio, near the top of each is a button which will generate a scrip to perform the action. This is a quick way to discover how to do this in SQL which can be executed from a database connection.

E.g. to detach database db1:

EXEC master.dbo.sp_detach_db @dbname = N'db1'