RESTORE DATABASE - Unknown filepath 2

[Davidmc555]

Hi.

I'm sorry if this has been asked before but I couldn't find anywhere on the tek-tips site or Books online that explained how to do what I'm trying to do.

I'm trying to make up a script that restore a database. This database will be a preconfigured blank version that our product uses. I want to make an executible file that takes the script and restore the file onto the user's SQL server.

ALTER DATABASE tmBase
SET SINGLE_USER WITH ROLLBACK AFTER 15 SECONDS

RESTORE DATABASE tmBase
   FROM DISK = 'D:\live.bak'
GO

That's what I have thus far. I'd rather not have the D:\ part as the .bak file will be zipped up with the executible.

Am I making sense? Cheers for any help in advance.

 

[mrdenny]

Why not have you application run a SQL statement to create the database and the objects after asking the user where they want the database created.

If you are going to stick to the restore method, then you'll need to know where the backup file will be so you can restore it.

You'll also need to prompt the user for where they want the database so you know where to put the files after the restore. The database can be moved with the MOVE paramater of the restore command.

If you are restoring a blank database on install, odds are the user doesn't have the tmBase database. The ALTER DATABASE statement will fail.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Davidmc555]

Thanks for the Reply mrdenny.

Perhaps, I should explain a little bit more about our application. We develop programs that set up SQL servers and copy into them the live.bak file which is used by an app we built.

This small app I'm trying to create is for our own personal internal use. We migrate data from other databases into our own SQL databases, back it up and send it back to the relevant owner for use in our software.

This has a lot of fiddly steps and I thought doing it all in SQL might be the best thing. The idea was that the SQL executible and the live.bak would be zipped up and downloaded by one of our other offices. However, it could be unzipped anywhere and therefore the filepath won't be known.

I guess copying the file using a batch file will need to be done unless there are other alternatives.

Thanks again.

 

[mrdenny]

You have to know where the file is in order to restore it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

Issue: You are trying to create a blank database.

Your solution is to backup an 'original' blank database and then use an app to restore the blank to a new site.

This is 'reworking the wheel'. You are just 'creating' a database, so do that in your application like Mr.Denny suggests.

Take your original blank database and 'script it off' (Enterprise Manager can make it easy for you). Put that 'CREATE DATABASE' script into your application. Change the location values to variables and have your application ask for the locations.

Again, this is what Mr.Denny suggested. I'm just rewording and agreeing with his suggestion.

-SQLBill

Posting advice: FAQ481-4875

 

[Davidmc555]

Thanks, I'll give that a try then.

Thanks again folks.