none
creating read-olny database with no free-space in files RRS feed

  • Question

  • Dear all, we need to create a readonly copy of a database from an actively used database with a lot of free space in its files.

    We wanted to use backup/restore, but are open to other solutions.

    Obviously, we want the backup to be as compact as possible, because we need to send it through the network, and we do not want any of the free space in the read-only database files, because that data will never be changed. What is our best option? We are on SQL Server 2012 and 2014

    Wednesday, July 10, 2019 8:02 PM

Answers

  • You can back up the database after shrinking the database file locally. If your database is in a production environment, I still don't recommend it. So it is probably the best way to increase disk space.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 12, 2019 3:13 AM
  • I guess there is no restore option to restore without free space.

    Right, there is no such option. A restore is always an exact 1:1 copy of the origin database incl. file size and if you don't have enough free space then you can't restore the database.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 12, 2019 5:57 AM
    Moderator

All replies

  • Restore a database with a new name from the backup. Set the new database to read only. Shrink that database to release the free space.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, July 10, 2019 8:40 PM
  • Hi Olka Molka,

     

    >>we want the backup to be as compact as possible, because we need to send it through the network, and we do not want any of the free space in the read-only database files, because that data will never be changed.

     

    You could use Backup Compression:

     BACKUP DATABASE [TESTDB] TO DISK = 'C:\backups\TESTDB.bak' WITH COMPRESSION

     

    After restoring on another instance, you could set it read-only:

    USE [master]

    GO

    ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT

    GO

     

    Finally try to shrink file:

    DBCC shrinkfile(TESTDB)

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 11, 2019 2:23 AM
  • Many thanks for your answer. We ran into a trouble restoring the initial size, because there is not enough space.

    And we were told, I do not know, how true the statement is, we cannot attach the space required (around 3TB). 

    I guess there is no restore option to restore without free space. Would there be any tool, which does backups/restores, which offer this?

    Thursday, July 11, 2019 9:57 PM
  • Many thanks for your answer. We ran into a trouble restoring the initial size, because there is not enough space.

    And we were told, I do not know, how true the statement is, we cannot attach the space required (around 3TB). 

    I guess there is no restore option to restore without free space. Would there be any tool, which does backups/restores, which offer this?



    Thursday, July 11, 2019 10:00 PM
  • You can back up the database after shrinking the database file locally. If your database is in a production environment, I still don't recommend it. So it is probably the best way to increase disk space.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 12, 2019 3:13 AM
  • I guess there is no restore option to restore without free space.

    Right, there is no such option. A restore is always an exact 1:1 copy of the origin database incl. file size and if you don't have enough free space then you can't restore the database.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 12, 2019 5:57 AM
    Moderator