Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and am
wanting to back it up remotely. I'm currently using a batch script that sh
uts down the service and then uses an xcopy command to copy the SQL director
y to a temporary location.
The script then uses a net start command to restart the services on a schedu
le. I then backup the temporary files that the xcopy command created. My q
uestion is, in the event of a System Crash, can I reinstall and configure SQ
L server and then restore
the temporary files and have them work? If not, is there another way to suc
cesfully back up SQL Server? Any help is very much appreciated. Thanks in
advance.Hi,
Please go thu the script will take the UNC path as the parameter and will
Backup Master, MSDB and all the User databases to the remote machine. This
script will create the unique Backup files names, this will ensure that old
backup sets were not overwritten.
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
> Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and
am wanting to back it up remotely. I'm currently using a batch script that
shuts down the service and then uses an xcopy command to copy the SQL
directory to a temporary location. The script then uses a net start command
to restart the services on a schedule. I then backup the temporary files
that the xcopy command created. My question is, in the event of a System
Crash, can I reinstall and configure SQL server and then restore the
temporary files and have them work? If not, is there another way to
succesfully back up SQL Server? Any help is very much appreciated. Thanks
in advance.|||"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
My question is, in the event of a System Crash, can I reinstall and
configure SQL server and then restore the temporary files and have them
work?
You should try out restoring the files to a different server and see if it
works. There is no substitute to this. It is always better if you have
carried out some test restores to confirm that your process will work, even
if somebody else says that it will, as it gives you some experience for when
you might have to do it for real.
Showing posts with label remotely. Show all posts
Showing posts with label remotely. Show all posts
Thursday, March 29, 2012
Thursday, March 22, 2012
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
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
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
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
Subscribe to:
Posts (Atom)