• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

VBS or ASP to restore database from backup-file by schedule ?

Hello,

I have a database at the client (SQL Server 2000) from which I have to put a duplicate online every night on our webserver. This is the scenario (all works well, but I need help with the final step ...)

* client sql-server: complete backup every night at 23 o'clock to 'database.bak' (vbs-script with windows scheduler)
* ftp of 'database.bak' to the webserver at 23.30 (bat-script with windows scheduler)
* webserver: delete the existing database (if necessary), restore 'database.bak' to web-sql-server, recreate user 'websqluser' at midnight

For the last step I need some kind of vbs or asp script I can call with windows scheduler. Now we have to do it by hand, but that's really no solution :-)

Any ideas ?

Thanks a lot
0
Amigo_fd
Asked:
Amigo_fd
  • 12
  • 7
  • 5
2 Solutions
 
muzzy2003Commented:
Why don't you write a SQL script and schedule it using SQL Server Agent as a job? Much neater solution. The script would be something like this:

RESTORE DATABASE <database>
FROM DISK = <filename>
<other options such as WITH MOVE if necessary>

then whatever you need to grant the user database access and necessary permissions.
0
 
Amigo_fdAuthor Commented:
have you any examples of this kind of script ?
0
 
muzzy2003Commented:
Exactly what tasks are you performing at the moment? Is everything through the Enterprise Manager GUI? If so, tell me what you are doing (in some detail) and I will give you the equivalent as a SQL script.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Amigo_fdAuthor Commented:
The problem is I have total access to my webserver, but the web-sql-server is on an other machine in the network to which I have only access through dns or dns-less connection. That's the reason why I was thinking of doing it with a vbs-script ...

I could ask it to the system administrator of the sql-server, but it would be much easier if I could do it from my own machine ...
0
 
Amigo_fdAuthor Commented:
btw, the web-sql-database is only used for reading, so no data is stored in there. That's the reason why the client-database can be written on top of it
0
 
