January 23, 2015 at 7:27 am
Is it possible to store a SQL backup file in the database? I've never actually worked with BLOBs or file storage and just wanted to check if it was even a valid file type to store. The file is a little over 2 gigs.
January 23, 2015 at 7:29 am
The size will be the problem. The file type isn't, SQL doesn't care at all what goes into a varbinary(max), it's just binary data as far as the database engine is concerned. The max size however is 2.1GB
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
January 23, 2015 at 8:26 am
UGH!!! So close!!!
I think my file is 2.2 gigs.
Now I just gotta figure out how to do it... 🙂 I guess, first step, is create a table and put the right field data type in for the file?
Then, how do you load the file into the table? Is it like a bulk insert statement? I also need to figure out how exactly you get it back out to "download" it after its in there...
January 23, 2015 at 8:39 am
Filestream would do it. No 2GB limit.
Loading would be like an image or other file. Essentially you stream it in. Stream it out. Like an image file. Any solution that lets you load an image would work for the .bak file.
January 23, 2015 at 8:41 am
Some info here: http://www.sqlservercentral.com/articles/FILESTREAM/67668/
Also, a free book we have: http://www.sqlservercentral.com/articles/books/94934/
January 23, 2015 at 8:45 am
Other question is why?
Generally for backups you want the storage as simple as possible so that retrieving the files for a restore is as easy as it can be made. Why dump them into a database which itself then should be 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
January 23, 2015 at 8:53 am
GilaMonster (1/23/2015)
Other question is why?Generally for backups you want the storage as simple as possible so that retrieving the files for a restore is as easy as it can be made. Why dump them into a database which itself then should be backed up?
+1000
For whatever reason you think this is a good idea, think again.
-- Gianluca Sartori
January 23, 2015 at 8:55 am
Ugh, don't ask why... its a horrible nightmare. It won't be in production or anything or nothing I'm doing as a "real solution". I also will not be taking backups of this backup file... I'm not that silly. 🙂
I basically am trying to get our backup file off production and onto our local machines to do a refresh of our dev environments. I used to have a method to do it but once the file got over 2 gigs, I can no longer move the file and finding an alternative method is proving to be, as I said, a nightmare. 😉
Thanks Steve, I will check that out!
January 23, 2015 at 9:31 am
If moving the backup file due to a 2GB limit is the problem, could you backup the database to more than one file. Then move each file independently?
Or, fix the actually problem with moving files over 2GB. Is it network performance, or a security setting?
January 23, 2015 at 9:51 am
No, none of those issues...
How do you backup the database to more than one file? I did not see that option in the Backup wizard...
January 23, 2015 at 10:22 am
Multiple files are striped backups - http://www.sqlservercentral.com/articles/Video/65295/
January 23, 2015 at 10:52 am
Oh my gosh! I can't believe I've never heard of or knew I could do that.
THAT is perfect!
I assume when you restore it, you just add those two files to the restore screen and poof... restored?
January 23, 2015 at 3:33 pm
Yep, but you need both files. If you lose one, then no restores.
January 26, 2015 at 1:28 am
amy26 (1/23/2015)
I basically am trying to get our backup file off production and onto our local machines to do a refresh of our dev environments. I used to have a method to do it but once the file got over 2 gigs, I can no longer move the file and finding an alternative method is proving to be, as I said, a nightmare. 😉
Option 1, stripe the backup, as discussed above.
Use this only if you're taking special backups for the refresh to dev (if you are, use the COPY_ONLY option too), as otherwise you're making your restore more complex which is the last thing you want in a disaster
Option 2, use something like WinRar to split the backup into multiple compressed files, then on dev use the same tool to extract from the compressed files.
For more robust copy than the standard explorer drag/drop, have a look at Robocopy.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply