2

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 ?

  • Can you repeat this with a fresh backup and confirm? When you restore from a backup it should use whatever file settings the db had at the time the backup was taken. – Sir Swears-a-lot Apr 6 '17 at 19:24
3

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 Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'AutoGrowTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_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 Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore.mdf'
, move N'AutoGrowTest_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_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.

1

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

  • 2
    However... if you restore from a backup the file size and properties should be the same as set for the user db. Not what is set for the model. – Sir Swears-a-lot Apr 6 '17 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.