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

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,
DilipYou can't use a linked server to address the database you want to backup. B
ut 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 D
ISK = ''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.googlegroup
s.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.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
>|||You can't do that, and it would make little sense as the backup is almost al
l I/O. Imagine if
database resides on A and you want B to process the backup. Then B would nee
d 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 pe
rmissions aspects
(service account of B need permissions on files that A uses). Also, it canno
t 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 b
e 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.googlegroup
s.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

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,
DilipYou 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
>|||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.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
>|||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
>|||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

Back button for drilldown reports

Hereâ's a good question for the knowledgeable: I have been experimenting with
drilldown reports (where a report item is linked to a â'Jump to Reportâ'
action). In the designer there is a nifty little button that allows me to
move back to the previous report (level). This button does not seem apparent
in the Report Manager.
Am I missing something? I realize that there is IEâ's â'backâ' button however
that does not always return me to the previous report. For example if I
toggle the visibility of an item the browserâ's â'backâ' button will return me
to the previous state (hidden) as opposed to back to the report from which
this one was called.Yeah, there isn't one. I just put a back hyperlink in the header and footer
of each page and made sure when going back it sets the parameters to
whatever the default values are or in some cases whatever they were set to
the last time that report was visited.
bob.
"Herman K" <HermanK@.discussions.microsoft.com> wrote in message
news:D08E3158-FB92-4828-A6A8-ABCAA702AE3A@.microsoft.com...
> Here's a good question for the knowledgeable: I have been experimenting
> with
> drilldown reports (where a report item is linked to a 'Jump to Report'
> action). In the designer there is a nifty little button that allows me to
> move back to the previous report (level). This button does not seem
> apparent
> in the Report Manager.
> Am I missing something? I realize that there is IE's 'back' button
> however
> that does not always return me to the previous report. For example if I
> toggle the visibility of an item the browser's 'back' button will return
> me
> to the previous state (hidden) as opposed to back to the report from which
> this one was called.
>

avoid a server from being linked?

i say
that the server can listen
TCP/uid+pwd connections
but it reject links from remote sql servers
atte,
Hernn Castelo
SGA - UTN - FRBA
I am not sure what you are asking but it sound like you want to prevent a
SQL server from being used a s a linked server. If this is the question, I
am not sure that you can do this. As long as a user has a valid method to
connect then a linked server can be created.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||excuse me for my english!
exactly
that is what i wanted to know...
atte,
Hernn Castelo
SGA - UTN - FRBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> escribi en el mensaje
news:lwrz76K0EHA.3028@.cpmsftngxa10.phx.gbl...
> I am not sure what you are asking but it sound like you want to prevent a
> SQL server from being used a s a linked server. If this is the question, I
> am not sure that you can do this. As long as a user has a valid method to
> connect then a linked server can be created.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

avoid a server from being linked?

i say
that the server can listen
TCP/uid+pwd connections
but it reject links from remote sql servers
atte,
Hernn Castelo
SGA - UTN - FRBAI am not sure what you are asking but it sound like you want to prevent a
SQL server from being used a s a linked server. If this is the question, I
am not sure that you can do this. As long as a user has a valid method to
connect then a linked server can be created.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||excuse me for my english!
exactly
that is what i wanted to know...
atte,
Hernn Castelo
SGA - UTN - FRBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> escribi en el mensaje
news:lwrz76K0EHA.3028@.cpmsftngxa10.phx.gbl...
> I am not sure what you are asking but it sound like you want to prevent a
> SQL server from being used a s a linked server. If this is the question, I
> am not sure that you can do this. As long as a user has a valid method to
> connect then a linked server can be created.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

avoid a server from being linked?

i say
that the server can listen
TCP/uid+pwd connections
but it reject links from remote sql servers
--
atte,
Hernán Castelo
SGA - UTN - FRBAI am not sure what you are asking but it sound like you want to prevent a
SQL server from being used a s a linked server. If this is the question, I
am not sure that you can do this. As long as a user has a valid method to
connect then a linked server can be created.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||excuse me for my english!
exactly
that is what i wanted to know...
--
atte,
Hernán Castelo
SGA - UTN - FRBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> escribió en el mensaje
news:lwrz76K0EHA.3028@.cpmsftngxa10.phx.gbl...
> I am not sure what you are asking but it sound like you want to prevent a
> SQL server from being used a s a linked server. If this is the question, I
> am not sure that you can do this. As long as a user has a valid method to
> connect then a linked server can be created.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>