MS SQL Server - Backing up a database remotely

Author

Backing up a database remotely

dilipn123@gmail.com

2005-08-09, 3:23 am

Hi,

How to achieve this?

I want to backup a database via linked server

e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup

Or is there any other workaround to achieve this?

Thanks and Regards,
Dilip

Tibor Karaszi

2005-08-09, 3:23 am

You can't use a linked server to address the database you want to backup. But you can use
sp_executesql to execute an SQL string on the remote server:

EXEC <linkedservername>.master.dbo.sp_executesql N'BACKUP DATABASE pubs TO DISK = ''c:\pubs.bak'''

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

<dilipn123@gmail.com> wrote in message news:1123568862.267207.106550@z14g2000cwz.googlegroups.com...
> Hi,
>
> How to achieve this?
>
> I want to backup a database via linked server
>
> e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
>
> Or is there any other workaround to achieve this?
>
> Thanks and Regards,
> Dilip
>

dilipn123@gmail.com

2005-08-09, 3:23 am

Hi Tibor,

Thanks for the quick response.

Actually, my objective here is to use the processing power of another
machine to backup the remote database. e.g. Server A hosts the database
and Server B is from where I would like to take the backup.

I tried your workaround and it works but it uses the processing power
of Server A and not Server B. e.g. I issued the backup command from
Server B and checked the processes running on Server A by sp_who and it
showed the backup commands. How do I do it from Server B?

TIA

Regards,
Dilip

Hari Prasad

2005-08-09, 3:23 am

Hi,

No You cant.

If want to Backup the database which is in SERVER A, then ultimately
resource usage will be on Server A only.
Server B can be a client to issue the backup command as well as backup
storage; but the Backup command will be
executing in Server A.

Thanks
Hari
SQL Server MVP

<dilipn123@gmail.com> wrote in message
news:1123571709.739767.266160@z14g2000cwz.googlegroups.com...
> Hi Tibor,
>
> Thanks for the quick response.
>
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
>
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
>
> TIA
>
> Regards,
> Dilip
>

Tibor Karaszi

2005-08-09, 3:23 am

You can't do that, and it would make little sense as the backup is almost all I/O. Imagine if
database resides on A and you want B to process the backup. Then B would need to read all the data.
How would B get access to the data?

1. B directly accessing the database files over the network. You get into permissions aspects
(service account of B need permissions on files that A uses). Also, it cannot be done since the
files are already opened by the A SQL Server.

2. B asks A to read the files and send the data to B.

Then the backup data has to be written. If they are to be written on B, you can gain a little bit.
If they are to be written on A, you have to push back the data from B to A, making it even more
expensive.

Above is just thinking out loud to hopefully make you see that there would be little, if any, to
gain from such a scheme. In any event, it isn't possible.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

<dilipn123@gmail.com> wrote in message news:1123571709.739767.266160@z14g2000cwz.googlegroups.com...
> Hi Tibor,
>
> Thanks for the quick response.
>
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
>
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
>
> TIA
>
> Regards,
> Dilip
>

dilipn123@gmail.com

2005-08-09, 11:23 am

Tibor/Hari,

Thanks for your inputs.

I can understand it doesn't make that much sense, actually my objective
was to release the cpu and I/O resources which by backup process takes.

Our environment is such that it's a highly transaction processing ERP
database (40 GB) in size and avg 2/3 transactions per sec.
The system is high end having 3 processors and 6.5 GB RAM but for
disaster recovery situation, I have my tlogs backup running every 15
mins in between the user transactions and sometimes the backup process
takes too much of CPU resources and I/O since i'm backing up onto
network machine(standby server).

Recently, I was trying out SQLLite which reduces backup and compresses
the size, but it installs extended stored procedures into master
database to do the job and I dont want to install it on my production
database. I was thinking like if i can run the backup remotely, it
would have been good - that's why this remote backup question spawn up.

Guys, thanks for all ur inputs..have a good day

regards,
dilip

Sponsored Links