Restore database preserving existing file/filegroup structure

Question

Hi all,

SQL Server 2008R2 SP1. I've a database, let's say "MyDB" this database is mounted in two different server: a development machine and a production machine. MyDB has different file/filegroup structure on the 2 machine for example on the development machine I've 3 datafile, In production I've 4 datafile.

Now I've to backup MyDb on development and restore into production. When I perform this operation is there a way to preserve file/filegroup strucutre existing on production? Have I to recreate them in a secound time?

Answer 1

I do not think so

CREATE DATABASE [dev] ON  PRIMARY 
( NAME = N'dev', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dev.mdf' , SIZE = 3072KB , FILEGROWTH = 10%), 
( NAME = N'dev1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dev1.ndf' , SIZE = 3072KB , FILEGROWTH = 10%), 
( NAME = N'dev2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dev2.ndf' , SIZE = 3072KB , FILEGROWTH = 10%)
 LOG ON 
( NAME = N'dev_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dev_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

CREATE DATABASE [prod] ON  PRIMARY 
( NAME = N'prod', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\prod.mdf' , SIZE = 3072KB , FILEGROWTH = 10%), 
( NAME = N'prod1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\prod1.ndf' , SIZE = 3072KB , FILEGROWTH = 10%), 
( NAME = N'prod2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\prod2.ndf' , SIZE = 3072KB , FILEGROWTH = 10%),
( NAME = N'prod3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\prod3.ndf' , SIZE = 3072KB , FILEGROWTH = 10%)
 LOG ON 
( NAME = N'prod_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\prod_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO


USE dev
GO
CREATE TABLE t1_dev (c INT)
INSERT INTO t1_dev VALUES (1)


USE prod
GO
CREATE TABLE t1_prod (c INT)
INSERT INTO t1_prod VALUES (10000)

USE master
GO
BACKUP DATABASE dev TO DISK ='C:\dev.BAK' WITH INIT

GO
DROP DATABASE dev
GO
RESTORE DATABASE prod  FROM  DISK ='C:\dev.BAK' WITH REPLACE  


USE prod
SELECT * FROM t1_prod
----Msg 208, Level 16, State 1, Line 1
----Invalid object name 't1_prod'.

SELECT * FROM t1_dev


SELECT * FROM sys.master_files
WHERE database_id =db_id('prod')

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

The restore command will create the database first, with the same number of files (etc) as the original database (the one you took backup of). If you first create the destination database with different file structure, then the restore command will first transparently delete that database and then create as per above. I.e., you cann't change the "file layout" with backup/restore.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 3

No way to preserve the file struct.on Prod will restore with replace(overwrite). for reference u can script the prod database

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.