[RESOLVED] Restore MS SQL database without using .bak file

[Slowhand]

Hello people,

A customer of us has purchased hosting where we can install software for them.
What we didnt' know is, we don't have rights to restore .bak files.
The hosting provider does it once, after that we have to pay.

Since this is a product in development, it's possible we'll need to update the database a couple of times.

So I'm looking for a way to do this without restoring a new .bak file.
I wondered if there's a way to make a backup like MySQL does it, with a huge sql dump.

[Jason Reed]

Which version of SQL Server are you using?

[Slowhand]

2005

[Jason Reed]

Ok I checked my "Express" version of it and I see no way to dump to SQL like MySQL. You can dump the structure to SQL but not the data inside which I figure is what you are wanting.

I'm guessing that to do this you would have to have an external app that reads the data from the database and creates an SQL script (with maybe a TRUNCATE call for each table). Then you could just run the standard SQL script without using a BAK file.

From what it looks to me is that MS thinks that the BAK file thing everyone would want to use and therefore doesn't provide any functionality to dump to SQL.

[simmo]

are you restoring to a certain state? or needing as backups to prevent data corruption/loss?

If restoring to a state you can write the scripts, use the SQL to build the structure then have scripts to populate needed data. If using as a backup solution you could have scripts that pump records to another table and move them back as needed, but that might be fairly resource intensive.

[Slowhand]

I've found a Microsoft application, called Microsoft SQL Server Database Publishing Wizard, that can export both schema and data to an sql file, just like MySQL.