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 (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 2781
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 (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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 (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81782 Visits: 19212
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 (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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 (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81782 Visits: 19212
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
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21736 Visits: 10311
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1502 Visits: 2457
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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35859 Visits: 40249
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