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

Question

Post reply

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

tamil.selvanmca

Ten Centuries

Points: 1367
More actions
July 29, 2019 at 6:54 am

#3666691

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.

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

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

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

Is backup compression enabled on the SQL 2016 server?

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

Andrey SSC Eights! Points: 840 More actions · Answer 2

couple of ideas:

sql2016 doesn't have option 'backup compression default' enabled, but others (sql2014) do
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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

tamil.selvanmca Ten Centuries Points: 1367 More actions · Answer 4

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

Andrey SSC Eights! Points: 840 More actions · Answer 5

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

tamil.selvanmca Ten Centuries Points: 1367 More actions · Answer 6

tamil.selvanmca

Ten Centuries

Points: 1367

July 29, 2019 at 12:38 pm

#3666770

No I am not change recovery Model

tamil.selvanmca Ten Centuries Points: 1367 More actions · Answer 7

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

Andrey SSC Eights! Points: 840 More actions · Answer 8

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

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