Solved

Restore multi-filegroup database to a new location and use only one filegroup? (sql2008r2)

Posted on 2012-04-03
2
342 Views
Last Modified: 2012-04-04
SQL 2008r2 database

This database has some tables in one filegroup, some in another filegroup.  Different filegroups are on different drives.

I have a database backup (bak) file of that database and I want to restore it somewhere else but I don't want to care about drives, filegroups or files.

I just want to restore all the tables in one place (in one database) so I can see the data.

Is there a way to do this easily?
0
Comment
Question by:gateguard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37803969
From an existing backup, you will have to keep the existing filegroups during the restore.

But you can restore all filegroups to the same folder, even if it's different than any of the original folders.

RESTORE DATABASE <db_name>
FROM DISK = 'x:\path\to\backup\file\backup.BAK'
WITH
    MOVE 'logical_filename_1' TO 'y:\path\to\datafile\datafile1.mdf',
    MOVE 'logical_filename_2' TO 'y:\path\to\datafile\logfile.ldf',
    MOVE 'logical_filename_3' TO 'y:\path\to\datafile\datafile2.ndf',
    MOVE 'logical_filename_4' TO 'y:\path\to\datafile\datafile3.ndf',
    ....

You can get the logical file names from this command:

EXEC [db_name].dbo.sp_helpfile

The first column contains the logical file names.
0
 

Author Closing Comment

by:gateguard
ID: 37806092
Thanks!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question