Solved

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

Posted on 2012-04-03
2
339 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 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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