Hi everyone,
already tried this in other SQL forums, but maybe i have some luck here.
I need mainly to restore database backups from customers. They arrive in all kind of formats (zip, rar, gz). I'd like to be able to restore those directly from the compressed file, because i'm talking up to 7GB rar files which take a while to uncompress in a separate step.
I'm working for 6 years in R&D environments, but mostly on Linux/Oracle where this is an easy task using pipes, but i haven't found a sinlge web page, post or even script to do this with MSSQL. The VDI is not really what i'm looking for, so aren't backup software like SQLBackup, Litespeed etc. because i can't force the customer to use those.
Anybody any idea or even the same problem maybe with solution?
Thx.
Sql restore can only work with a file that it can read/understand. Zip/Rar is nothing something the engine understand so it will not be able to process the file. Sorry.|||Because database backups and restored are fired off from within the T/SQL command and not from the OS there isn't any way to pipe the command through a decompresser first. Sorry.
You could setup xp_cmdshell and use a DOS command to decompress the files before running the backup command. At least thay way you could simply run a single script and do all the work in a fire and forget setup.
|||Appreciate the replies, but that somehow doesn't solve my problems.
Maybe i should rephrase the question. How would you deal with moving large database across countries when it comes to the problem that time is money and you have neither one of it? I cannot belive i'm the only person in the world who has to move databases in R&D environments. Maybe i'm just after the wrong thing, but i can't think of anything except taking a backup to transfer a database.
|||i regularly transfer 4-5 gb database from my dev server in India to US server as a compressed file. But, before restoring i again unzip the file to normal format and then restore. I don't think sql server supports direct restore from any zip format
Madhu
|||You can backup then zip the file before transferring it to the remote site. However, on restoring the file must be unzipped first. This is by design as sql engine knows nothing of the zipped file.If you require your backup to be compressed, consider using 3rd party like Quest Litespeed or Red-gate Sql backup.
http://www.quest.com/litespeed-for-sql-server
http://red-gate.com/products/SQL_Backup/index.htm|||I would recommend against the third party backup compression tools which can compress and backup on the fly. This would introduce a rather hefty licensing requirement on your clients as well as your self when it can be simply zipped up in the current method.|||
When moving the database do you need all the data in the system?
Perhaps they can script out the database objects and send you just the SQL Script for the objects then you can load the database up with sample data. This would be a very small script at this point and would transfer easily.
Typically I try to aviod moving the entire database to development as this can take a lot of time, and as long as the structors are kept in sync all of the production data isn't needed. When a data refresh is needed I will usually export the table to a file then import it into the development system. Which then reduces the transfer time.
|||Thanks for all the ideas. The biggest problem is that we've more than one customer and that for customer support we need a copy of the entire database from time to time. Installing all the Backup products our customers may use is a huge license issue. Well i guess i just have to live with the fact that it takes a day to decompress a backup.
|||I'm using a product called HyperBac, which I think might help you. I used it because I wanted to compress my backups without changing my existing maintenance plans. It does that fine.
I actually compress the backups to ZIP format and provided I've got the product installed on the server to which I'm going to restore, it reads the file directly. It allows me to keep using the native SQL Server facilities, which gives me a sense of comfort. I haven't played with RAR format etc. but the vendor says that they can support that as well.
I came across the product after a presentation at the last SQL Server user group in Brisbane. You can look them up at www.hyperbac.com.
Hope this helps.
Andy
|||
mrdenny wrote:
I would recommend against the third party backup compression tools which can compress and backup on the fly. This would introduce a rather hefty licensing requirement on your clients as well as your self when it can be simply zipped up in the current method.
You're right if you're referring to LiteSpeed! I can't believe what those guys are asking for that product!
No comments:
Post a Comment