August 16, 2017 at 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 🙁
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
August 16, 2017 at 2:48 am
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.
Larnu.uk
August 16, 2017 at 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
August 16, 2017 at 4:57 am
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
August 16, 2017 at 5:44 am
aaron.reese - Wednesday, August 16, 2017 4:46 AMwould 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.
Larnu.uk
August 16, 2017 at 8:39 am
aaron.reese - Wednesday, August 16, 2017 2:24 AMI 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 🙁
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.
August 16, 2017 at 1:46 pm
Are you using compression on the backup?
Joie Andrew
"Since 1982"
August 16, 2017 at 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.
August 16, 2017 at 2:46 pm
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.
August 16, 2017 at 5:54 pm
Arsh - Wednesday, August 16, 2017 1:56 PMSee 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! 😉 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
Change is inevitable... Change for the better is not.
August 16, 2017 at 11:09 pm
Arsh - Wednesday, August 16, 2017 1:56 PMSee 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.
And worthless, seeing as when you run the numbers, his backup file is roughly the same size as the database, indicating that there's little to no free space.
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
August 31, 2017 at 7:35 am
GilaMonster - Wednesday, August 16, 2017 11:09 PMArsh - Wednesday, August 16, 2017 1:56 PMSee 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.And worthless, seeing as when you run the numbers, his backup file is roughly the same size as the database, indicating that there's little to no free space.
Gail and Jeff , agreed totally. Thank u for correction. Jeff , by fragmentation after shrinking , do you mean those files that store indexes or any data files ?
August 31, 2017 at 2:22 pm
Arsh - Thursday, August 31, 2017 7:35 AMGilaMonster - Wednesday, August 16, 2017 11:09 PMArsh - Wednesday, August 16, 2017 1:56 PMSee 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.And worthless, seeing as when you run the numbers, his backup file is roughly the same size as the database, indicating that there's little to no free space.
Gail and Jeff , agreed totally. Thank u for correction. Jeff , by fragmentation after shrinking , do you mean those files that store indexes or any data files ?
They're normally one and the same as most folks keep both in the MDF file.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply