Backup / restore of msde database...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
Hi all, I need some advice, guidance and help with this problem.

With my application I am writing there is a need for the administrator to create a new project (database). Now I have written an Admin module for my app, and within this I want to allow the creation of this new database. However the new database I need to be a copy of the default database I supply with the app (this has no data, but does contain some 15 tables and 54 stored procedures, hence the reason why I am thinking its best to copy the default database and rename).

Now this is where I need help.... I think there is 2 ways I can acheive this:-

1) Detach the default database. Copy the .mdf and .ldf files and rename. Attach both the default database and the new database.

I have done the code for this, but this does seem like it would be quite to error trap i.e. if something happened with the copy I would need to ensure that the default database gets re-attached.

2) Is I could create a backup of the default database into a .bak and then just restore the backup into a new database. Which to be honest seems a better way to do this, especially as the default database structure will not change.

The only problem with this is I am unsure how to acheive this, code wise??

Just a bit of more info, the reason why I am creating seperate databases instead of including them in the same database and using different tables is that when the job / project ends the database will need to be electronically archived. And there is legal issues about storing different job / project data.

Anyway, sorry to go on but this is quite an important issue that I would like to do right.

Thanks in advance

Simon
 
The database has no actual data in it, but the problem is that it has about 54 stored procedures and I obviously dont want to have to create script for all of those + the table. Also it must be quicker to some how copy the database (one of the methods above) than to create a new database and all the tables and stored procedures???

I not to sure about the best way to proceed, hence the post.

Any help and advice would be appreshiated guys.....

Simon
 
If you have access to Enterprise Manager it can generate a full script to recreate the database. Otherwise I would plump for the backup / restore option.

From any valid SQL tool (e.g. Query Analyzer) you could always do a backup with something as simple as
Code: 
BACKUP DATABASE Northwind TO DISK = c:\nwind.bak
and restore with
Code: 
RESTORE DATABASE Northwind FROM DISK = c:\nwind.bak
 
I do have access to Enterprise Manager, so the option that you were suggesting about using a script to recreate the database could be possible.

How should I go about doing this, I assume I would create a new database and then somehow run this script in???

I dont suppose you could give me a quick example or a link to how I would acheive this??

Thanks inadvance

Simon
 
Sorry to keep asking questions on this, I assume this will then create a .sql script ??

If I then created a new database I assume I could then run the .sql file into the new database and that would create the tables and stored procedures??

Am I on the right track?? If so how would I run the .sql script into the new database using vb.net??

Thanks for your time PlausiblyDamp
 
If you look at the generated script (or the interface to create it) you will see that it will script everything including the creation of the DB itself.
Easiest way to run the script would be to just run the command line osql.exe tool and pass the script in as a parameter.
 
Back
Top