August 21, 2012 at 4:57 am
Hi
I have a small database on a web server. I would like to make scheduled backups of this database.
I've tried to create a Linked Server and then execute:
DECLARE @BackupCode nvarchar(4000)
SET @BackupCode = 'BACKUP DATABASE MyDatabaseName to disk="d:\tt.bak"'
EXECUTE [LinkedServerName].master.dbo.sp_executesql @BackupCode
Obviously I do not have access to the D drive on the server. Are there any way that I can save the backup directly on my machine rather than on the server?
August 21, 2012 at 5:05 am
If you create a share on your computer that the SQL server service account has read/write/modify permissions on you can reference a UNC in your backup statement to backup across the network
\\ComputerName\ShareName
Backup Database DB_Name
TO DISK = '\\ComputerName\ShareName\DBBackup.BAK'
Gethyn Elliswww.gethynellis.com
August 21, 2012 at 5:23 am
Thanks. As this server is out of our control getting a directory to which the SQL user have access to might not be so easy.
However, something else that seems to be working is SQL Server's built in "Copy Database" functionality.
If you have a local SQL Server, you can right click on your remote database and select 'Copy Database' under Tasks and SQL will create a scheduled job for the SQL Agent that will copy the database on a scheduled interval.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply