Backup / restore of msde database...

[lidds]

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 it's 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 get's 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

[ost]

if its an empty database then how about creating a script for the database and then running that?

[lidds]

The database has no actual data in it, but the problem is that it has about 54 stored procedures and I obviously don't 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

[PlausiblyDamp]

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

BACKUP DATABASE Northwind TO DISK = 'c:\nwind.bak'

and restore with

RESTORE DATABASE Northwind FROM DISK = 'c:\nwind.bak'

[lidds]

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 don't suppose you could give me a quick example or a link to how I would acheive this??

 

Thanks inadvance

 

Simon

[PlausiblyDamp]

In enterprise Manager right click on the DB in question and go to the 'All Tasks' sub menu, click 'Generate SQL Script'.

[lidds]

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

[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.