Importing a MSSQL BAK file (plans to move to MySQL)

[mattclifton]

Hi all

I'm struggling with a MSSQL database that, ultimately, I want to get into MySQL, but for now I just want to import to a local server.
I know nothing about MSSQL, but I am pretty familiar with MySQL.

I have a .bak file exported from my current hosting company. I have installed SQL Server Express 2012 on a virtual Windows 7 drive on my Mac, and am trying to restore it. However, it seems like there's a built-in filegroup size limit in the original .bak file, and nothing I try seems to allow it to auto grow when I restore it.

Here's what I'm doing.

I create a new database in Object Explorer, go to Properties, Files, and set both the data file and log to have an initial size of 1000MB, and an unlimited max size. Bear in mind that the current database is around 200MB. And I do have 30GB free on my HD.

restore database db203021561 from disk='c:\temp\db203021561.bak' with move 'db203021561' to 'c:\temp\db203021561.mdf', move 'db203021561_log' to 'c:\temp\db203021561.ldf'

The restore step processes it for a while, and then ends with:
RESTORE DATABASE is terminating abnormally.
Could not allocate space for object 'sys.sysschobjs'.'nc2' in database 'db203021561' because the PRIMARY filegroup is full.

So I'm kind of stumped as to how I tell it to restore the database so I can start converting it.

Can anyone advise? Also, if anyone knows of a better way to convert MSSQL to MySQL, I'm all ears! The other existing tools either seem to rely on already having local access to a database, or being able to log in remotely to a hosting server (and it seems like the current hosting doesn't allow remote access.)

Thanks!

Matt

[BitBlt]

Couple of things.
1. You shouldn't have to create a database prior to doing your restore.
2. If you HAVE created a database, run the restore command with a "WITH REPLACE" in addition to your "MOVE" clauses, like so:

restore database db203021561 
from disk='c:\temp\db203021561.bak' 
with replace, 
move 'db203021561' to 'c:\temp\db203021561.mdf', 
move 'db203021561_log' to 'c:\temp\db203021561.ldf'

Hope that helps. Good luck!

[mattclifton]

Thanks for the quick reply, BitBlt. I realized that I could try it with or without creating the DB first. I didn't know about the WITH REPLACE clause.

Unfortunately, it doesn't seem to help with the filegroup size issue. Still stumped!

best,
Matt

[BitBlt]

Without knowing more about the original database from which the backup was made, I'd be shooting in the dark trying to advise here. One thing you might try is using the RESTORE FILELISTONLY statement to get more information about the backup file you have.
It looks like this:

restore filelistonly 
from disk='c:\temp\db203021561.bak'

After that, there are a couple of questions you might consider;
1. Are there multiple filegroups in the original MDB? If so, you should expand your restore statement to list all the data names
2. Are there multiple MDB's in the original database? If so, you should expand your restore statement to list all the data names

If all appears correct from above, consider these:
3. Can you have the host vendor generate a restore statement for you for your database? Sometimes, using the SQL Server scripting capability will give you options you didn't know you needed.
4. Will the host vendor allow you to remotely connect so you can simply import the data using SSIS? That would solve a lot of issues.
5. If the vendor actually has placed a size limit on the MDF file, would they be willing to take off the size limit temporarily, and take another backup for you? That might also help.

I can't really think of anything else to try. Hopefully one of the above items will help. Sorry 'bout that!