muzzy2003Commented:
It's not much good for reading if it doesn't have any data in it. ;) (Don't worry, I know what you mean.)

Well, there's nothing to stop you running the RESTORE DATABASE script over such a connection, and then handling it from a VB script file that is run using wscript and the Windows scheduler if that's what you would rather do. Let me know the details and I'll help you with the script.
0
 
Amigo_fdAuthor Commented:
ok, let's do it like this with a vbs (this is a very rough version of what it could look like ...):

Filename = "//webserver/database/backup.bak"
SQLserver = "123.456.789.123"
SQLdatabase = "databasename"
SQLlogin = "login"
SQLPassword = "password"

SQLWeblogin = "weblogin"
SQLWebpassword = "webpassword"

'tell the website the database is offline
'change the text of the file c:/website/database/check.txt to false

'import the backup
RESTORE DATABASE " & SQLdatabase & " FROM DISK = " & Filename & ""

'recreate the user
CREATE USER FOR DATABASE " & SQLdatabase & ""
USER = " & SQLWeblogin & ""
PASSWORD = " & SQLWebpassword & ""

'tell the website the database is online again
'change the text of the file c:/website/database/check.txt to false
0
 
muzzy2003Commented:
OK. Filename will need to be the local path on the web server as that is where the SQL script will run, and it needs \ not /. The SQL script could be as simple as:

SQLScript= "RESTORE DATABASE " & SQLDatabase & " FROM DISK = '" & FileName & "'"
... execute SQL script using an OLEDB or ODBC connection, your choice ...
SQLScript = "EXEC sp_grantdbaccess '" & SQLlogin & "'"
... execute SQL script using an OLEDB or ODBC connection, your choice ...

but you will probably need to also grant permissions appropriately in the database.
0
 
Amigo_fdAuthor Commented:
don't you have to tell  sp_grantdbaccess  which database to give access to ?
0
 
muzzy2003Commented:
No, you run it in the database in question.
0
 
Amigo_fdAuthor Commented:
what do you think of this ? Could this work ?

<%
Dim BakFilename, StatusFilename
Dim SQLServer, SQLDatabase, SQLLogin, SQLPassword
Dim SQLWeblogin, SQLWebpassword
Dim MyFileObj, MyOutStream, DoSql

BakFilename = "c:\database\backup.bak"
StatusFilename = "c:\database\status.txt"
SQLServer = "123.456.789.123"
SQLDatabaseName = "databasename"
SQLLogin = "login"
SQLPassword = "password"

'tell the website the database is offline
Set MyFileObj=CreateObject("Scripting.FileSystemObject")
Set MyOutStream=MyFileObj.OpenTextFile(StatusFilename, 2, TRUE)
MyOutStream.Write("false")
MyOutStream.Close

'import the backup
Set DoSql = Server.CreateObject("ADODB.Command")
DoSql.ActiveConnection = "dsn=" & SQLDatabaseName & ";uid=" & SQLLogin & ";pwd=" & SQLPassword & ";"
DoSql.CommandText = "RESTORE DATABASE " & SQLDatabaseName & " FROM DISK = '" & BakFilename & "'"
DoSql.Execute
DoSql.ActiveConnection.Close
      
'recreate the user
Set DoSql = Server.CreateObject("ADODB.Command")
DoSql.ActiveConnection = "dsn=" & SQLDatabaseName & ";uid=" & SQLLogin & ";pwd=" & SQLPassword & ";"
DoSql.CommandText = SQLScript = "EXEC sp_grantdbaccess '" & SQLLogin & "'"
DoSql.Execute
DoSql.ActiveConnection.Close

'tell the website the database is online again
Set MyFileObj=CreateObject("Scripting.FileSystemObject")
Set MyOutStream=MyFileObj.OpenTextFile(StatusFilename, 2, TRUE)
MyOutStream.Write("true")
MyOutStream.Close
%>
0
 
muzzy2003Commented:
You might need two different DSNs - you'll have a job restoring the database from within the same database, so one pointing to master or somewhere else might be an idea to run the RESTORE DATABASE command. Apart from that, it's getting there. Have you a test system you can try it on?
0
 
Amigo_fdAuthor Commented:
nope, no test available, so I have to make this code as good as possible before I let it run the first time ... :-(
0
 
muzzy2003Commented:
Right, so you'd better tell us the exact steps you take when recreating the logon at the moment, and any details (like changing file locations) related to the restore ...
0
 
arbertCommented:
So why do you take the database offline before you back it up?  SQL backups are realtime and don't lock users while executing....

Also, have you thought about using SQLDMO for the backup/restore?  Just another option....
0
 
Amigo_fdAuthor Commented:
can you give me any more info about sqldmo ?

I don't have the time to test it right now, probably in a few days ....
0
 
arbertCommented:
Very good examples here:

http://www.15seconds.com/howto/pg000945.htm

Brett
0
 
Amigo_fdAuthor Commented:
we just tested the script (like it is here above)

the restore won't work because the database is in use. Is there a simple way to stop it ? Not the entire sql server, but only this one ?

I can't have a master-login because it's a shared sql-server and I only have a login for my db...

Thanks
0
 
arbertCommented:
You can stop the database, but if your connection string has you connecting to that database, your connection will fail....

if you can connect to another database, you don't really have any options.
0
 
Amigo_fdAuthor Commented:
it didn't work this way because I didn't have "master" login for the sql server

did something completely different with xml-files, so found a solution but not the easiest one ;-)
0
 
arbertCommented:
"it didn't work this way because I didn't have "master" login for the sql server"

It's up to you to followup on the question--we had no idea what was going on.

I also don't appreciate the grade of a 'C'.  I would rather you ask for a refund of the points than receive a bad grade.

Brett
0
 
Amigo_fdAuthor Commented:
sorry, the c was a mistake ! can I still change that ?
0
 
Amigo_fdAuthor Commented:
please make it an A, the answer was correct, the only problem was that I didn't have adminstrator-access to the sql server
0
 
arbertCommented:
Appreciate it.

Brett
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now