September 29, 2008 at 1:03 pm
I have a database the I back up which is made up of 1 Primary file and 1 transaction log file. Is there a way to restore the backup to a database on a different server that has multiple files ( 3 files for the PRIMARY and 1 for the transaction log)?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 29, 2008 at 1:42 pm
Just to clarify, you currently have a database named ABC on Server 1 that is contained in the Primary filegroup. You want to take a backup of ABC database from Server 1 and restore it to Server 2, which has the same ABC database, but has 3 filegroups? Am I understanding this correctly?
Fraggle
September 29, 2008 at 1:45 pm
Exactly.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 29, 2008 at 1:51 pm
No. The restore will restore the DB with the same number of files and filegroups as it had when it was backed up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2008 at 1:55 pm
No.
Restore performs its action to the same number of files as was the case for the original database. (the one you backed up)
It even needs the same space requirements to host the original database !
(filesizes !)
The only thing you can do is reroute the file locations.
Check Restore database in BOL.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 29, 2008 at 2:01 pm
Then this sounds like its going to lead me down the path of file groups. I have a 590GB PRIMARY data file along with a 30GB t-log. I need to be able to break the database up into appropriate file groups across disk fro performance and backup rtime reduction. Is there a way I can do this online or does the database need to be down and what would be the best way to do this?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 29, 2008 at 2:02 pm
What ALZDBA said above is correct.
However, I would venture to say that you could, if you truely desired, to create a script that would do it. I would wonder though why you you have it setup this way.
Fraggle
September 29, 2008 at 2:09 pm
I did not do it. I am just coming in and this is what was in place when I got here.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 29, 2008 at 2:17 pm
You could add new filegroups and move your data to the appropriate filegroup by moving the clustering indexes.
Also keep in mind you may also have to move non clustering indexes.
These actions will interfere with your ongoing applications !
After you've moved all objects you wanted to, best is the shrink the current primary filegroup ! (or your restores will still need the space !)
This shrink operation will also interfere with ongoing operations !!!
Plan it during downtime(s).
Prepare, plan, and start with a full backup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply