Backup .bak to My PC Local drive?

Question

Backup .bak to My PC Local drive?

JohnR

Right there with Babe

Points: 747
More actions
May 17, 2010 at 2:21 pm

#224526

Backup .bak to My PCs Local drive? Instead of to the SQL Server's Local drive. I know this has been discussed over the years, so I'm just wondering if SQL 2008 may offer something new in this area. I know about the UNC method with excessive SQL service rights, but that's not a doable solution for my end-user clients. I'm thinking maybe new parameter that would send the bits back over the network, save to local bak file, example parameter "BITS-To-Client"
BACKUP DATABASE MYDB TO DISK='C:\MYDB.bak' WITH INIT, BITS-To-Client

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DNA_DBA SSCrazy Eights Points: 9107 More actions · Answer 1

This is not possible, you are executing the backup command on the server so it will go to disks that are local to the server or to a unc path relative to the server. You could put the backups in a shared folder on the server and allow access to certain people from their desktops.

chandu.ade Mr or Mrs. 500 Points: 508 More actions · Answer 2

You can try with Litespeed. This is 3rd party backup tool where you can store your .bak file on any network dirve.

Chandu

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Litespeed doesn't change the fact the server is doing the backup.

You are going to be copying data from a location the server can see to the client's disk. That can happen with an intermediate location or not. What you could do is copy to a shared location both can see and script a file copy from the client's machine.

JohnR Right there with Babe Points: 747 More actions · Answer 4

Thanks for the replies.

I was just wishful thinking for an easier way to get a copy of our end-user clients database. Your reply confirmed what we sort of expected to still be the case.

We really don't have any access to the SQL Server for most of our clients, including access to their SQL server's service rights management, so saving .bak from SQL to network is not going to happen.

Our story if you're curious;

We are a commercial software shop, big app with SQL backend, our clients are the end-users, not their IT folks. Sometimes we need to get a copy of client database to troubleshoot issues the end-user client is reporting during support calls to us. We wrote a report in our application which scripts all the table's data, and then the client uploads the resulting ZIP file it to us. This solution continues to work ok for us, but it frequently takes 3-4 hours to create the script. We know a .BAK file takes a fraction of the time and would be a quicker solution and less effort for the end-users.

Again, thanks for the replies. SQLServerCentral is the best!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Can you not script (with VBScript/Powershell/SMO) something that runs a backup for the client and uploads/emails the bak?

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

JohnSQLServerCentral (5/18/2010)
We wrote a report in our application which scripts all the table's data, and then the client uploads the resulting ZIP file it to us. This solution continues to work ok for us, but it frequently takes 3-4 hours to create the script. We know a .BAK file takes a fraction of the time and would be a quicker solution and less effort for the end-users.

Personally, when I have to copy a database, I always use the backup...restore approach. Not only does the script approach usually take a lot longer, but it also misses things (depending on your script) like identity values, indexes, free space, etc. The backup catches all this. The backup itself could be automated, but please make sure you include the copy_only option. If you have to do a point-in-time recovery and don't have the full backup available, you're out of luck. The copy_only option says to not mark the transaction log entries as having been backed up, so running the backup won't interfere with any later recovery.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

krypto69 SSChampion Points: 13591 More actions · Answer 7

Maybe I don't understand your needs..but couldn't you create an SSIS package to create the bak then move it to wherever you want in the package.

You could then write a quick front end in VB or C that calls the package from whichever client workstation you wish.

I've done something similar..works well.

Lowell SSC Guru Points: 323504 More actions · Answer 8

there's an exisitng option for the COPYTO='\\jons-laptop\SharedFolder\SQLBackups\' that is already a part of Native Sql backups,as well as litespeed and redgate backups.

can't you just add that to your backup command, after you create the proper share to your local machine?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!