Restore Databse From SQL Server 2014 to 2016 Database Size was Increse

  • Hi

    I take backup from SQL server 2014 the backup size was 250 MB

    and I restored in SQL server 2016 when I take the backup the size was increased 4.5 GB

    and also I was tested in another server SQL server 2014 restored and backup it was 250 MB only.

     

  • Is backup compression enabled on the SQL 2016 server?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • couple of ideas:

    1. sql2016 doesn't have option 'backup compression default' enabled, but others (sql2014) do
    2. between  "I restored in SQL server 2016"     and      "when I take the backup the size was increased 4.5 GB"   data is changed and/or transaction log has grown and there were no log backups
  • Did the recovery model get changed and the logs are now huge?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Andrey,

    Thank You

    Data File Size : 4360 MB

    Log File Size : 3398 MB

    when I checked Both SQL server 2014 and 2016 Data File and Log file size was same.

    the below script to run the backup file in 2014 and 2016 bur backup file size was different

    SQL server 2014 : 250 MB

    SQL server 2016: 4.25 GB

    BACKUP DATABASE [DBName]

    TO DISK = N'E:\Old\DBName.bak' WITH NOFORMAT

    , NOINIT

    , NAME = N'DBName-Full Database Backup'

    , SKIP

    , NOREWIND

    , NOUNLOAD

    , STATS = 10

    GO

     

     

  • add ", COMPRESSION"  and run backups again , compare backup sizes

    example:

    BACKUP DATABASE [DBName]
    TO DISK = N'E:\Old\DBName.bak' WITH NOFORMAT
    , NOINIT
    , NAME = N'DBName-Full Database Backup'
    , SKIP
    , NOREWIND
    , NOUNLOAD
    , STATS = 10
    , compression
  • No I am not change recovery Model

  • Hi Andrey

    Thank you,

    BACKUP DATABASE [DBName]
    TO DISK = N'E:\Old\DBName.bak' WITH NOFORMAT
    , NOINIT
    , NAME = N'DBName-Full Database Backup'
    , SKIP
    , NOREWIND
    , NOUNLOAD
    , STATS = 10
    , compression

    I run the above query in SQL Server 2016 the backup size is 250 MB

    But in SQL server 2014 I am not use compression

    Thank You

     

  • tamil.selvanmca wrote:

    But in SQL server 2014 I am not use compression

    I bet it's configured there by default

    run the query on both servers :

    SELECT *
    FROM sys.configurations
    WHERE name = 'backup compression default' ;
    GO
  • Andrey wrote:

    tamil.selvanmca wrote:

    But in SQL server 2014 I am not use compression

    I bet it's configured there by default

    run the query on both servers :

    SELECT *
    FROM sys.configurations
    WHERE name = 'backup compression default' ;
    GO

    Nice catch!

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Andrey,

    SQL server 2014 Value = 1 and Value_in_use = 1

    SQL server 2016 Value = 0 and Value_in_use = 0

    Thank You

  • tamil.selvanmca wrote:

    Hi Andrey,

    SQL server 2014 Value = 1 and Value_in_use = 1

    SQL server 2016 Value = 0 and Value_in_use = 0

    Thank You

    as expected

    read this :

    https://www.mssqltips.com/sqlservertip/1964/sql-server-2008-backup-compression/

     

  • Hi Andrey,

    Thank You...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply