November 29, 2015 at 8:09 am
I need to restore a backup i received but dont have enough space on my machine. the purpose of the restore is for testing purposes so I can be a bit creative and dont need to the database for production/transactional purposes.
My understanding is that i cant do anything to restore this backup by using less memory so I have to go back to the source for another backup, right? If so, how can I get a smaller backup, what can they do to give me a smaller database that would fit on my machine..? Please note I just need the data (table records) from this database for reporting purposes so any tricks not leaving out unnecessary functionality/information so as to reduce the size?
Thanks in advance for your recommendations to reduce the backup size
Eric
November 29, 2015 at 8:35 am
When you restore a backup, it recreates the database exactly as it was at the time it was backed up. It's not the size of the backup that's going to stop you from restoring, it's the size of the original database.
You could restore it somewhere that has space (external drives aren't that expensive), shrink and move the files or shrink, backup again and then restore to the space-limited location.
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
November 29, 2015 at 8:37 am
Hello and welcome to the forums.
Your understanding is correct. If you need the data in the database, you must have enough space on the target machine to be able to restore (and use) the data. You could, when restoring the database, move the data and log files to different drives that have adequate free space. Another option would be to add another drive. If you can get a smaller backup, that might be an option, but you have to ask yourself if it would give you the data you need. Even if your source compresses the backup, that only makes the backup smaller, not the data.
Whatever direction you take, the bottom line is that you need the space to store the data you need to use.
November 29, 2015 at 8:41 am
thanks for your reply. How do i restore to an external drive? I thought the restore location would need to have SQL server installed, no?
November 29, 2015 at 8:50 am
Thanks Ed. I would like to try the option you suggested in trying to restore files in different drives..Can you explain a bit further? When i restore, I see five options: dat, images, messages, Files and log...any of these can be restored in separate drives?
November 29, 2015 at 8:58 am
Plug the external drive into the machine that has SQL on, restore to the external drive.
Hang on..
dat, messages, images??? Is this a SQL Server database you're restoring (because SQL databases don't have those things)
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
November 29, 2015 at 9:06 am
SQL Server needs to be installed on the system you're using to restore, but it does not have to be installed on the drive you want to restore to. For example, I have a database server with SQL Server installed on a local drive. The data files are on a SAN volume. The log files are on a different SAN volume. Neither of these SAN volumes have SQL Server on them - they're just there to store the data and log files.
When you fire your RESTORE DATABASE command, you can use the MOVE option to relocate files to different locations. The option is there to do exactly what you need to do. The command is covered at https://msdn.microsoft.com/en-us/library/ms186858%28v=sql.110%29.aspx.
November 29, 2015 at 9:11 am
Thanks Gila, I had no idea that external drive restore was an option..I would like to give it a shot
Here is a screenshot of my restore options. Do I check the "relocate all files to folder" and change path to external drive OR do i change the "Restore as" path to external drive?
November 29, 2015 at 1:12 pm
You can use the 'relocate all files' if there's enough space on the external for all the files. If there isn't, you'll have to edit the paths one by one.
And this should be temporary. Restore, shrink (or delete data then shrink) and then move the files back to the internal drive. Otherwise when the external gets unplugged, the DB will be unavailable.
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
November 29, 2015 at 2:52 pm
There's enough space on external so i m doing all. But why is this a temporary solution, is there a problem with leaving it on external and keep it plugged in?
>>"shrink (or delete data then shrink) "
If I cant delete any table/records, what are my options of reducing the db size?
>>"and then move the files back to the internal drive"
If i simply copy&paste files to internal drive will sql server automatically repoint to new location or i have to change something under Databases?
November 29, 2015 at 2:57 pm
titsiros (11/29/2015)
But why is this a temporary solution, is there a problem with leaving it on external and keep it plugged in?
None, until it gets accidentally unplugged, dropped, borrowed for other use, etc. Plus, unless it's a USB 3 drive, it's going to be slower than internal drives
If I cant delete any table/records, what are my options of reducing the db size?
None, unless there's already a lot of free space in the files
If i simply copy&paste files to internal drive will sql server automatically repoint to new location or i have to change something under Databases?
You have to tell SQL where the files went. Look up ALTER DATABASE command.
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
November 29, 2015 at 3:17 pm
ok thanks for your help )
December 1, 2015 at 4:35 pm
If you go with an external drive, remember that you'll have to take the database offline or detach it in SSMS for SQL Server to properly close the database files before you eject the drive, otherwise you're likely to blow up your restored copy. That would be no fun.
Personally I'd ask your IT people to install a second hard drive in your computer, desktop HDs are cheap. How big of a restore are you looking at?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply