Cannot re-attach SQL Server Express database to the same server after detaching?
2
votes
1
answer
893
views
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)
Asked by DrCopyPaste
(241 rep)
Sep 29, 2017, 02:17 PM
Last activity: Nov 16, 2020, 09:52 PM
Last activity: Nov 16, 2020, 09:52 PM