Restore database without overriding Auto growth

Posted on

Question :

I created a database with auto growth 200MB. After restoring database auto growth changed to 1MB. Because my Backup file database auto growth is 1MB.

Is there a way to restore without overriding the auto growth ?

Answer :

I just tested this in SQL 2016 (13.0.4001.0). Restore retain the auto-growth setting of the database (for which backup was taken). Here is the code I tested with.
Create database

CREATE DATABASE [AutoGrowTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AutoGrowTest', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAutoGrowTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'AutoGrowTest_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAutoGrowTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

Change auto-growth

USE [master]
GO
ALTER DATABASE [AutoGrowTest] MODIFY FILE ( NAME = N'AutoGrowTest', SIZE = 262144KB , FILEGROWTH = 131072KB )
GO

enter image description here
Backup database

BACKUP DATABASE [AutoGrowTest] TO  DISK = N'C:AutoGrowthTestFull.bak' WITH NOFORMAT, NOINIT,  NAME = N'AutoGrowTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore with diff name

USE [master] 

RESTORE DATABASE [AutoGrowTest_restore] FROM DISK = N'C:AutoGrowthTestFull.bak' 
WITH FILE = 1, move N'AutoGrowTest' TO 
N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAutoGrowTest_restore.mdf'
, move N'AutoGrowTest_log' TO 
N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAutoGrowTest_restore_log.ldf'
, nounload, stats = 5 

GO

It retained auto-growth setting of original database.

enter image description here

Please note auto-growth setting changed in SQL 2016 than what is mentioned in some comments and answers.

I created a database with auto growth 200MB. After restoring database
auto growth changed to 1MB. Because my Backup file database auto
growth is 1MB.

The initial settings for auto-growth are set to the default values when defining a new database. These defaults are established by using the auto-growth settings on the model database files.

The default auto-growth setting for the data file is 1 MB with unrestrictive growth, and the log file is set to grow by 10% with unrestricted growth. If you haven’t tweaked your model database settings then you will have the same default auto-growth setting.

For You Ref SQL Server Database Growth and Autogrowth Settings

Leave a Reply

Your email address will not be published.