Making a backup of an SQL Server database...

Question

I'm working on software that has been developed to support two SQL Server versions. We have one version running on SQL Server 2000 and another on 2005. Both are installed on customer sites (two different customers) and I need to be able to get a backup from both servers to do tests for upgrades. The back-ups will then be used to do a final test before upgrades are sent to both customers. By testing on their actual data, the test result would be the most reliable.

But the problem is that both customers don't give us access to any SQL tools to run on those servers. All I get is an SQL Connection string to use with C# as part of an ASP.NET application. So I need to do something smart through c#/SQL statements through this connection. I also don't have access rights to the file system on the server so I can't even make a backup in some file on the server, then download it. So, other alternatives?

Answer 1

Assuming you are sql admin, you have (or may enable) access to xp_cmdshell. So after you take the backup (and store it in sql data directory, to which Sql Server service account must have access), you can invoke xp_cmdshell to do whatever you want with these backup files (again, xp_cmdshell will run under security context of Sql Server engine, so it will have access to the data directory).

Answer 2

This SMO or SQL DMO stuff sounds promising. There is a section on performing a backup programmatically through a connection. Note the requirements that SMO be isntalled, and I'm sure there will be a dependency on how the SQL Server User's permissions are configured that you use to connect with:

http://msdn.microsoft.com/en-us/magazine/cc163409.aspx

Answer 3

either do what paul says and get them to give you remote access to their network and server, or have them get their people to send you a backup of the database. i wouldn't waste the time trying to develop a workaround. talk to your boss about why it's important to get these back ups and see if they can provide some leverage.

Answer 4

Perhaps your customer can do the backup for you?

If you have a requirement to handle it over a remote connection, then your first challenge will be ensuring consistent DB state. If you're running SQL Enterprise, you can do that by creating a DB snapshot.

You can use Server Management Objects (SMO) to programmatically do anything you can do from SSMS, including making scripts to create the DB objects, running a traditional backup, etc.

If you don't need the DB structure, you could write an SSIS package to scoop up all of the remote data and dump it into a local DB. That would probably be faster than trying to code it yourself.