Backup to a remote server

本文探讨了如何解决SQL Server备份过程中遇到的问题,特别是当尝试将数据库备份到远程服务器时。文章强调了SQL Server服务账户权限的重要性,并提供了一个VBScript脚本示例,用于创建带有日期时间戳的备份文件名。
http://www.sqlservercentral.com/Forums/Topic101853-22-1.aspx

Does the SQLServer account have the rights to write that file?  You start off saying you are trying to backup to a remote server, but your example shows writing to a local drive.  If you want to backup to a remote server, the SQLServer service must run under a domain account, and have rights to create/write files at the destination (as any network account must).

Also (if memory serves correctly), I had a problem when trying to create a backup of the same backup filename that existed.  I named the file with datetime, just like maintenance plan does.  Here is a snippet of my vbscript that I used:



' Create a Backup object and set action and source database properties.
sDtStr = CStr(Year(Now))
If Month(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Month(Now))
If Day(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Day(Now))
If Hour(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Hour(Now))
If Minute(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Minute(Now))

Dim oBackup
Set oBackup = CreateObject("SQLDMO.Backup")
oBackup.Action = SQLDMOBackup_Database
oBackup.Database = sDBName
oBackup.Files = sDBBkpPath & sDBName & "_" & sDtStr & ".bak"
oBackup.MediaName = sDBName & ".bak " & Date & " " & Time
oBackup.BackupSetName = sDBName
oBackup.BackupSetDescription = "Full backup of Yada DB before Updates."
oBackup.SQLBackup oServer

Since you are using a local path during your test, I would suspect that the SQL Service running under local Admin account would work OK (unless that account had its file permissions removed from that directory).  However, when you change that path to a remote location, the local Admin will have no possible way to have rights on the remote server, and you will need to run the SQL Service under a domain account to have Windows handle the file permissions.

When you say "specifying a device", I assume you mean writing a backup to a backup device, instead of a file.  You do not need to define a device for a remote backup, you can perform a backup using a UNC filename (e.g. //servername/share/backup/mydata.bak).  But you will need the SQL Service to be running under a domain account, as Windows will use that account for accessing the remote server (so that the remote server knows which account to check for file permissions).


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值