Backing up a database remotely

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
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.googlegr oups.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
>
|||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
|||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.googlegr oups.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
>
|||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.googlegr oups.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/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