Programmatically separate 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 take the mfd and ldf files at any time, copy them, pin them and make them available to the end user.

Now it is possible manually:

1) Logging on to SQL Server via Remote Desktop

2) Detaching a database through SQL Management Studio. I have to bother with a combination of setting up the database on single_user and / or restarting the service so that I can separate it, since the application server usually writes to it.

3) During detachment, I scan the file system and copy the mdf and ldf files.

4) I reconnect the database through SQL Management Studio

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

This is a terrible, inefficient process. This is not just a matter of the need for a circuit, but the need for people to work with snapshots of real production data on their local machines for the purpose of destructive experimentation. Fortunately, the zipped database is very small - maybe 30 megabytes with the log.

Ideally, I would like to create a page in an ASP.NET web application with a button that the user can click to start packing the current database into a zip file, and then I just provided a link to the file.

Answer 1

Why not make a normal backup (just do it with sqlcommand) and add a feature for users to easily restore this backup file with the click of a button?

• You can backup the database using sql commands
• You can upload and archive the backup file using sql commands
• You can also upload the ftp file and the ftp file automatically to the web server if you want.

What are the end users consuming your db? Winform program? Then it’s easy to do everything by clicking the mouse button for the user.

Here is a sample code for this:

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 should have a file movetoftp.bat that contains this (change the ftp server to yours):
ftp -s: ftpcommands.txt ftp.myftp.net

And you should have ftpcommands.txt that contains this (you can create this file using a zip file only using sqlcommands, but I did it myself):

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

Answer 2

Look at the dialogs that you use in SQL Management Studio, next to each of them there is a button that will create a script to perform the action. This is a quick way to learn how to do this in SQL, which can be done from a database connection.

eg. To detach the db1 database:

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

Answer 3

Easy — Check out the SQL Management SMOs that ship with SQL Server — the cool C # / VB.NET classes and methods to do all this.

See: SMO - Manage Your SQL Server!

or

Backing up and restoring SQL Server 2005 databases using C # and .NET 2.0

Mark

Answer 4

Personally, I will back up the database and zip them and send them to users. Perhaps you could write a small recovery script.

The reason for disconnecting the database makes it inaccessible to others.

Answer 5

I would use the console version of SQL Dumper and compress the sql dump.

IMHO it is always better to have a text copy instead of a binary file. If something goes wrong, at least you can rewrite your data manually because you can read it.

Answer 6

The first connection to SQL Server without binding any DB file and without using a database name.

 ConnectionString = @"Data Source=XXX;Integrated Security=True;Connect Timeout=30"; 

Note: XXX =. OR. \ SQLEXPRESS OR. \ MSSQLSERVER OR (local) \ SQLEXPRESS OR (localdb) \ v11.0 & ...

and then in the Query section, detach the database file.

 "ALTER DATABASE [your DB] SET OFFLINE WITH ROLLBACK IMMEDIATE \n\r exec sp_detach_db @dbname = [your DB]"; 

Ok

My example:

 sql_connect1.ConnectionString = @"Data Source=.\sqlexpress;Integrated Security=True;Connect Timeout=30"; sql_command.CommandText = "ALTER DATABASE [IRAN] SET OFFLINE WITH ROLLBACK IMMEDIATE \n\r exec sp_detach_db @dbname = [IRAN]"; sql_command.Connection = sql_connect1; sql_connect1.Open(); sql_command.ExecuteNonQuery(); sql_connect1.Close();