Member Avatar for 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

  • 2 Contributors
  • 3 Replies
  • 763 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Quick, practical follow-up for (and anyone else hitting the "PRIMARY filegroup is full" error). was right to ask you to inspect the backup contents — the common causes are: the backup contains multiple data files or filegroups that you did not MOVE during restore, the backup is not a full/base backup, or the backup was taken on a server with file-size/autogrowth limits that the destination cannot satisfy.

Do these checks in order:

  • Verify the backup metadata and integrity (header and verify checks) so you know the backup type and source SQL Server version. Run the header and verify commands against the .bak to see the backupset type and version.
  • Use the backup's file-list output (the file-list command) to get every logical file name and size. If there are secondary MDF/NDF files or filestream containers, you must include a relocation (MOVE) for each logical file in your restore statement.
  • Confirm the backup is a full backup. A differential or log-only backup cannot be restored by itself. Also confirm the backup wasn't created on a newer SQL Server build than your Express instance — you cannot restore a newer-version backup to an older server.

Quick troubleshooting pointers:

  • Make sure the target drive really has the free space available to SQL Server and that the SQL Server service account can write to the destination folder. Virtual disk/container quotas can be misleading.
  • If you created a database first, overwrite it during restore (the restore operation can replace existing files). If the restore still fails after moving every logical file, ask the host to produce a fresh full backup (or one without restrictive file-size settings).

Once you can restore locally, export/convert options include schema+data scripting, CSV/BCP exports, or using migration tools (MySQL Workbench migration or Microsoft's SSMA). If the restore keeps failing despite the above, ask the hosting provider to run the restore on their side or give you a full, copy-only backup taken with default autogrowth settings.

Recommended Answers

All 3 Replies

Member Avatar for 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!

Member Avatar for 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

Member Avatar for 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!

Edited by BitBlt
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.