Cannot restore backup on SQL Server Express

0
votes

I have a backup of a SQL database, and it is 409 MB.

When I try to restore it says:

Restore failed for Server 'SPLYF-R3K8G8JL2\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

2
sql-server-2008
PS how is it that you have the same machine name as this guy, who posted this same error message SIX YEARS AGO? Seems an odd coincidence. social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/… - Aaron Bertrand

2 Answers

4
votes

(1) it seems like you are trying to restore a SQL Server 2008 R2 database on an instance of SQL Server 2005. You can't go backwards like that.

(2) SQL Server 2005 Express had a limit of 4GB per database. With 2008 R2, the limit is 10GB.

(Note that the size of the backup file is not necessarily the size of the database. The backup file does not include empty space, so if your MDF is >4GB (and mostly empty) the limitation is still enforced, since the limitation is on data file size, and not on backup size or actual data.)

So, the solution is: install SQL Server 2008 R2 Express Edition, and you should be able to restore your database there (assuming it is < 10GB).

If your data file is currently > 10GB but there is < 10GB of data in it, you can:

  • in the source instance (or after attaching to a different edition without this limitation), since it seems like there is far less data than 10GB, reduce the data file size using DBCC SHRINKFILE to something less than 10GB.
  • take another backup
  • proceed with the attach to Express
1
votes

Are you sure you are running SQL Server 2008 R2 Express?

Database backups are often compressed: it's not a surprise that this backup would create a database larger than 4 GB, the limit for SQL Server 2008 Express and SQL Server 2005 Express.

Try upgrading to SQL Server 2008 R2 Express.