Backup in another machine
-
I want to backup the DB but in another PC (not local).... please help
-
You can create the network mapping from your OS and create a script backup to that drive
-
Hi Osama,
U want to take the backup on the network derive .
U can do by the below script but before the script u create a folder (backup folder) on network derice and share it with full permission.
run the script
BACKUP DATABASE [northwind]
TO DISK = N'\\192.168.4.23\c:\bk_db_pro\northwind.bkp'
with format
GO
-
thank you for your help
but i try it and got this error
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\10.0.2.83\d:\aa\northwind.bkp'. 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.
is it security problem ??
-
Your SQL Server service account must have an access to that PC
-
The problem lies in which account your SQL Agent and the SQL Server is running under. If I have understood the error correctly, they probably start under the 'LocalSystem' system (or domain account without the necessary permissions). You need a domain account with read/write permissions to the folder you want to backup the database in. You can check this is the SQL Server properties or the Computer Management under Services.
You will have to restart your SQL Server after you make this change. Once this change is made, regular Database Maintenance plans should work.
-
Hi osama,
I am sorry for my incomplete answer.
u should have the logon service rights on both the pc means ur domain account should have log on services right.
secondly u start the sql services with the log on service account on both the machine .
create a backup folder on the network machine and give security and permission to that log on service account.
then mapp that network derive or u can directly run the script
BACKUP DATABASE northwind
TO DISK = '\\192.168.32.24\backup_olap\northwind.bkp'
with format
GO
mohd sufain
-
in fact i try to backup DB in another server ,, and i want the backup file to be saved in my local machine.
both the server and my PC is on one domain.
???
-
It cannot be done. The backup has to run on the server where the database resides.
Are you trying to sneak a database on one of the servers onto you machine without the DBA's knowledge!!!!
DBAs hate developers who do this, so beware. -
thank u mohammad
i will ask the network admin to give me db_owner permition , and i hope that will solve the problem.
thanks
-
No Rookie , i don't want to sneak the DB .
but i can't make changes to production server untill i make sure that every thing is okay. i hope u got it
c u
bye
-
hi there,
here is what i would recommend you.
1. take a backup on your server (on which SQL Server is installed)
2. then write a copy command to copy the backup to the other machine (you need write access to copy the backup file on the other machine). you can do this using xp_cmdshell command. any dos statements can be executed through this command.
these both steps could be included to create a job that will backup the database and on success, copy the file to the network machine (using xp_cmdshell)
hope this helps.
-- Parag
-
Yes Mr. Expert DBA, I got it alright!
If you are going to get dbo_permissions to the database, it is still not going to solve the problem. Look at all the answers you have recieved and read them again.
-
Hey Rookie , take it easy and don't be angry.
I'm not Expert DBA for that am asking!
i understand that the account which i have to access the DB with must have write permission on the PC i want to write the backup file on (in my case this PC is my PC and i think i have full permission on my PC)
thanks Rookie
Bye
-
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply