Use ASP to create a complete Database Backup

[Daniel]

I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of the
usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were asked
for a backup and they refused to provide one. So now I am trying to
find a way to do it myself.


I have been trying to use the following but it doesn't seem to produce
any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID &";Password="&
DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need to
do to be able to save it to my drive or e-mail it or so method I can
actually get a copy?


Also worth explaining. The website files are hosted on one server
while the db is on another. So the Backup file is not on the local
computer with the asp files... I tried to add an e-mail routine but
the server running the asp file cannot locate the file (as it is
actually on another machine, I assume is the reason).


Please keep in mind I know next to nothing about both SQL Server and
ASP. I truly am just trying to help someone who is completely
technologically ignorant out of a mess.


Also, I am using the credentials given by the Host. Is there a
simple way to find out if they have the required permissions to
perform a backup? I was simply wondering if it wasn't a permission
issue.


I have also tried to connect my server (SQL Server 2008) to the remote
server using the supplied credentials, but it doesn't work and returns
an error #53...?


Thank you very much for your help!!!


QB

 

[Bob Barrows]

I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of
the usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were asked
for a backup and they refused to provide one. So now I am trying to
find a way to do it myself.


I have been trying to use the following but it doesn't seem to produce
any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID &";Password="&
DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need to
do to be able to save it to my drive or e-mail it or so method I can
actually get a copy?

Are you in the same domain as the SQL Server? It soesn't sound like it.

If the host knows how to do security (and it sounds like he does), you
are not likely going to be able to get a copy of this database. All of
the techniques I would typically use (DTS/SSIS, xp_cmdshell,
sp_sendmail, etc.)are easily locked down by a system administrator.

Frankly, going behind the host's back sounds more than a little shady to
me, and I am really hesitant to provide more information to you.

 

[Daniel]

There is nothing shady going on!

The website owner wishes to switch and the host is being as uncooperative as
can be. i am simply trying to resolve this issue quickly before things
degerate further.

I have the permission of the website owner and the credentials to access the
website (ftp) and the sql server given to the client by the host.

This is not a request to become a hacker. It is a ligitemate problem to be
resolved.

QB

I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of
the usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were asked
for a backup and they refused to provide one. So now I am trying to
find a way to do it myself.


I have been trying to use the following but it doesn't seem to produce
any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID &";Password="&
DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need to
do to be able to save it to my drive or e-mail it or so method I can
actually get a copy?

Are you in the same domain as the SQL Server? It soesn't sound like it.

If the host knows how to do security (and it sounds like he does), you
are not likely going to be able to get a copy of this database. All of
the techniques I would typically use (DTS/SSIS, xp_cmdshell,
sp_sendmail, etc.)are easily locked down by a system administrator.

Frankly, going behind the host's back sounds more than a little shady to
me, and I am really hesitant to provide more information to you.

 

[Daniel]

Sounds fine to me. All I want are the tables and data. How can I do that?

I think what you need to do is an "export", not a backup, to retrieve it
from the server.

I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of the
usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were asked
for a backup and they refused to provide one. So now I am trying to
find a way to do it myself.


I have been trying to use the following but it doesn't seem to produce
any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID &";Password="&
DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need to
do to be able to save it to my drive or e-mail it or so method I can
actually get a copy?


Also worth explaining. The website files are hosted on one server
while the db is on another. So the Backup file is not on the local
computer with the asp files... I tried to add an e-mail routine but
the server running the asp file cannot locate the file (as it is
actually on another machine, I assume is the reason).


Please keep in mind I know next to nothing about both SQL Server and
ASP. I truly am just trying to help someone who is completely
technologically ignorant out of a mess.


Also, I am using the credentials given by the Host. Is there a
simple way to find out if they have the required permissions to
perform a backup? I was simply wondering if it wasn't a permission
issue.


I have also tried to connect my server (SQL Server 2008) to the remote
server using the supplied credentials, but it doesn't work and returns
an error #53...?


Thank you very much for your help!!!


QB

 

[Bob Barrows]

There is nothing shady going on!

Forgive me, but these are just words on my screen. Why should I be
convinced by them? After all, if you really were a potential hacker,
would you really come out and reply "dammit, you're too smart for me, I
guess I'll give up on the idea of hacking ... "

The website owner wishes to switch and the host is being as
uncooperative as can be. i am simply trying to resolve this issue
quickly before things degerate further.

I have the permission of the website owner and the credentials to
access the website (ftp) and the sql server given to the client by
the host.

That's the issue - the credentials for the sql server are likely to have
limited rights, so atempting things like sp_sendmail or xp_cmdshell are
not likely to be successful if the host has things locked down the way
he should.

This is not a request to become a hacker. It is a ligitemate problem
to be resolved.

QB

Bottom line, even if this is a legitimate problem, I still see no way to
do it if the host wants to prevent you from doing it.
The best you can do is query each of the tables in the database and
write the results to response in an html table. In fact you can set the
contenttype to excel and see the results in an excel spreadsheet that
you can save locally. There are many examples of doing this in this
newsgroup as well as in .asp.db so google should quickly get you going.

Oh! It just occurred to me that there are ASP-based SQL Server
management tools out there that might be useful for you. I think one of
them is called MyLittleSQL or something like that. Be right back, I'm
going to do a quick search ...

No, it's MyLitleAdmin I was thinking of. This page has a list of this
and other tools, some of which are discontinued:
http://sqlserver2000.databases.aspfaq.com/how-do-i-manage-sql-server/msde.html

 

[Bob Barrows]

just google:

sql server import / export database

you can also google:

sql server import / export DTS

I do not believe this is a viable solution in his situation. He would
need to be able to directly access the SQL Server from his client
machine (or vice versa) to make that work. From what he's been saying,
he does not have such access, which is why he was attempting to use ASP
to do it.

 

[Daniel]

Bob,

Is there a way for me, using ASP, to query the db to find what permission
the logon credentials I have been supplied with actually have. This way I
can then affirm to my colleague and his host that the supplied login info
does not permit the backing up of the db.

Thank you for all your help,

QB

 

[Bob Barrows]

I'm pretty sure that your account does have rights to perform a backup.
You would have gotten an error message if it didn't (unless you used on
error resume next to suppress error messages ...).

What it likely does not have rights for is to go out to the operating
system and work with files in the file system. Neither is it likely to
be able to use the smtp server to send email. Or ftp for that matter.
You can try running xp_cmdshell just to see if the host didn't lock that
down ...

 

[p byers]

Why not take a copy using ftp ??

There is nothing shady going on!

The website owner wishes to switch and the host is being as uncooperative as
can be. i am simply trying to resolve this issue quickly before things
degerate further.

I have the permission of the website owner and the credentials to access the
website (ftp) and the sql server given to the client by the host.

This is not a request to become a hacker. It is a ligitemate problem to be
resolved.

QB

I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of
the usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were asked
for a backup and they refused to provide one. So now I am trying to
find a way to do it myself.


I have been trying to use the following but it doesn't seem to produce
any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID &";Password="&
DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need to
do to be able to save it to my drive or e-mail it or so method I can
actually get a copy?

Are you in the same domain as the SQL Server? It soesn't sound like it.

If the host knows how to do security (and it sounds like he does), you
are not likely going to be able to get a copy of this database. All of
the techniques I would typically use (DTS/SSIS, xp_cmdshell,
sp_sendmail, etc.)are easily locked down by a system administrator.

Frankly, going behind the host's back sounds more than a little shady to
me, and I am really hesitant to provide more information to you.

 

[Bob Barrows]

Yeah, I thought of this, but the database is not on the web server. The
database server might not have an ftp server running. And even if it did,
the host is being uncooperative here so it is likely that he has locked it
down.

Why not take a copy using ftp ??

There is nothing shady going on!

The website owner wishes to switch and the host is being as
uncooperative as can be. i am simply trying to resolve this issue
quickly before things degerate further.

I have the permission of the website owner and the credentials to
access the website (ftp) and the sql server given to the client by
the host.

This is not a request to become a hacker. It is a ligitemate
problem to be resolved.

QB

Daniel wrote:
I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of
the usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were
asked
for a backup and they refused to provide one. So now I am trying
to find a way to do it myself.


