Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup .bak to My PC Local drive? Expand / Collapse
Author
Message
Posted Monday, May 17, 2010 2:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 4:41 PM
Points: 19, 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

Post #923126
Posted Monday, May 17, 2010 2:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 2,717, Visits: 2,335
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.
Post #923136
Posted Tuesday, May 18, 2010 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:18 AM
Points: 16, 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
Post #923558
Posted Tuesday, May 18, 2010 8:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:17 AM
Points: 31,278, Visits: 15,731
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
Post #923649
Posted Tuesday, May 18, 2010 10:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 4:41 PM
Points: 19, 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!
Post #923703
Posted Tuesday, May 18, 2010 10:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:17 AM
Points: 31,278, Visits: 15,731
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
Post #923727
Posted Wednesday, April 30, 2014 5:58 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:03 PM
Points: 4,451, Visits: 3,899
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
Post #1566336
Posted Wednesday, April 30, 2014 10:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:31 AM
Points: 706, Visits: 1,752
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.



Post #1566498
Posted Wednesday, April 30, 2014 10:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 AM
Points: 12,923, Visits: 32,282
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1566502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse