Restore dabase with filegroups | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restore dabase with filegroups

Hi !
If i have db with mdf and ndf files and i would like to restore it to
other server should i first create db with mdf and ndf files and then restore or
only restore?
Simply take full backup from this server and use RESTORE on target server which will create the database with required itself. Its not possible to do so using SQL 6.5 version. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
With restore you can get your db in new server.
If performance is important, after restore, run update statistics.
Luis Martin
Moderator
SQL-Server-Performance.com
Thank you.
I actually started restore before asked this question
but then I looked at data drive and notice that only mdf file is created
so I started to be consern if i am doing this correctly. We have 1 mdf and 2 ndf files but we keep them all at one drive.
That make me think what the point to have ndf I always thought that ndf needed
to separate part db to other drive am I wrong?
BOL specifies:
This example restores a full database and transaction log and moves the restored database into the C:program FilesMicrosoft SQL ServerMSSQLData directory. RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE ‘MyNwind’ TO ‘c:program FilesMicrosoft SQL ServerMSSQLDataNewNwind.mdf’,
MOVE ‘MyNwindLog1’ TO ‘c:program FilesMicrosoft SQL ServerMSSQLDataNewNwind.ldf’
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH RECOVERY Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
There is no benefits (except to keep data separate from index to easy maintenance) to have ndf in same drive.
If you can have ndf in other drive and channel, then you will have more peformance.
Luis Martin
Moderator
SQL-Server-Performance.com
how do i check what exactly being kept on those ndf?
You can see exactly what ndf had, using the following script: select si.rows as ‘Rows’, SO.Name as Tabla, SI.name as ‘Index’, SFG.groupname as ‘Filegroup’
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId order by SO.Name , SI.name, SFG.GroupName Luis Martin
Moderator
SQL-Server-Performance.com
Okey :have to correct myself We have 1 filegroup Primary but 3 files: 1mdf and 2 ndf all at the same drive.
Log file however stay on separate drive What benefits do we have from that?
I funully restored that db. But instead of 1 mdf and 2 ndf get 3 mdf.
how it is possible?
Firstly, SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions are recommended to help identify the use of the file. Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf. SO when you restored the database it redefined those files as .MDF and you need not worry about file extension. As long as data is restored and database is working fine leave it as it is. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thank you I restored that db once again manually with Move options
to rename mdf to ndf Just in case
]]>