Remote backup to local machine | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Remote backup to local machine

I have a database with 1 user and that user has been assigned db_backupoperator fixed db role.Now this user doesent have been assigned sysadmin or diskadmin fixed serv role.
Is it possible in any way to take backup of this database by user remotly into his local machine.say inside C:Backup .
Yes that user can backup the database, but be aware the backup might take longer time than performing it to a local drive. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
You want that or not?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

Satya I can understand that it will a time taking process.But I am wondering how can I do so.I have tried that but unable to do so.Though I can take the backup on the server remotly (as a dumpdevice is there in server on which sqlserver runs)but on my local machine I cant.
Neither I am unable to make my local disk as a dump device,cz the user with which I have log on doesnt have sysadmin or diskadmin fix server role. Can you please throw some light on this or if you have some ex ,script to do so.I will really appreciate your help.
We can do that at our end as one of my developer has similar privileges on a development box. Connect to that remote SQL server using query analyzer and execute
BACKUP DATABASE <dbnae> TO DISK=’\machinenamesharenamedbname.bak’ WITH INIT
. Explain your way to doing this or define any error you’re getting. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘\machinenameBackupdbname.bak’. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
I assume you replaced "machinename" with the name of the computer you want to backup to? Also you need to create a share on that machine, in this case called "Backup". Then the SQL Server Agent needs to be a running as domain users, say mydomainsqlservice Then you need to give this account rights on "machinname" both on folder and share level on the "backup" folder.
Yes I replaced the machinename with the ip of my machine.
But I am confused about "share".How can I create a share.Does is some thing like sharing the disk.
On the machine where you’re storing backups, goto windows explorer and right-click on the BACUP directory choose sharing to do the rest. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
To share a drive or folder on a remote machine, you may try … Exec master..xp_cmdshell ‘net share myshare=T: emp’ where myshare is just a logical share name
and T: emp is the folder you are sharing . Same you can use in local machine also, unlesss you are doing in graphically . rajiv
In your case you may also try *take backup in remote machine (Backup command )
*copy it to local machine (EXEC xp_cmdshell ‘copy c:EDRWHSFull08182005.BAK \yourmachinemyshare’) I am not sure y BACKUP DATABASE <dbnae> TO DISK=’\machinenamesharenamedbname.bak’ WITH INIT is not workin your end . rajiv
]]>