Database BackupSize has Changed after migration from SQL Server 2012 Exterprise to SQL Server 2012 Express [closed]

Question

I would like to say that after migration of MS SQL server 2012 Enterprise to MS SQL Server 2012 Express. I have seen there is some subtle diffence in DB backupsize as well as in "LSN" of all databases.

Let's from begining i am going to write down step by step . what i have done in migration from MS SQL server 2012 Enterprise to MS SQL Server 2012 Express.

1) I had taken all 3 Audit databases backup in MS SQL Server 2012 Enterprise as on dated 23/12/2015. The MS SQL Server 2012 Enterprise version was

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
    Oct 19 2012 13:38:57 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

2) After successfully Migration From MS SQL Server 2012 Enterprise to MS SQL server 2012 Express . Restored all 3 databases in MS SQL Server 2012 Express. All 3 databases restoreration went successfully as on dated 24/12/2015. The MS SQL Server 2012 Express Version is

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
        Oct 19 2012 13:38:57 
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

3) After working of few days i have taken full backup of all my 3 Audit databases in MS SQL Server 2012 Express as on dated 31/12/2015.

4) For myself to check out the database consistency with "LSN" and DatabaseSize. I have gone through so many SQL Blog as well as MSDN BOL. And find out this Missing Transaction log in chain, Possible to skip and http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/26/transaction-log-backup-and-restore-sequence-myths-amp-truths.aspx for cheking the "LSN" consistency of log chain.

5) FROM This TSQL to check 'first LSN' and 'Last LSN' and log swquence

Restore headeronly from disk =N'C:\Backup\AuditDatabase1_23122015.bak'
Restore headeronly from disk =N'C:\Backup\AuditDatabase2_23122015.bak'
Restore headeronly from disk =N'C:\Backup\AuditDatabase3_23122015.bak'

-- These above 3 databses backup had taken in Enterprise (as on dated 23/12/2015)--

Which details is as follows in respectively from databse 1 to 3.

BackupSize  FirstLSN               LastLSN        CheckpointLSN       DatabaseBackupLSN
243532800   571000000388500000  571000000390600000  571000000388500000  571000000372500000
92480512    671000000080800000  671000000082900000  671000000080800000  671000000064900000
57824256    495000000001600000  495000000002600000  495000000001600000  494000000027300000

And also to checked Express full backup all 3 audit databases, which i had taken full backup as on dated 31/12/2015

Restore headeronly from disk =N'C:\Backup\AuditDatabase1_31122015.bak'
Restore headeronly from disk =N'C:\Backup\AuditDatabase2_31122015.bak'
Restore headeronly from disk =N'C:\Backup\AuditDatabase3_31122015.bak'

Which details is as follows in respectively from databse 1 to 3.

BackupSize  FirstLSN               LastLSN        CheckpointLSN       DatabaseBackupLSN
243401728   571000000558800000  571000000560900000  571000000558800000  571000000478100000

91956224    674000000027800000  674000000029900000  674000000027800000  672000000043600000

57627648    498000000013500000  498000000016700000  498000000013500000  496000000028600000

For all 3 databases combinig i am writing the details. Which is as follows

Databases Backup in   BackupSize  FirstLSN               LastLSN        CheckpointLSN       DatabaseBackupLSN
     (1 DB in Enterprise)   243532800   571000000388500000  571000000390600000  571000000388500000  571000000372500000
      (1 DB in Express)     243401728   571000000558800000  571000000560900000  571000000558800000  571000000478100000


    (2 in Enterprise)       92480512    671000000080800000  671000000082900000  671000000080800000  671000000064900000
    (2 in Express)          91956224    674000000027800000  674000000029900000  674000000027800000  672000000043600000


     (3 in Enterprise)      57824256    495000000001600000  495000000002600000  495000000001600000  494000000027300000
      (3 in Express)        57627648    498000000013500000  498000000016700000  498000000013500000  496000000028600000

Here i have taken the few details of TSQL query results to understand the DatabaseSize and 'First LSN' & 'Last LSN'. In above table there is Respective database comparision results , which you can seen in table.

Hope i am clear to understood to you. What i mean to say that about log sequence number checking of all 3 databases.

How i could understand i have restore my database with consistent "LSN".

Any suggestion would be appreciated.

Answer 1

Assuming no significant data volume changes occurred after migrating to the Express Edition then the simplest explanation is backup compression. Backup compression is not supported in Express Edition. Furthermore you may have been leveraging it on the Enterprise server without even noticing, if it was enabled by default.

Answer 2

Examples and data reported by you are not sufficient to asnwer your question, but you can consider the following:

• You have to be sure about the amount of reserved space in your database when you executed the backup. You can obtain this size executing:

  USE exampledb GO EXEC sp_spaceused @updateusage = 'true'

  In the output of the command you can read a section called "reserved". THis is the actual size of data in youur database. THe size of your backup pwill be tound this size.

• Also you have to considera the transaction log records included in your full backup: The start LSN of the log included in a database backup is the minimum of: LSN of the last checkpoint, LSN of the start of the oldest active transaction, LSN of the last replicated transaction. This can modify the size of your backup.