Forum Moderators: open
So I need to (1) identify which versions of software are running on the current host so that we can find another comparable server, (2) backup her database in a format that will allow it to be recreated on a different server, and (3) restore the database to the new server.
I've searched around a fair amount and found only partial answers to some questions, plus a few sites selling ASP scripts that claim to do these things for certain versions of software. So far I've figured out that her current server is running Windows 2000, .Net framework 1.1, and IIS 5.0. The database connection code in ASP is:
set connTemp=server.createobject("adodb.connection")
connString = "DRIVER={SQL Server};SERVER=...;UID=...;PWD=...;DATABASE=..."
connTemp.Open ConnString
I realize that this is a big request, but I'm hoping that someone can suggest how to go about identifying more specifically what software is running on the server (are there a bunch of tests one can do to rule in/out various versions of software?) and/or point me in the direction of an appropriate ASP script for backing up and restoring databases.
Many thanks,
akitinic
The question is what abilities you have to connect to the database. Do you have full control and can perform backups? Or do you just have an interface to it such as GoDaddy provides?
If you have full access (and have SQL Server client tools locally), then connect to it and perform a Backup and obtain those backup files, then perform an Attach Database on the new server.
It is likely one or both of the hosts (old and new) won't let you perform those necessary tasks. Perhaps they will do it for you for a nominal fee. Check with them.
If you only have an interface to the SQL Server database, you are in for a rough road that it appears you and your friend are not going to be able to perform due to lack of ASP related skills. But you need to programatically or manually get the database structure. Then you could write a script to open each table, grab all the data and write it out as xml, csv, tab delimited, whatever your choice is. Then you can use DTS in Sql Server to import that data, or again, write an ASP script to import it.
No matter what, I would suggest hiring someone to do this for you, unless you are into learning and have plenty of time to learn with.
At the moment, we only have an interface to the database.
I assume that a backup would be done through the GUI or through a Transact-SQL command such as "BACKUP DATABASE ... TO DISK = '...'" From your reply, I infer that there is no way to execute the latter T-SQL command via an ASP script. Is that correct? Or am I barking up the wrong tree altogether?
I'm into learning, but I don't have the time :).
Thanks again,
akitinic
You can execute any tsql statement through odbc/oledb.
The problem is that certain commands are restricted to specific server roles. One such command is BCP.
For BACKUP, books online says this:
BACKUP DATABASE and BACKUP LOG permissions default to members of the db_owner fixed database role, who can transfer permissions to other users, and to members of the db_backupoperator fixed database role.
MSSQL Books Online is available at the microsoft.com site.
However, even without divining the table structure, you can do select * which returns all columns. That will at least get you the data.
For poking around, I would write a asp based command console window as a text area form with a submit button that executes whatever is in the text area as tsql statements returning the results as plain text. This would act as a substitute for the tsql query analyser tool.
I would do this as a two stage process:
1. -- figure out what user tables are involved,(sp_tables)
-- grab the data using select *
2. approach current host about a backup, which could be as simple as doing a sp_detach <dbname> and shipping the files to you. this is preferred because at the new host, all you have to do is ship them the files and have them do a sp_attach <dbname> <filenames>
#2 is not impossible, unless the db is actually part of a proprietary system owned by the host. even then, the data is rightfully that of your friend. professionalism might trump personal feelings. especially if the negotiations are delegated to a third party such as yourself.
Good Luck.
It might cost some fees and some sweet-talking (begging?) on both ends, but it is a surefire way to move the database if it pans out and both hosts will perform these easy tasks, even though it is likely not part of their "standard" tech support.
