SQL Server Express - .bak restore failed due to size limit

Question

SQL Server Express - .bak restore failed due to size limit

tomas-krchnak 0

I am using MS SQL Express 2019 and I am not able to restore database backup created with this edition due to size limit.

Error detail: CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database. RESTORE DATABASE is terminating abnormally.

How is this even possible?

There are some varbinary filesteam columns but if I am not wrong (https://learn.microsoft.com/en-us/sql/relational-databases/blob/filestream-compatibility-with-other-sql-server-features?redirectedfrom=MSDN&view=sql-server-ver15) these should not be taken into account for size limit of MS SQL Express.

3 answers

Your answer

Answer 1

Olaf Helper 47,436

How is this even possible?

If the error message says, the database size it to large for Express limitation of 10 GB, then it is to large.

tomas-krchnak 0 Reputation points

2023-06-28T11:15:57.5366667+00:00

But how can it exceed the size limit of MS SQL Express in the first place? I would assume that MS SQL Express would not allow inserting new records, ... once the limit is exceeded.
tomas-krchnak 0 Reputation points

2023-06-28T11:36:47.2133333+00:00

I would expect that db backup created using MS SQL Express 2019 is always restorable to MS SQL Express 2019.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

I think the best advice I can give is to install an instance of Developer Edition and restore the backup on this edition. Then you can check the sizes. If you find that the size without the filestream data is less than 10 GB, there is either an error in the documentation or in SQL Server.

Another thing you can try is something like:

RESTORE DATABASE filestreamtest FILEGROUP = 'PRIMARY' FROM DISK = 'C:\temp\filestreamtest.bak' WITH PARTIAL, RECOVERY

This restores only the primary filegroup, but not the filestream data.

I agree that it is unexpected that the backup does not restore on Express, if it was indeed taken on Express. But without a repro for the issue it is difficult to comment in more detail.

Erland Sommarskog 121.4K MVP Volunteer Moderator

I tried to reproduce the problem with a simple script. The below script creates a database that is > 10 GB, including the filestream data, but is very small without it. The script then backs up the database, drops it and successfully recreates it. I running it on SQL Express 2022.

USE tempdb
go
CREATE DATABASE filestreamtest ON (NAME = 'filestreamtest', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\data\filestreamtest.mdf'),
FILEGROUP filestreamdata CONTAINS FILESTREAM (NAME = 'filestreamtestdir', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\data\filstreamtestdir')
LOG ON (NAME = 'filestreamtest_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\data\filestreamtest.ldf')
go
ALTER DATABASE filestreamtest SET RECOVERY FULL
go
USE filestreamtest
go
CREATE TABLE normaltable (a int NOT NULL, b sysname NOT NULL, CONSTRAINT pk_normal PRIMARY KEY (a))
INSERT normaltable (a, b) SELECT object_id, name FROM sys.objects
go
CREATE TABLE filestreamtable 
   (id int NOT NULL,
    guid uniqueidentifier NOT NULL ROWGUIDCOL,
    filename sysname NOT NULL,
    filestream varbinary(MAX) FILESTREAM NULL
    CONSTRAINT pk_filestreamtable PRIMARY KEY (id),
    CONSTRAINT u_guid UNIQUE( guid)
)
go
INSERT filestreamtable(id, guid, filename, filestream)
  SELECT value, newid(), concat('slask', value, '.txt'), convert(varbinary(MAX), replicate(convert(varchar(MAX), 'A'), 1E9))
  FROM  generate_series(1, 12)
go
BACKUP DATABASE filestreamtest TO DISK = 'C:\temp\filestreamtest.bak' WITH INIT
go
USE tempdb
go
DROP DATABASE filestreamtest
go
RESTORE DATABASE filestreamtest FROM DISK = 'C:\temp\filestreamtest.bak' WITH RECOVERY
go
USE filestreamtest
go
SELECT id, guid, filename, substring(filestream, 1, 1000) FROM filestreamtable   -- Now succeeds!
go
USE tempdb
go
DROP DATABASE filestreamtest

I can't say why it does not work in your case, but it may be that your regular data is close to the limit, and the space check had not yet struck when you took the backup, but when you restore it happened. Maybe due to some transient space consumption as transactions were rolled forward or back when you restored.

Answer 3

Anonymous

Hi @tomas-krchnak

You can try to shrink the source database and make a backup again, of course this is not the best solution. As Erland said, the best advice is to upgrade Express to the Developer edition. Go to SQL Server Installation Center->Maintenance->Edition upgrade.

For more details, you can refer to these links:

https://dba.stackexchange.com/questions/75220/reducing-database-size-to-restore-to-sql-server-express

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ffff6205-f986-4b54-ae55-081c359fee9f/database-restored-failed-because-licensed-limits-10240mb-per-database-in-ssms?forum=sqldataaccess.

Best regards,

Aniya

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-29T20:33:15.23+00:00

As Erland said, the best advice is to upgrade Express to the Developer edition.

No, I did not really say that. The idea was to install an instance of Developer Edition to test the backup, not to actually upgrade. Very important is what Tomas is using the instance. If it's only dev/test, yes, upgrading may be a good idea. But absolutely not if it the instance is used for production, since Express is licensed for production, and Developer is not!

Share via

SQL Server Express - .bak restore failed due to size limit

3 answers

Your answer