MSSQL backup conversion

curiousb

I host a website (asp/mssql) with a provider I wish to move away from. I have a backup of the database (.bak) which is a standard mssql backup format.

However the host I want to move to uses mssql express edition and the backup won't restore to it. They say if I can convert the backup to plain SQL they can import it then.

Is there a tool available whereby I can convert from .bak to .sql, using an offline backup file?

Both hosts are shared hosting platforms so remote desktop access is impossible.

I would really appreciate any suggestions.

Graham

If you still have access to the source database, could you use something like Navicat to export from MS SQL (as SQL)? If I remember correctly there's a 30 day free trial.

curiousb

I do, I'll give that a try thanks.

Beano

If that doesnt work you can use the Generate Scripts option in SSMS that will generate scripts for both the schema and the data that you can use to recreate the database on your new server.

curiousb

Pardon my ignorance, but what is SSMS?

I have installed Navicat, but don'r seem to have an SQL export option. csv, xml, xls are all there but no sql so far...

Beano

curiousb wrote: »

Pardon my ignorance, but what is SSMS?

I have installed Navicat, but don'r seem to have an SQL export option. csv, xml, xls are all there but no sql so far...

SQL Server Management Studio

Graham

It's listed under Data Transfer tools on the navicat website:

http://www.navicat.com/products/navicat-for-sqlserver

TonyStark

curiousb wrote: »

I host a website (asp/mssql) with a provider I wish to move away from. I have a backup of the database (.bak) which is a standard mssql backup format.

However the host I want to move to uses mssql express edition and the backup won't restore to it. They say if I can convert the backup to plain SQL they can import it then.

Is there a tool available whereby I can convert from .bak to .sql, using an offline backup file?

Both hosts are shared hosting platforms so remote desktop access is impossible.

I would really appreciate any suggestions.

What version of SQL is the .BAK file and what is your target version of SQL Server Express Edition?

Tow

I have not used Developer/Express Edition for a new years, but from memory there was no difference between the data/file formats and the full version. As Tony asks, what are the version numbers of SQL. If they are different versions or your database exceeds limits or uses functionality which is not in the Express Edition you will have problems.

Yakuza

Just a side note, Developer and Express are not the same thing; Developer has the same functionality as Enterprise (it costs €50) but is only licenced for development use and not production. Express is limited at 1GB RAM per instance and 10GB per database as well as other limitations.

What versions are you moving from and to (not editions) 2005 to 2008/2012 etc?

I'd either pick a different host that uses a proper edition of SQL Server, or you could install Developer yourself, import the backup, create a new DB (that has the same version as your target host) and give that to them.

I'll repeat; a hosting option that uses SQL Express is probably a false economy, unless you just want a bare bones database in your application.

curiousb

Thanks for the replies. It is coming from 2012 and going to 2008 R2.

Tow

curiousb wrote: »

Thanks for the replies. It is coming from 2012 and going to 2008 R2.

There is your problem, you are going backwards.
I would be very reluctant to change from a provider who uses a full version to one with the free version, especially when their free version is a couple of revisions old.

curiousb

Ok, that makes sense. I have been able to export a backup using Navicat (thanks Graham), which is compatible with 2008 R2, so I will try an import and see how that works.

The issue with hosts is the level of support to be honest. The current provider has a useless level of support, takes days to get replies to tickets and the one i am moving to is one I use for other sites and has great support so thats the main reason I am moving.

The site is reasonably small with not a lot of traffic so hopefully it will run ok on the new server. I can put it on a VPS if necessary but am trying shared first.