How to take database backup into another PC in Sql

2019-08-04 09:32发布

问题:

I have configured an Auto Backup option for my database, which makes database backups in regular intervals. This backup file is saved on the same Server PCs HDD (in another partition), from where I transfer (copy - paste) it into another PC (let's say, Backup PC) in the same network. Is there any option/way to configure the Auto Backup option such that it will save the Backup file into that Backup PC? I am using SQL Server 2008.

Edit:
I tried to create a backup plan and execute it, but getting below error (actual error message provided).

BACKUP DATABASE [CheckMateDB] TO  DISK = N''\\192.168.44.17\IIML4_MiniRaps_DB_Backup\CheckMateDB_backup_2014_01_27_132201_3595000.bak'' WITH NOFORMAT, NOINIT,  NAME = N''CheckMateDB_backup_2014_01_27_132201_3595000'', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''CheckMateDB'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''CheckMateDB'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''CheckMateDB'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N''\\192.168.44.17\IIML4_MiniRaps_DB_Backup\CheckMateDB_backup_2014_01_27_132201_3595000.bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

However, the user id I have used to create this plan, has admin privileges to the other PCs share folder. For example, I can locate the share folder of the Backup PC from Server PC using the user id from Start>Run>Backup PC's IP and can create/read/delete file to that folder.

回答1:

You need to map your target sytem with your system by network share

You can by using SQL

BACKUP DATABASE Foo TO DISK = '\\myserver\myshare\foo.bak' WITH INIT

Make sure the account used for the sql services has enough rights to write to the other pc.


For the reason the mapped network drives don't show up made with your user account. This has to do with the Sql Server service running under another user account then the one you've used for mapping the drive.