SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup .bak to My PC Local drive?


Backup .bak to My PC Local drive?

Author
Message
JohnSQLServerCentral
JohnSQLServerCentral
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 101
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
DNA_DBA
DNA_DBA
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3152 Visits: 2770
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
chandu.ade
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 229
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
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62643 Visits: 19111
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
JohnSQLServerCentral
JohnSQLServerCentral
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 101
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
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62643 Visits: 19111
Can you not script (with VBScript/Powershell/SMO) something that runs a backup for the client and uploads/emails the bak?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16655 Visits: 10070
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
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 2444
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28227 Visits: 39955
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search