Solved

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

Posted on 2012-04-03
2
338 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:
ScottPletcher 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now