Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-04-03
2
Medium Priority
?
344 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 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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