Sample Header Ad - 728x90

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