March 4, 2008 at 11:59 am
Hi,
I'm trying to backup a remote server from a PC that has SQL Server 2005 Management Studio installed on it.
I can connect to the remote server with SQL Server 2005 Management Studio and browse the database.
However whenever I try to backup a database, it only lets me choose a destination that exists on the remote database server. I can't choose a folder on the PC that SQL Server 2005 Management Studio is installed on.
Is there anyway around this?
Thanks
March 4, 2008 at 12:12 pm
Try this script to take backup on remote server. I am using it for long time.
/*
exec dbo.Usp_Remote_Backup_2005 @dbname = 'DataBaseName',@backupdevicename = 'DataBaseBackupName', @path = 'F:\Backup\'
exec dbo.Usp_Remote_Backup_2005 @dbname = 'DatabaseName',@backupdevicename = 'backupdevicename', @path = '\\RemoteServerName\F$\Backup\'
*/
CREATE procedure [dbo].[Usp_Remote_Backup_2005]
@dbname varchar(100),
@backupdevicename varchar(200),
@path varchar(1000)
as
set nocount on
set quoted_identifier off
declare @squery varchar(5000)
declare @physicaldevice varchar(500)
declare @errcode int
declare @errmsg varchar(250)
if not exists(select name from master.dbo.sysdatabases where name = @dbname and status <> 3608 )
begin
return -1
end
select @backupdevicename = ltrim(rtrim(@@SERVERNAME))+"_"+upper(@backupdevicename)+"_BACKUP"
select @physicaldevice = ltrim(rtrim(@path))+ltrim(rtrim(@backupdevicename))
--select @physicaldevice AS PHYSICALDEVICE,@backupdevicename AS BACKUPDEVICENAME
--Drop Database Backup Device, if any
If exists (select * from master..sysdevices where name = @backupdevicename)
begin
select @squery = "sp_dropdevice " + @backupdevicename
exec (@squery)
end
--Drop Database Backup Device
--Create Database Backup Device
select @squery = "sp_addumpdevice 'disk', " + @backupdevicename + ", '"+@physicaldevice + "'"
exec (@squery)
if @@ERROR != 0
begin
return -1
end
--Start Backup
set @squery = "backup database " + @dbname + " to " + @backupdevicename + " with init"
exec ("backup database " + @dbname + " to " + @backupdevicename + " with init")
select @errcode = @@ERROR
--Start--Drop Database Backup Device
If exists (select * from master.dbo.sysdevices where name = @backupdevicename)
begin
select @squery = "sp_dropdevice " + @backupdevicename
exec (@squery)
end
---End--Drop Database Backup Device
if @errcode <> 0
begin
select @errmsg = 'Fail to backup database ' + @dbname
return -1
end
SQL DBA.
March 4, 2008 at 12:30 pm
You can also just back it up to a UNC.
BACKUP DATABASE [Test] TO DISK = N'\\MyPath\Test.bak' WITH NOFORMAT, NOINIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
March 4, 2008 at 12:35 pm
The problem is, is that due to security issues and network policy(which I cant change), the ONLY port that is open between the machine with Managment Studio and the DB server is the mssql port. So any backups need to be done through that port...I think that's the tricky part.
Thanks!
March 4, 2008 at 12:56 pm
Magy (3/4/2008)
The problem is, is that due to security issues and network policy(which I cant change), the ONLY port that is open between the machine with Managment Studio and the DB server is the mssql port. So any backups need to be done through that port...I think that's the tricky part.Thanks!
That presents quite a bit of a problem. The only way I could possibly see this working then would be for you to do the backup locally, and then setup an ftp server on your local machine listening on 1433 and have the server ftp it to that port when it was done writing out the backup.
even if you were able to do it via management studio (UNC Backups are only possible via TSQL), it would make use of the default MS file sharing ports as that's what they are there for.
Almost seems like it would be easier to plug some sort of removable storage into the server and either a) backup to the local disk and then copy it to the removable media (my personal preference in this case) or b) back up directly to the removable media. Then disconnect the media, attach it to your machine and do the restore.
March 4, 2008 at 3:52 pm
I'd work with the net admins and get it rigged so that you can back it up to your machine or any other server on the network. That is crazy not having permission to perform a remote backup. I manage 35 servers and I'm constantly moving and backing up files across various machines through remote desktop, SSMS, or windows explorer. Sounds like the net admins have it locked down a little too tight...just my opinion...:)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply