Sql-server – Cannot re-attach SQL Server Express database to the same server after detaching

attachmigrationsql serversql-server-2008-r2sql-server-2012

I migrated a database in a test environment from one server running SQL Server Express 2008 R2 to another running the full version of SQL Server 2012.

I did that by detaching the database from the first server, copying over the .mdf and .ldf files, and attaching those to the new server.

That worked well, except I see some performance losses, hence I want to do some comparisons in terms of speed and maybe execution plans and here starts my problem:

I cannot reattach the original database I left on the old SQL Server Express instance. I did not do anything to it since it was detached and it worked like a charm until I did.

But now, when I want to attach it to the original SQL Server Express instance, I get this message:

CREATE DATABASE or ALTER DATABASE failed because the resulting
cumulative database size would exceed your licensed limit of 10240 MB
per database. (.Net SqlClient Data Provider)

Sad thing is, this is party true, the database is 10,911,616 KB in size (1,024 KB for logs), which, honestly, stumps me, since I didn't do anything with it after detaching and it worked up until then.

However I am very certain, there is lots of free space inside that database (something around 9GB)

So this is a three-part-question:

  1. Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach?

  2. Is there a way to free up space inside that .mdf file without actually attaching it?

  3. Or could it be feasible to take the database I already attached to the new (full) SQL Server instance, shrink that in size and then attach it to SQL Server Express instance? (or would attaching to the full SQL Server instance change the format of said .mdf file to be unusable by SQL Server Express?)

Additional Server information:

  • New Database Server has version Microsoft SQL Server 2012 (SP1) –
    11.0.3128.0 (X64)

  • Old Database Server has version Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)

  • Database is running in compatibility level SQL Server 2008 (100)

Best Answer

  1. Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach?

You have 2 problems:

  1. Your 'old' server is SQL Server 2008 R2, and the new one is SQL Server 2012. You cannot attach a database from a SQL Server 2012 instance on a SQL Server 2008R2 instance.
  2. The limit of 10Gbs is exceeded.

What to do:

  • attach your database to a SQL Server 2012 (or higher) instance.
  • Export data from that instance to your SQL Server 2008 R2 instance.
  1. Is there a way to free up space inside that .mdf file without actually attaching it?

You do not actually need it. I do not know such a way.

  1. Or could it be feasible to take the database I already attached to the new (full) SQL Server, shrink that in size and then attach it to the SQL Server Express instance? Would attaching the file to a full version of SQL Server change the format of said .mdf file to be unusable by SQL Server Express?

You can do it if you will work on SQL Server 2012 Express. No - the format will not be changed.

Related Question