October 21, 2014 at 7:33 am
Hi, I did a backup of a database on SQL2008R2, databasename.bak and need to restore it to another server with the same SQL version. But it is required for the backup to be restored in different files of filesname like below,
Filename FileGroup
F:\MSSQL\Data\databasename_sysdata.mdf PRIMARY
F:\MSSQL\Data\databasename_data.ndf databasename_data
F:\MSSQL\Data\databasename_index.ndf databasename_index
F:\MSSQL\Data\databasename_text.ndf databasename_index
G:\MSSQL\Data\databasename_log.ldf not applicable
Any idea how I can divide/partition the backup to restore it into these different files and groups aforementioned and to different drive? We normally only restore the backup as is and not divide it up like these, which is supposed to be better, but I don't see any option in the restore backup to allow to do this. any inputs would be greatly appreciated.
October 21, 2014 at 7:46 am
Not possible
A restore recreates the database exactly as it was at the time of the backup. You'll have to do that in two parts, restore first, then split it up into the files and filegroups that you want.
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
October 21, 2014 at 8:46 am
Gail is correct, you need to restore things as they were. You can move drives/folders, but not files.
Is this a one time thing or do you do this often? If often, you might build a script that you save to do this. What you'd want to do is
- restore like this
- add new files as you want them
- move objects with index rebuilds or drop/create to the new files
- emptyfile and drop the old files.
Are you looking to create new filegroups or just reorganize the files you have in a different way?
October 22, 2014 at 7:02 am
Thank you both Gail and Steve for your response. This is not a one-time thing, we have to do this at every few months or whenever needed, so definitely, I would need to write a script to automate this as much as I can. But I am not clear on what you said about splitting the files AFTER the restore. Do you mean after the restore, since I don't have the script yet, I can script out the data, index from the SSMS UI >> databasename > tasks > generate scripts? But from looking at the current database structure, the database was restored by each of these files, so that is where I am confused and stuck.
October 22, 2014 at 7:21 am
Huh?
After the restore you can add more files and filegroups, move the data around, that kind of thing. Nothing to do with generating scripts.
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
October 22, 2014 at 9:18 am
Correct, as Gail mentioned, no script generation, or at least, not in one stage.
Here's what I'd do.
Set up the restore in the GUI, moving paths/folders as needed. Don't click "OK", click "Script" and save that.
Run that.
Once that's done, now plan out your changes to files. I'd again, do this in the DB properties dialog, make the changes and script them out. Now save that script, run it, and add it to your "restore" script.
Now you will need to move the objects from old filegroups/files to the new ones. As you rebuild indexes or drop/create procs, save those items as part of your scripts. Note this is a point of maintenance. As you add objects, you'll be modifying this part of the script.
Note that if you can start to do this in your source system, rearranging objects into filegroups there that you can restore and move here, it will get easier over time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply