[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-04-03
2
Medium Priority
?
345 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
2 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

830 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