0

I got a 42 MB .bak file, when restored, the database size is about 2048.00 MB. When I do backup for this DB .bak file size becomes 277 MB.

Why .bak file size increases? I do it immediately after restore for test.

Please find RESTORE SCRIPT:

IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL
BEGIN
     DROP TABLE #tmp
END
go
declare @path varchar(50)
create table #tmp
(
LogicalName nvarchar(128) 
,PhysicalName nvarchar(260) 
,Type char(1) 
,FileGroupName nvarchar(128) 
,Size numeric(20,0) 
,MaxSize numeric(20,0),
Fileid tinyint,
CreateLSN numeric(25,0),
DropLSN numeric(25, 0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlocSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit, 
TDEThumbPrint varchar(50),
SnapshotUrl varchar(50)
)
set @path = '/opt/mssql/backup/IDIT_MSSQL.bak'

insert #tmp
EXEC ('restore filelistonly from disk = ''' + @path + '''')
--select * from #tmp
Declare @RestoreString as Varchar(max)
Declare @NRestoreString as NVarchar(max)
DECLARE @LogicalName  as varchar(75)
Declare @counter as int
Declare @rows as int
set @counter = 1
select @rows = COUNT(*) from #tmp
--select @Rows as [These are the number of rows]

DECLARE MY_CURSOR Cursor 
FOR 
Select LogicalName 
From #tmp

Open My_Cursor 
set @RestoreString = 
'RESTORE DATABASE [TEST_CONTAINER] FROM DISK = N''/opt/mssql/backup/TEST_MSSQL.bak'''
+ ' with  ' 

Fetch NEXT FROM MY_Cursor INTO @LogicalName 
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
select @RestoreString =
case 
when @counter = 1 then 
   @RestoreString + 'move  N''' + @LogicalName + '''' + ' TO N''/var/opt/mssql/data/'+
@LogicalName + '.mdf' + '''' + ', '
when @counter > 1 and @counter < @rows then
   @RestoreString + 'move  N''' + @LogicalName + '''' + ' TO N''/var/opt/mssql/data/'+
@LogicalName + '.ndf' + '''' + ', '
WHen @LogicalName like '%log%' then
   @RestoreString + 'move  N''' + @LogicalName + '''' + ' TO N''/var/opt/mssql/data/'+
@LogicalName + '.ldf' +''''
end
--select @RestoreString

set @counter = @counter + 1
FETCH NEXT FROM MY_CURSOR INTO @LogicalName
END

--select @RestoreString
set @NRestoreString = @RestoreString
EXEC sp_executesql @NRestoreString
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

BACKUP SCRIPT

BACKUP DATABASE TEST_CONTAINER TO DISK = N'/opt/mssql/backupNew/TEST_MSSQL.bak' WITH NOFORMAT, NOINIT, NAME = 'TEST_CONTAINER', SKIP, NOREWIND, NOUNLOAD, STATS = 10

General Info

Options Info

enter image description here

1 Answer 1

5

By default backups are uncompressed. So it would be expected that if you restored a compressed backup and then took an uncompressed backup of the restored database the new backup would be bigger.

Like a few other defaults in SQL Server, it's the default not becuase it's optimal, but to match the behavior of older versions. But for the vast majority of modern systems, you should compress backups.

You can get a compressed backup by changing the server-level default for backup compression or by specifying the COMPRESSION option in BACKUP DATABASE.

BACKUP DATABASE TEST_CONTAINER 
  TO DISK = N'/opt/mssql/backupNew/TEST_MSSQL.bak' 
WITH FORMAT, 
     INIT, 
     NAME = 'TEST_CONTAINER', 
     COMPRESSION,
     STATS = 10
5
  • Thanks David. This is exactly what i was looking for. Thanks again!!
    – Raushan
    Nov 20, 2021 at 16:50
  • One more question can i shrink restored db ?? I tried this DBCC SHRINKDATABASE (TEST_CONTAINER); GO
    – Raushan
    Nov 20, 2021 at 16:50
  • DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim. DBCC SHRINKDATABASE: File ID 2 of database ID 5 was skipped because the file does not have enough free space to reclaim. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    – Raushan
    Nov 20, 2021 at 16:51
  • @Raushan That means there's nothing to shrink your database down to (there's no unused space), and normally you shouldn't need to shrink your database anyway. By the way, if you found David's answer helpful, feel free to upvote and/or accept it so other users see it verified as helpful.
    – J.D.
    Nov 20, 2021 at 17:05
  • 1
    @DavidBrowne-Microsoft You may also want to consider explaining (or at least quoting) the different compression options for backups as well.
    – J.D.
    Nov 20, 2021 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.