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
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.
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