SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I restore a single file .mdf to multiple files spread across disks


Can I restore a single file .mdf to multiple files spread across disks

Author
Message
aaron.reese
aaron.reese
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10586 Visits: 1048
I am trying to create a 2nd copy of my database which is 200GB so that I can do Log Shipping (but on the same server).

The drive allocated for the data files has 385BG of free space and the log drive has 120GB. After doing the backup the data drive now only has 185GB - not enough for a restore Sad

Is there a way to restore the single file so that half of it is on DATA and half on LOG (this is a temporary UAT environment so will not make it to production)

Alternatively can I split the backup across DATA and LOG and then restore both files to DATA
Thom A
Thom A
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74280 Visits: 19182
Why not create your Backup on a different location to your server and restore from there instead? A separate SAN, a Network location, or even an External (USB) Drive would work fine for the task of doing a single restore.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
aaron.reese
aaron.reese
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10586 Visits: 1048
would love to but our network is screwed down so tight there isn't a drive I have access to that is big enough
GilaMonster
GilaMonster
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859452 Visits: 48575
A restore recreates the DB as it was at the time of backup. You can't split/merge or change files during a restore.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Thom A
Thom A
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74280 Visits: 19182
aaron.reese - Wednesday, August 16, 2017 4:46 AM
would love to but our network is screwed down so tight there isn't a drive I have access to that is big enough

External Media isn't an option?

Unfortunately, if you're being supplied a server with insufficient storage capabilities, the only option otherwise is to raise your concerns with your management. Maybe they can then make arrangements to have the storage amount increased or have the server be given access to an additional network share.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4603 Visits: 917
aaron.reese - Wednesday, August 16, 2017 2:24 AM
I am trying to create a 2nd copy of my database which is 200GB so that I can do Log Shipping (but on the same server).

The drive allocated for the data files has 385BG of free space and the log drive has 120GB. After doing the backup the data drive now only has 185GB - not enough for a restore Sad

Is there a way to restore the single file so that half of it is on DATA and half on LOG (this is a temporary UAT environment so will not make it to production)

Alternatively can I split the backup across DATA and LOG and then restore both files to DATA
The database files can only be worked on in their entirety as of now. Best option as others too have suggested , is to use external disk for the backup.

Joie Andrew
Joie Andrew
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23443 Visits: 2110
Are you using compression on the backup?

Joie Andrew
"Since 1982"
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4603 Visits: 917
See how much free space you have in the database. If it's huge and if it's sure that it won't be used for a longtime, you can consider shrinking and using that space . It should be the last option and would among the last to consider though.
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4603 Visits: 917
This option is not feasible as you want to setup log shipping meaning required space has to be more than double minimum which can't be obtained by freeing the unused space which won't be of that high percentage normally. so external disk looks a good option at the moment.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816242 Visits: 46257
Arsh - Wednesday, August 16, 2017 1:56 PM
See how much free space you have in the database. If it's huge and if it's sure that it won't be used for a longtime, you can consider shrinking and using that space . It should be the last option and would among the last to consider though.


Good lord, NO! Wink Not even as a last option. The type of fragmentation caused by shrinking an MDF requires that a defrag be done (it's not like the normal rather well behaved fragmentation you would get just from normal usage). The type of fragmentation done by a shrink WILL absolutely kill performance even for some of the small stuff. If you do index rebuilds to do the defrag, your MDF will grow by ~120% of whatever your largest index was... which could and probably will be the clustered index on your largest table. If that's a 30GB object, your MDF will likely grow by 36GB. If you do reorgs to fix the fragmentation, your log file could easily grow by that much or more depending on how often you do backups, etc.

There are ways to do a different type of "shrink" but, guess what? They also require extra disk space until all the data/index pages have been moved.

What really needs to happen is that Aaron's hardware group needs to work with him to make this possible. I know it's not free but buying extra hard disk space is a hell of a lot cheaper than spending a man-month two trying to figure out a solution that may simply fail or, worse yet, one that doesn't fail but isn't correct.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search