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