How do I shrink the size of backup file

  • Hello:

    Our compnay wants me to

    1.) make a back up of the production database ( The database is 150 GB ).

    2.) restore the backup in a different server.

    3.) Remove data except for 10 customers.

    4.) Make a new backup using the database in step #3.

    5.) Restore the database (The one that has only 10 customers ) to another server.

    I have completed steps 1 through 4.

    However the size of the backup obtained in step #4 is still large in size. I expected it

    to be about a tenth of the original database or even less.

    what am I doing wrong here.

    Is there a command to force SQL server to reduce the size of the backup file ?

  • The problem is that removing the data from the database does not cause the size of the database to go down. Then when you back it up you are still backing up the full size of the database. After you remove everything but the 10 customer records you will need to shrink the database prior to backing it up. Once you have done this your backup will be a reasonable size.

    Regards,

    Jason P. Burnett
    Senior DBA

  • try to rebuild indexes.

  • How do I shrink the size of the database.

    Is there a command that I can use ?

  • RE: Rebuild Indexes ?

    Is there one single command that will start to rebuild all the indexes ?

    (We have about 2000 tables. Some of the tables have upto 20 indexes. )

  • mw112009 (2/3/2011)


    RE: Rebuild Indexes ?

    Is there one single command that will start to rebuild all the indexes ?

    (We have about 2000 tables. Some of the tables have upto 20 indexes. )

    try this:

    -------------------------------------------------------------------------------------------

    -- OBJECT NAME : isp_ALTER_INDEX

    --

    -- AUTHOR : Tara Kizer

    --

    -- DATE : February 27, 2007

    --

    -- INPUTS : @dbName - name of the database

    -- @statsMode - LIMITED, SAMPLED or DETAILED

    -- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)

    -- @minFragPercent - minimum fragmentation level

    -- @maxFragPercent - maximum fragmentation level

    -- @minRowCount - minimum row count

    --

    -- OUTPUTS : None

    --

    -- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,

    -- sys.schemas, sys.indexes, sys.partitions

    --

    -- DESCRIPTION : Defragments indexes

    /*

    EXEC isp_ALTER_INDEX

    @dbName = 'DatabaseName',

    @statsMode = 'SAMPLED',

    @defragType = 'REBUILD',

    @minFragPercent = 10,

    @maxFragPercent = 100,

    @minRowCount = 1000

    */

    -- 09/03/2008 - http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

    -------------------------------------------------------------------------------------------

    ALTER PROC [dbo].[isp_ALTER_INDEX]

    (

    @dbName sysname,

    @statsMode varchar(8) = 'SAMPLED',

    @defragType varchar(10) = 'REORGANIZE',

    @minFragPercent int = 25,

    @maxFragPercent int = 100,

    @minRowCount int = 0

    )

    AS

    SET NOCOUNT ON

    IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')

    BEGIN

    RAISERROR('@statsMode must be LIMITED, SAMPLED or DETAILED', 16, 1)

    RETURN

    END

    IF @defragType NOT IN ('REORGANIZE', 'REBUILD')

    BEGIN

    RAISERROR('@defragType must be REORGANIZE or REBUILD', 16, 1)

    RETURN

    END

    DECLARE

    @i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname,

    @schemaName sysname, @partitionNumber int, @partitionCount int,

    @sql nvarchar(4000), @edition int, @parmDef nvarchar(500), @allocUnitType nvarchar(60),

    @indexType nvarchar(60), @online bit, @disabled bit, @dataType nvarchar(128),

    @charMaxLen int, @allowPageLocks bit, @lobData bit

    SELECT @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

    SELECT

    IDENTITY(int, 1, 1) AS FragIndexId,

    [object_id] AS ObjectId,

    index_id AS IndexId,

    avg_fragmentation_in_percent AS FragPercent,

    record_count AS RecordCount,

    partition_number AS PartitionNumber,

    index_type_desc AS IndexType,

    alloc_unit_type_desc AS AllocUnitType

    INTO #FragIndex

    FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)

    WHERE

    avg_fragmentation_in_percent > @minFragPercent AND

    avg_fragmentation_in_percent < @maxFragPercent AND

    index_id > 0

    ORDER BY ObjectId

    -- LIMITED does not include data for record_count

    IF @statsMode IN ('SAMPLED', 'DETAILED')

    DELETE FROM #FragIndex

    WHERE RecordCount < @minRowCount

    SELECT @i = MIN(FragIndexId)

    FROM #FragIndex

    SELECT

    @objectId = ObjectId,

    @indexId = IndexId,

    @partitionNumber = PartitionNumber,

    @indexType = IndexType,

    @allocUnitType = AllocUnitType

    FROM #FragIndex

    WHERE FragIndexId = @i

    WHILE @@ROWCOUNT <> 0

    BEGIN

    SET @sql = '

    SELECT @objectName = o.[name], @schemaName = s.[name]

    FROM [' + @dbName + '].sys.objects o

    JOIN [' + @dbName + '].sys.schemas s

    ON s.schema_id = o.schema_id

    WHERE o.[object_id] = @objectId'

    SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

    EXEC sp_executesql

    @sql, @parmDef, @objectId = @objectId,

    @objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

    SET @sql = '

    SELECT @indexName = [name], @disabled = is_disabled, @allowPageLocks = allow_page_locks

    FROM [' + @dbName + '].sys.indexes

    WHERE [object_id] = @objectId AND index_id = @indexId'

    SET @parmDef = N'

    @objectId int, @indexId int, @indexName sysname OUTPUT,

    @disabled bit OUTPUT, @allowPageLocks bit OUTPUT'

    EXEC sp_executesql

    @sql, @parmDef, @objectId = @objectId, @indexId = @indexId,

    @indexName = @indexName OUTPUT, @disabled = @disabled OUTPUT,

    @allowPageLocks = @allowPageLocks OUTPUT

    IF @indexType = 'CLUSTERED INDEX'

    BEGIN

    -- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml

    SET @sql = '

    SELECT @lobData = 1

    FROM [' + @dbName + '].INFORMATION_SCHEMA.COLUMNS c

    WHERETABLE_NAME = @objectName AND

    (DATA_TYPE IN (''text'', ''ntext'', ''image'') OR

    CHARACTER_MAXIMUM_LENGTH = -1)'

    SET @parmDef = N'@objectName sysname, @lobData bit OUTPUT'

    EXEC sp_executesql

    @sql, @parmDef, @objectName = @objectName, @lobData = @lobData OUTPUT

    END

    SET @sql = '

    SELECT @partitionCount = COUNT(*)

    FROM [' + @dbName + '].sys.partitions

    WHERE [object_id] = @objectId AND index_id = @indexId'

    SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'

    EXEC sp_executesql

    @sql, @parmDef, @objectId = @objectId, @indexId = @indexId,

    @partitionCount = @partitionCount OUTPUT

    -- Developer and Enterprise have the ONLINE = ON option for REBUILD.

    -- Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:

    -- disabled indexes, XML indexes, indexes on local temp tables, partitioned indexes,

    -- clustered indexes if the underlying table contains LOB data types (text, ntext, image, varchar(max),

    -- nvarchar(max), varbinary(max) or xml), and

    -- nonclustered indexes that are defined with LOB data type columns.

    -- When reoganizing and page locks is disabled for the index, we'll switch to rebuild later on,

    -- so we need to get setup with the proper online option.

    IF @edition = 3 AND (@defragType = 'REBUILD' OR (@defragType = 'REORGANIZE' AND @allowPageLocks = 0))

    BEGIN

    SET @online =

    CASE

    WHEN @indexType = 'XML INDEX' THEN 0

    WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0

    WHEN @lobData = 1 THEN 0

    WHEN @disabled = 1 THEN 0

    WHEN @partitionCount > 1 THEN 0

    ELSE 1

    END

    END

    ELSE

    SET @online = 0

    SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +

    @schemaName + '].[' + @objectName + '] ' +

    CASE

    WHEN @defragType = 'REORGANIZE' AND @allowPageLocks = 0 THEN 'REBUILD'

    ELSE @defragType

    END

    IF @online = 1

    SET @sql = @sql + ' WITH (ONLINE = ON)'

    IF @partitionCount > 1 AND @disabled = 0 AND @indexType <> 'XML INDEX'

    SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

    PRINT @SQL

    --EXEC (@SQL)

    SELECT @i = MIN(FragIndexId)

    FROM #FragIndex

    WHERE FragIndexId > @i

    SELECT

    @objectId = ObjectId,

    @indexId = IndexId,

    @partitionNumber = PartitionNumber,

    @indexType = IndexType,

    @allocUnitType = AllocUnitType

    FROM #FragIndex

    WHERE FragIndexId = @i

    END

  • The .BAK backup file should get smaller as the data in the database gets smaller (free space increases), even if the overall size of the .MDF remains the same.

    How much free space do the different databases have ?

    How large are the different .LDF files ?

  • Can somebody help me with a command syntax here please ?

    I know there is a command where you just specify only a table name and then SQL server

    will rebuild all the indexes for that particular table ?

    I think the syntax looks like the following:

    ALTER INDEX ALL ON patient_encounter REBUILD

    Please confirm

  • This is from Books OnLine 'ALTER INDEX ':

    ALTER INDEX ALL ON Production.Product

    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

    STATISTICS_NORECOMPUTE = ON);

    How will this help your backups ?

  • Good question

    See the steps below

    1.) I am going to delete most of the data from the tables and have only 10 customers

    This will cut the size of data in the database.

    2.) When I run the "ALTER INDEX ...... REBUILD" commad on each table that will cut the

    size of the index ( Rebuillding an index will have to get rid of data that was deleted )

    So this means we made the indexes small.

    After completing steps 1 and 2 you can create a new backup file.

    So then you can expect the size of the BAK file to be small ?

    Am I right ?

  • What recovery model is your database ? How big is the log file ?

    What is the result of this ?

    USE MyDatabase

    GO

    SELECT DB_NAME() AS DbName,

    name AS FileName,

    size/128.0 AS CurrentSizeMB,

    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

    FROM sys.database_files;

  • These are the stats I got from the real prod database ( I did not do any shrinking yet on this one )

    NGProdNextGen_System_Data308.5000007.937500

    NGProdNextGen_Log 11962.06250011923.859375

    NGProdNextGen_Index_1 60492.31250017741.125000

    NGProdNextGen_Core_Data_183719.18750012532.687500

  • Let me ask you a question here.

    Why are you dividing by 128 ?

    I thougt you divide by 1024 to convert to megabytes

    ( or you can divide it by 1024*1024 to get the answer in gigabytes )

    Please explain

  • if your table is a HEAP, the space would not be released automatically.

    to release the space, I guess you can create a clustered index, which will re-organise all the data and thus remove all the holes that were generated when you deleted the data. Then if you want to, you can delete the clustered index

  • So your Prod backup should be about 114 G

    ".. Why are you dividing by 128 ?.."

    BOL: size, int, Current size of the file, in 8-KB pages.

Viewing 15 posts - 1 through 15 (of 43 total)

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