I have been trying to use the following but it doesn't seem to
produce any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID
&";Password="& DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD,
STATS = 10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need
to
do to be able to save it to my drive or e-mail it or so method I
can actually get a copy?

Are you in the same domain as the SQL Server? It soesn't sound like
it.

If the host knows how to do security (and it sounds like he does),
you are not likely going to be able to get a copy of this database.
All of the techniques I would typically use (DTS/SSIS, xp_cmdshell,
sp_sendmail, etc.)are easily locked down by a system administrator.

Frankly, going behind the host's back sounds more than a little
shady to me, and I am really hesitant to provide more information
to you. --
HTH,
Bob Barrows

 

[Daniel]

I have no FTP account info for the db server, only the webserver.

I have advised my colleague to speak again with his host and ask again for a
backup to be supplied. Otherwise, I informed him he had 2 choices: start
over, try and recreate it (requires some work, but can be done since I do
have all the website files and I can extract the bulk of the textual data
from his site - just need to figure out the db design) or call his lawyer
(costly). The ball is in his court now.

I can't thnak you both enough for your advice!

QB

Yeah, I thought of this, but the database is not on the web server. The
database server might not have an ftp server running. And even if it did,
the host is being uncooperative here so it is likely that he has locked it
down.

Why not take a copy using ftp ??

There is nothing shady going on!

The website owner wishes to switch and the host is being as
uncooperative as can be. i am simply trying to resolve this issue
quickly before things degerate further.

I have the permission of the website owner and the credentials to
access the website (ftp) and the sql server given to the client by
the host.

This is not a request to become a hacker. It is a ligitemate
problem to be resolved.

QB

Daniel wrote:
I am trying to help a colleague to backup his website to migrate it
elsewhere. As such, I am trying to figure out how I can using ASP
perform a backup to my hard drive of the entire db (it is actually
very small). The host provides no admin console, so I have none of
the usual tools to create a backup. Thus the question.

The host is being more than difficult to work with. They were
asked
for a backup and they refused to provide one. So now I am trying
to find a way to do it myself.


I have been trying to use the following but it doesn't seem to
produce any results?


<%
SQL_Server_Name = "servername"
DB_Name = "dbname"
DB_User_ID = "username"
DB_Password = "password"
strcon = "Provider=SQLOLEDB;Data Source="& SQL_Server_Name
&";Initial Catalog=" & DB_Name &";User ID="& DB_User_ID
&";Password="& DB_Password&";charset=utf-8"
set con = Server.CreateObject("ADODB.Connection")
con.Open strCon
sql="BACKUP DATABASE ["& DB_Name &"] TO DISK = N'c:/db.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup', SKIP, NOREWIND, NOUNLOAD,
STATS = 10"
con.Execute(sql)
%>


Since I am running the script on the server, I suppose it is being
saved to the server rather than on my hard drive. What do I need
to
do to be able to save it to my drive or e-mail it or so method I
can actually get a copy?

Are you in the same domain as the SQL Server? It soesn't sound like
it.

If the host knows how to do security (and it sounds like he does),
you are not likely going to be able to get a copy of this database.
All of the techniques I would typically use (DTS/SSIS, xp_cmdshell,
sp_sendmail, etc.)are easily locked down by a system administrator.

Frankly, going behind the host's back sounds more than a little
shady to me, and I am really hesitant to provide more information
to you. --
HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[JM]

I have no FTP account info for the db server, only the webserver.

I have advised my colleague to speak again with his host and ask again for
a backup to be supplied. Otherwise, I informed him he had 2 choices:
start over, try and recreate it (requires some work, but can be done since
I do have all the website files and I can extract the bulk of the textual
data from his site - just need to figure out the db design) or call his
lawyer (costly).

An hour or two with a lawyer would be my choice. It would be a lot cheaper
than re-designing and populating the DB. Sounds like the hosting company is
trying to hold your data hostage in an attempt to keep you from switching.

Depending on the host and the state/country where they're located, your
colleague might want to consider contacting his state attorney general's
office
(if in the US). They always love hearing about big, bad corporations beating
up on the little guy..

Exactly why telcos in the US can no longer hold your number hostage.

John