I have done a database backup and store the backup file in a harddisk folder.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.
exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.
|||Thanks a lot. It works.
"Hassan" wrote:
> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> folder.
>
>
Showing posts with label restore. Show all posts
Showing posts with label restore. Show all posts
Wednesday, March 28, 2012
Remove Backup History
I have done a database backup and store the backup file in a harddisk folder
.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.|||Thanks a lot. It works.
"Hassan" wrote:
> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> folder.
>
>
.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.|||Thanks a lot. It works.
"Hassan" wrote:
> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> folder.
>
>
Remove Backup History
I have done a database backup and store the backup file in a harddisk folder.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.|||Thanks a lot. It works.
"Hassan" wrote:
> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> > I have done a database backup and store the backup file in a harddisk
> folder.
> > Now,
> > I want to remove the backup history log from the Restore Database dialog
> > window but I could not find the command to remove it.
>
>sql
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.|||Thanks a lot. It works.
"Hassan" wrote:
> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> > I have done a database backup and store the backup file in a harddisk
> folder.
> > Now,
> > I want to remove the backup history log from the Restore Database dialog
> > window but I could not find the command to remove it.
>
>sql
Monday, March 26, 2012
removal of merge subscriptions
I try to restore a set of replicas where the main server has been stolen.
Fortunately I have the databases at another server but when i try to push a
merge replication to a new main server i can not create a merge publication
beacuse the database has merge subscriptions that are anonymous or that use
the priority of the publisher to solve conflicts. Drop...
the problem is that I can not drop the subscriptions. If I delete it in
enterprise manager I still get the same error messages. I also used the
sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
help. Is there anyone who got any idea?
Thanks for your help in advance
Mats
Best wishes
Was the stolen server a publisher?
"Mats" wrote:
> I try to restore a set of replicas where the main server has been stolen.
> Fortunately I have the databases at another server but when i try to push a
> merge replication to a new main server i can not create a merge publication
> beacuse the database has merge subscriptions that are anonymous or that use
> the priority of the publisher to solve conflicts. Drop...
> the problem is that I can not drop the subscriptions. If I delete it in
> enterprise manager I still get the same error messages. I also used the
> sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
> help. Is there anyone who got any idea?
> Thanks for your help in advance
> Mats
> --
> Best wishes
|||Yes it was!
"Jim Breffni" wrote:
[vbcol=seagreen]
> Was the stolen server a publisher?
>
> "Mats" wrote:
|||If you have no other publishers you must drop all subscriptions.
Backup a subscription database.
Restore subscription database to NEW publisher.
Re-create publication.
Re-create Subscriptions
Note: If you think your subscriptions contain slightly different data then
you will need to manually sync these up to the publication before creating
the new subscriptions.
DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
Hilary also posted a link that helps to remove subsciptions
You
"Mats" wrote:
[vbcol=seagreen]
> Yes it was!
> "Jim Breffni" wrote:
|||Thanks for your answer:
Could you please tell me where is the link you mentioned
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> If you have no other publishers you must drop all subscriptions.
> Backup a subscription database.
> Restore subscription database to NEW publisher.
> Re-create publication.
> Re-create Subscriptions
> Note: If you think your subscriptions contain slightly different data then
> you will need to manually sync these up to the publication before creating
> the new subscriptions.
> DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
> Hilary also posted a link that helps to remove subsciptions
> You
> "Mats" wrote:
|||On 2004-12-07, Mats <Mats@.discussions.microsoft.com> wrote:
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
>
Can you please post the resolution of your problem, sucssesfull or not?
I had similair situation where I needed to set up replication between
publisher and subscriber where subscriber was previousley subscribed to
another publisher. I needed to issue sp_dropsubscriber at the subscriber (or
some other stored procedure, can't find it now) befure I was able to push
the snapshots to those subscribers.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Here you go...
http://groups-beta.google.com/group/...a?dmode=source
"Mats" wrote:
[vbcol=seagreen]
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
> Mats
> "Jim Breffni" wrote:
|||I thank you very much for your effort! But when i run the script it works OK.
When I then try to create a merge publication I get an error 208 when
creating the articles which means that a reference is missing. Any ideas?
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> Here you go...
> http://groups-beta.google.com/group/...a?dmode=source
>
>
> "Mats" wrote:
Fortunately I have the databases at another server but when i try to push a
merge replication to a new main server i can not create a merge publication
beacuse the database has merge subscriptions that are anonymous or that use
the priority of the publisher to solve conflicts. Drop...
the problem is that I can not drop the subscriptions. If I delete it in
enterprise manager I still get the same error messages. I also used the
sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
help. Is there anyone who got any idea?
Thanks for your help in advance
Mats
Best wishes
Was the stolen server a publisher?
"Mats" wrote:
> I try to restore a set of replicas where the main server has been stolen.
> Fortunately I have the databases at another server but when i try to push a
> merge replication to a new main server i can not create a merge publication
> beacuse the database has merge subscriptions that are anonymous or that use
> the priority of the publisher to solve conflicts. Drop...
> the problem is that I can not drop the subscriptions. If I delete it in
> enterprise manager I still get the same error messages. I also used the
> sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
> help. Is there anyone who got any idea?
> Thanks for your help in advance
> Mats
> --
> Best wishes
|||Yes it was!
"Jim Breffni" wrote:
[vbcol=seagreen]
> Was the stolen server a publisher?
>
> "Mats" wrote:
|||If you have no other publishers you must drop all subscriptions.
Backup a subscription database.
Restore subscription database to NEW publisher.
Re-create publication.
Re-create Subscriptions
Note: If you think your subscriptions contain slightly different data then
you will need to manually sync these up to the publication before creating
the new subscriptions.
DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
Hilary also posted a link that helps to remove subsciptions
You
"Mats" wrote:
[vbcol=seagreen]
> Yes it was!
> "Jim Breffni" wrote:
|||Thanks for your answer:
Could you please tell me where is the link you mentioned
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> If you have no other publishers you must drop all subscriptions.
> Backup a subscription database.
> Restore subscription database to NEW publisher.
> Re-create publication.
> Re-create Subscriptions
> Note: If you think your subscriptions contain slightly different data then
> you will need to manually sync these up to the publication before creating
> the new subscriptions.
> DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
> Hilary also posted a link that helps to remove subsciptions
> You
> "Mats" wrote:
|||On 2004-12-07, Mats <Mats@.discussions.microsoft.com> wrote:
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
>
Can you please post the resolution of your problem, sucssesfull or not?
I had similair situation where I needed to set up replication between
publisher and subscriber where subscriber was previousley subscribed to
another publisher. I needed to issue sp_dropsubscriber at the subscriber (or
some other stored procedure, can't find it now) befure I was able to push
the snapshots to those subscribers.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Here you go...
http://groups-beta.google.com/group/...a?dmode=source
"Mats" wrote:
[vbcol=seagreen]
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
> Mats
> "Jim Breffni" wrote:
|||I thank you very much for your effort! But when i run the script it works OK.
When I then try to create a merge publication I get an error 208 when
creating the articles which means that a reference is missing. Any ideas?
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> Here you go...
> http://groups-beta.google.com/group/...a?dmode=source
>
>
> "Mats" wrote:
Wednesday, March 21, 2012
Remote Stored Proc Call
I'm calling this from another sql server...
I created a linked server... and want to restore database backups on the other box...
The restore script runs fine when ran locally but fails with the message below when calling it remotely
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
CREATE PROCEDURE usp_restore_database_backups AS
RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'BesMgmt', 'single user', true
GO
I have set it to read only dbo only ... single user... still get the same message...
does anyone have any suggestions...try executing...
ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
or
ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
before the restore statement and drop the sp_dboption.|||thanks Thrasymachus
I'm still getting the same error:
Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.|||post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.|||The error does not tally with this but just belt and braces.
If you run
EXEC sp_helpserver
do you see RPC & RPC out in the status field?|||When I set the database manually into single user mode I get a different error when trying to restore the database... this is the error that comes from when the database is in single user mode....
"Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."
CREATE PROCEDURE usp_restore_database_backups AS
/*
declare @.x varchar(255)
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('BesMgmt')
exec (@.x)
*/
ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'
WAITFOR DELAY '00:00:05'
--EXEC sp_dboption 'BesMgmt', 'single user', true
GO|||The command is within a job...
the sql agent service is ran under a domain user acount...
I created a linked server... and want to restore database backups on the other box...
The restore script runs fine when ran locally but fails with the message below when calling it remotely
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
CREATE PROCEDURE usp_restore_database_backups AS
RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'BesMgmt', 'single user', true
GO
I have set it to read only dbo only ... single user... still get the same message...
does anyone have any suggestions...try executing...
ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
or
ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
before the restore statement and drop the sp_dboption.|||thanks Thrasymachus
I'm still getting the same error:
Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.|||post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.|||The error does not tally with this but just belt and braces.
If you run
EXEC sp_helpserver
do you see RPC & RPC out in the status field?|||When I set the database manually into single user mode I get a different error when trying to restore the database... this is the error that comes from when the database is in single user mode....
"Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."
CREATE PROCEDURE usp_restore_database_backups AS
/*
declare @.x varchar(255)
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('BesMgmt')
exec (@.x)
*/
ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'
WAITFOR DELAY '00:00:05'
--EXEC sp_dboption 'BesMgmt', 'single user', true
GO|||The command is within a job...
the sql agent service is ran under a domain user acount...
Monday, March 12, 2012
Remote Restore via Linked Server
Hi, I'm initiating a restore via a linked server and an SQL Agent Job. The
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||This is a multi-part message in MIME format.
--050606090705070603040506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
>> The job completes successfully in about 10 minutes, but the backup is left
>> in a loading state. This suggests that the connection timed out. Any
>> thoughts on how to make this more robust? (possibly start an SQL Agent
>> job on the remote server from the parent server')
>> Thanks
>> Bill
>>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--050606090705070603040506
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Bill S wrote:
<blockquote cite="mid%237WfcfqrGHA.1140@.TK2MSFTNGP05.phx.gbl"
type="cite">
<pre wrap="">Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:swartz.bill@.gmail.com"><swartz.bill@.gmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl">news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
The job completes successfully in about 10 minutes, but the backup is left
in a loading state. This suggests that the connection timed out. Any
thoughts on how to make this more robust? (possibly start an SQL Agent
job on the remote server from the parent server')
Thanks
Bill
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the
restore is done so that will give you what you want.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--050606090705070603040506--
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||This is a multi-part message in MIME format.
--050606090705070603040506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
>> The job completes successfully in about 10 minutes, but the backup is left
>> in a loading state. This suggests that the connection timed out. Any
>> thoughts on how to make this more robust? (possibly start an SQL Agent
>> job on the remote server from the parent server')
>> Thanks
>> Bill
>>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--050606090705070603040506
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Bill S wrote:
<blockquote cite="mid%237WfcfqrGHA.1140@.TK2MSFTNGP05.phx.gbl"
type="cite">
<pre wrap="">Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:swartz.bill@.gmail.com"><swartz.bill@.gmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl">news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
The job completes successfully in about 10 minutes, but the backup is left
in a loading state. This suggests that the connection timed out. Any
thoughts on how to make this more robust? (possibly start an SQL Agent
job on the remote server from the parent server')
Thanks
Bill
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the
restore is done so that will give you what you want.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--050606090705070603040506--
Remote Restore via Linked Server
Hi, I'm initiating a restore via a linked server and an SQL Agent Job. The
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words
it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words
it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator
Remote restore question
Hello everyone,
Quick question. We perform db backups at 10pm on a certain database
that is about 12 gig. Then the transaction log is backed up every
hour. We'd like to restore this database to another off site server,
then apply the logs to keep it in sync w/ production. I've tested this
and it seems to work fine. My only question is regarding the full
backups that are performed every night. Due to bandwidth limitations,
we can't get the full db backup to the remote server in a reasonable
amount of time (the logs can make it over within one hour no problem).
Can we just keep restoring the logs on the remote server without the
nightly db backups being restored? I guess I'm asking if the full db
backup does anything to the log (empties it or the like) that would
make the dbs out of sync?
Thanks in advance.That would be fine. This is one of the reasons why a full backup doesn't
truncate the transaction log. But I suggest you have operational
instructions readily available on how to do this manually, in case the log
restores becomes out of sync, for any reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||You can restore logs in sequence on a SQL 7.0 or 2000 and ignore intervening
full backups. This is intentional so that if a full backup goes bad, you
can go to an older full backup and restore more logs to get your database up
to the last committed transaction.
BTW, this is called log-shipping. SQL enterprise edition includes it as
built-in, but many people write their own routines to handle situations and
configurations not directly supported but the built-in tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||Excellent. Thanks to both of you.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<enVq$YGDEHA.1588@.tk2msftn
gp13.phx.gbl>...
> You can restore logs in sequence on a SQL 7.0 or 2000 and ignore interveni
ng
> full backups. This is intentional so that if a full backup goes bad, you
> can go to an older full backup and restore more logs to get your database
up
> to the last committed transaction.
> BTW, this is called log-shipping. SQL enterprise edition includes it as
> built-in, but many people write their own routines to handle situations an
d
> configurations not directly supported but the built-in tools.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
Quick question. We perform db backups at 10pm on a certain database
that is about 12 gig. Then the transaction log is backed up every
hour. We'd like to restore this database to another off site server,
then apply the logs to keep it in sync w/ production. I've tested this
and it seems to work fine. My only question is regarding the full
backups that are performed every night. Due to bandwidth limitations,
we can't get the full db backup to the remote server in a reasonable
amount of time (the logs can make it over within one hour no problem).
Can we just keep restoring the logs on the remote server without the
nightly db backups being restored? I guess I'm asking if the full db
backup does anything to the log (empties it or the like) that would
make the dbs out of sync?
Thanks in advance.That would be fine. This is one of the reasons why a full backup doesn't
truncate the transaction log. But I suggest you have operational
instructions readily available on how to do this manually, in case the log
restores becomes out of sync, for any reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||You can restore logs in sequence on a SQL 7.0 or 2000 and ignore intervening
full backups. This is intentional so that if a full backup goes bad, you
can go to an older full backup and restore more logs to get your database up
to the last committed transaction.
BTW, this is called log-shipping. SQL enterprise edition includes it as
built-in, but many people write their own routines to handle situations and
configurations not directly supported but the built-in tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||Excellent. Thanks to both of you.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<enVq$YGDEHA.1588@.tk2msftn
gp13.phx.gbl>...
> You can restore logs in sequence on a SQL 7.0 or 2000 and ignore interveni
ng
> full backups. This is intentional so that if a full backup goes bad, you
> can go to an older full backup and restore more logs to get your database
up
> to the last committed transaction.
> BTW, this is called log-shipping. SQL enterprise edition includes it as
> built-in, but many people write their own routines to handle situations an
d
> configurations not directly supported but the built-in tools.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
Remote Proc Restore Fails
I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
Remote Proc Restore Fails
I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
Remote Proc Restore Fails
I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
Wednesday, March 7, 2012
Remote Desktop
Hi,
When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see local drives and not mapped drives. When I go physically to the computer I can see the local and mapped drives when trying to restore db. This leads me to belive it is a 'Remote D
esktop' issue.
Any suggestions?
Thanks in advance
stonehead,
Why not just use Enterprise Manager, or even better, Query Analyzer from
your client workstation to do the restore? Do you need to use Remote
Desktop? Do you have a roaming profile on the domain? If you map the drives
using Remote Desktop, log off and then log on again, do they still
disappear?
When restoring over the network, you should always use a UNC name anyway, as
this reduces exposure to bugs. Mapped drive letters can change, or even
disappear. Try using a UNC name, i.e.
\\servername\share\folder\file
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"stonehead" <anonymous@.discussions.microsoft.com> wrote in message
news:9261C67A-2A59-452B-9129-FEF7DADC6EC9@.microsoft.com...
> Hi,
> When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see
local drives and not mapped drives. When I go physically to the computer I
can see the local and mapped drives when trying to restore db. This leads
me to belive it is a 'Remote Desktop' issue.
> Any suggestions?
> Thanks in advance
When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see local drives and not mapped drives. When I go physically to the computer I can see the local and mapped drives when trying to restore db. This leads me to belive it is a 'Remote D
esktop' issue.
Any suggestions?
Thanks in advance
stonehead,
Why not just use Enterprise Manager, or even better, Query Analyzer from
your client workstation to do the restore? Do you need to use Remote
Desktop? Do you have a roaming profile on the domain? If you map the drives
using Remote Desktop, log off and then log on again, do they still
disappear?
When restoring over the network, you should always use a UNC name anyway, as
this reduces exposure to bugs. Mapped drive letters can change, or even
disappear. Try using a UNC name, i.e.
\\servername\share\folder\file
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"stonehead" <anonymous@.discussions.microsoft.com> wrote in message
news:9261C67A-2A59-452B-9129-FEF7DADC6EC9@.microsoft.com...
> Hi,
> When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see
local drives and not mapped drives. When I go physically to the computer I
can see the local and mapped drives when trying to restore db. This leads
me to belive it is a 'Remote Desktop' issue.
> Any suggestions?
> Thanks in advance
Remote Desktop
Hi,
When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see lo
cal drives and not mapped drives. When I go physically to the computer I ca
n see the local and mapped drives when trying to restore db. This leads me
to belive it is a 'Remote D
esktop' issue.
Any suggestions?
Thanks in advancestonehead,
Why not just use Enterprise Manager, or even better, Query Analyzer from
your client workstation to do the restore? Do you need to use Remote
Desktop? Do you have a roaming profile on the domain? If you map the drives
using Remote Desktop, log off and then log on again, do they still
disappear?
When restoring over the network, you should always use a UNC name anyway, as
this reduces exposure to bugs. Mapped drive letters can change, or even
disappear. Try using a UNC name, i.e.
\\servername\share\folder\file
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"stonehead" <anonymous@.discussions.microsoft.com> wrote in message
news:9261C67A-2A59-452B-9129-FEF7DADC6EC9@.microsoft.com...
> Hi,
> When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see
local drives and not mapped drives. When I go physically to the computer I
can see the local and mapped drives when trying to restore db. This leads
me to belive it is a 'Remote Desktop' issue.
> Any suggestions?
> Thanks in advance
When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see lo
cal drives and not mapped drives. When I go physically to the computer I ca
n see the local and mapped drives when trying to restore db. This leads me
to belive it is a 'Remote D
esktop' issue.
Any suggestions?
Thanks in advancestonehead,
Why not just use Enterprise Manager, or even better, Query Analyzer from
your client workstation to do the restore? Do you need to use Remote
Desktop? Do you have a roaming profile on the domain? If you map the drives
using Remote Desktop, log off and then log on again, do they still
disappear?
When restoring over the network, you should always use a UNC name anyway, as
this reduces exposure to bugs. Mapped drive letters can change, or even
disappear. Try using a UNC name, i.e.
\\servername\share\folder\file
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"stonehead" <anonymous@.discussions.microsoft.com> wrote in message
news:9261C67A-2A59-452B-9129-FEF7DADC6EC9@.microsoft.com...
> Hi,
> When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see
local drives and not mapped drives. When I go physically to the computer I
can see the local and mapped drives when trying to restore db. This leads
me to belive it is a 'Remote Desktop' issue.
> Any suggestions?
> Thanks in advance
Remote Desktop
Hi
When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see local drives and not mapped drives. When I go physically to the computer I can see the local and mapped drives when trying to restore db. This leads me to belive it is a 'Remote Desktop' issue
Any suggestions
Thanks in advancestonehead,
Why not just use Enterprise Manager, or even better, Query Analyzer from
your client workstation to do the restore? Do you need to use Remote
Desktop? Do you have a roaming profile on the domain? If you map the drives
using Remote Desktop, log off and then log on again, do they still
disappear?
When restoring over the network, you should always use a UNC name anyway, as
this reduces exposure to bugs. Mapped drive letters can change, or even
disappear. Try using a UNC name, i.e.
\\servername\share\folder\file
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"stonehead" <anonymous@.discussions.microsoft.com> wrote in message
news:9261C67A-2A59-452B-9129-FEF7DADC6EC9@.microsoft.com...
> Hi,
> When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see
local drives and not mapped drives. When I go physically to the computer I
can see the local and mapped drives when trying to restore db. This leads
me to belive it is a 'Remote Desktop' issue.
> Any suggestions?
> Thanks in advance
When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see local drives and not mapped drives. When I go physically to the computer I can see the local and mapped drives when trying to restore db. This leads me to belive it is a 'Remote Desktop' issue
Any suggestions
Thanks in advancestonehead,
Why not just use Enterprise Manager, or even better, Query Analyzer from
your client workstation to do the restore? Do you need to use Remote
Desktop? Do you have a roaming profile on the domain? If you map the drives
using Remote Desktop, log off and then log on again, do they still
disappear?
When restoring over the network, you should always use a UNC name anyway, as
this reduces exposure to bugs. Mapped drive letters can change, or even
disappear. Try using a UNC name, i.e.
\\servername\share\folder\file
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"stonehead" <anonymous@.discussions.microsoft.com> wrote in message
news:9261C67A-2A59-452B-9129-FEF7DADC6EC9@.microsoft.com...
> Hi,
> When I go to restore db in SQL 2000 using 'Remote Desktop' I can only see
local drives and not mapped drives. When I go physically to the computer I
can see the local and mapped drives when trying to restore db. This leads
me to belive it is a 'Remote Desktop' issue.
> Any suggestions?
> Thanks in advance
Remote Database backup/restore
Anyone know if this can be done over TCP/IP connection?
Thanks in advance..
-BBHi,
Yes, You can do that. All you have to do is , create a Alias for the SQL
server using TCP/IP protocol, Ip address and Port number.
Using that Alias name register the SQL server in enterprise manager or use
Query Analyzer to connect.
After that you could run the BACKUP DATABASE command to backup and RESTORE
DATABASE command to restore the database.
To backup the file remotely or restore the file from remote locaton you have
to start the SQL Server service
using an Domain OS user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Anyone know if this can be done over TCP/IP connection?
> Thanks in advance..
> -BB
>|||Hi Hari,
I had gotten this already. So the only file systems that can EVER be seen
are the ones available to the Server .. no matter where you execute the
BACKUP from? For example if I want to execute the backup from a remote
site, and put the dump file on client that is executing the BACKUP, then I
MUST have a network share enabled on the remote client computer?
Many thnx again,
-BB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, You can do that. All you have to do is , create a Alias for the SQL
> server using TCP/IP protocol, Ip address and Port number.
> Using that Alias name register the SQL server in enterprise manager or use
> Query Analyzer to connect.
> After that you could run the BACKUP DATABASE command to backup and RESTORE
> DATABASE command to restore the database.
> To backup the file remotely or restore the file from remote locaton you
have
> to start the SQL Server service
> using an Domain OS user which got write access to the remote share.
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option.
> There you give a Valid Domain OS user and password to start the service.
Now
> stop and start the MSSQL Serevr service.
> Note: That domain user should have previlages in the remote share to write
> the file as well as prev. to start the SQL server.
> After this you try to execute the Backup database command in Query
> Analyzer:-
>
> Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> init ( With init will overwrite the backup file every time)
> Thanks
> Hari
> MCDBA
>
>
> Thanks
> Hari
> MCDBA
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> > Anyone know if this can be done over TCP/IP connection?
> >
> > Thanks in advance..
> >
> > -BB
> >
> >
>|||Correct. SQL Server does the backup, not the client from where you run the client app to send the BACKUP
commend to SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hi Hari,
> I had gotten this already. So the only file systems that can EVER be seen
> are the ones available to the Server .. no matter where you execute the
> BACKUP from? For example if I want to execute the backup from a remote
> site, and put the dump file on client that is executing the BACKUP, then I
> MUST have a network share enabled on the remote client computer?
> Many thnx again,
> -BB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Yes, You can do that. All you have to do is , create a Alias for the SQL
> > server using TCP/IP protocol, Ip address and Port number.
> > Using that Alias name register the SQL server in enterprise manager or use
> > Query Analyzer to connect.
> >
> > After that you could run the BACKUP DATABASE command to backup and RESTORE
> > DATABASE command to restore the database.
> >
> > To backup the file remotely or restore the file from remote locaton you
> have
> > to start the SQL Server service
> > using an Domain OS user which got write access to the remote share.
> >
> > So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
> sercice--
> > Double click and select the "Log on" option.
> > There you give a Valid Domain OS user and password to start the service.
> Now
> > stop and start the MSSQL Serevr service.
> >
> > Note: That domain user should have previlages in the remote share to write
> > the file as well as prev. to start the SQL server.
> >
> > After this you try to execute the Backup database command in Query
> > Analyzer:-
> >
> >
> > Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> > init ( With init will overwrite the backup file every time)
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> > news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> > > Anyone know if this can be done over TCP/IP connection?
> > >
> > > Thanks in advance..
> > >
> > > -BB
> > >
> > >
> >
> >
>|||ok thx
-BB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9njHXseEHA.396@.TK2MSFTNGP12.phx.gbl...
> Correct. SQL Server does the backup, not the client from where you run the
client app to send the BACKUP
> commend to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > Hi Hari,
> >
> > I had gotten this already. So the only file systems that can EVER be
seen
> > are the ones available to the Server .. no matter where you execute the
> > BACKUP from? For example if I want to execute the backup from a
remote
> > site, and put the dump file on client that is executing the BACKUP, then
I
> > MUST have a network share enabled on the remote client computer?
> >
> > Many thnx again,
> >
> > -BB
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Yes, You can do that. All you have to do is , create a Alias for the
SQL
> > > server using TCP/IP protocol, Ip address and Port number.
> > > Using that Alias name register the SQL server in enterprise manager or
use
> > > Query Analyzer to connect.
> > >
> > > After that you could run the BACKUP DATABASE command to backup and
RESTORE
> > > DATABASE command to restore the database.
> > >
> > > To backup the file remotely or restore the file from remote locaton
you
> > have
> > > to start the SQL Server service
> > > using an Domain OS user which got write access to the remote share.
> > >
> > > So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
> > sercice--
> > > Double click and select the "Log on" option.
> > > There you give a Valid Domain OS user and password to start the
service.
> > Now
> > > stop and start the MSSQL Serevr service.
> > >
> > > Note: That domain user should have previlages in the remote share to
write
> > > the file as well as prev. to start the SQL server.
> > >
> > > After this you try to execute the Backup database command in Query
> > > Analyzer:-
> > >
> > >
> > > Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat'
with
> > > init ( With init will overwrite the backup file every time)
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > > "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> > > news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> > > > Anyone know if this can be done over TCP/IP connection?
> > > >
> > > > Thanks in advance..
> > > >
> > > > -BB
> > > >
> > > >
> > >
> > >
> >
> >
>
Thanks in advance..
-BBHi,
Yes, You can do that. All you have to do is , create a Alias for the SQL
server using TCP/IP protocol, Ip address and Port number.
Using that Alias name register the SQL server in enterprise manager or use
Query Analyzer to connect.
After that you could run the BACKUP DATABASE command to backup and RESTORE
DATABASE command to restore the database.
To backup the file remotely or restore the file from remote locaton you have
to start the SQL Server service
using an Domain OS user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Anyone know if this can be done over TCP/IP connection?
> Thanks in advance..
> -BB
>|||Hi Hari,
I had gotten this already. So the only file systems that can EVER be seen
are the ones available to the Server .. no matter where you execute the
BACKUP from? For example if I want to execute the backup from a remote
site, and put the dump file on client that is executing the BACKUP, then I
MUST have a network share enabled on the remote client computer?
Many thnx again,
-BB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, You can do that. All you have to do is , create a Alias for the SQL
> server using TCP/IP protocol, Ip address and Port number.
> Using that Alias name register the SQL server in enterprise manager or use
> Query Analyzer to connect.
> After that you could run the BACKUP DATABASE command to backup and RESTORE
> DATABASE command to restore the database.
> To backup the file remotely or restore the file from remote locaton you
have
> to start the SQL Server service
> using an Domain OS user which got write access to the remote share.
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option.
> There you give a Valid Domain OS user and password to start the service.
Now
> stop and start the MSSQL Serevr service.
> Note: That domain user should have previlages in the remote share to write
> the file as well as prev. to start the SQL server.
> After this you try to execute the Backup database command in Query
> Analyzer:-
>
> Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> init ( With init will overwrite the backup file every time)
> Thanks
> Hari
> MCDBA
>
>
> Thanks
> Hari
> MCDBA
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> > Anyone know if this can be done over TCP/IP connection?
> >
> > Thanks in advance..
> >
> > -BB
> >
> >
>|||Correct. SQL Server does the backup, not the client from where you run the client app to send the BACKUP
commend to SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hi Hari,
> I had gotten this already. So the only file systems that can EVER be seen
> are the ones available to the Server .. no matter where you execute the
> BACKUP from? For example if I want to execute the backup from a remote
> site, and put the dump file on client that is executing the BACKUP, then I
> MUST have a network share enabled on the remote client computer?
> Many thnx again,
> -BB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Yes, You can do that. All you have to do is , create a Alias for the SQL
> > server using TCP/IP protocol, Ip address and Port number.
> > Using that Alias name register the SQL server in enterprise manager or use
> > Query Analyzer to connect.
> >
> > After that you could run the BACKUP DATABASE command to backup and RESTORE
> > DATABASE command to restore the database.
> >
> > To backup the file remotely or restore the file from remote locaton you
> have
> > to start the SQL Server service
> > using an Domain OS user which got write access to the remote share.
> >
> > So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
> sercice--
> > Double click and select the "Log on" option.
> > There you give a Valid Domain OS user and password to start the service.
> Now
> > stop and start the MSSQL Serevr service.
> >
> > Note: That domain user should have previlages in the remote share to write
> > the file as well as prev. to start the SQL server.
> >
> > After this you try to execute the Backup database command in Query
> > Analyzer:-
> >
> >
> > Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> > init ( With init will overwrite the backup file every time)
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> > news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> > > Anyone know if this can be done over TCP/IP connection?
> > >
> > > Thanks in advance..
> > >
> > > -BB
> > >
> > >
> >
> >
>|||ok thx
-BB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9njHXseEHA.396@.TK2MSFTNGP12.phx.gbl...
> Correct. SQL Server does the backup, not the client from where you run the
client app to send the BACKUP
> commend to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > Hi Hari,
> >
> > I had gotten this already. So the only file systems that can EVER be
seen
> > are the ones available to the Server .. no matter where you execute the
> > BACKUP from? For example if I want to execute the backup from a
remote
> > site, and put the dump file on client that is executing the BACKUP, then
I
> > MUST have a network share enabled on the remote client computer?
> >
> > Many thnx again,
> >
> > -BB
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Yes, You can do that. All you have to do is , create a Alias for the
SQL
> > > server using TCP/IP protocol, Ip address and Port number.
> > > Using that Alias name register the SQL server in enterprise manager or
use
> > > Query Analyzer to connect.
> > >
> > > After that you could run the BACKUP DATABASE command to backup and
RESTORE
> > > DATABASE command to restore the database.
> > >
> > > To backup the file remotely or restore the file from remote locaton
you
> > have
> > > to start the SQL Server service
> > > using an Domain OS user which got write access to the remote share.
> > >
> > > So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
> > sercice--
> > > Double click and select the "Log on" option.
> > > There you give a Valid Domain OS user and password to start the
service.
> > Now
> > > stop and start the MSSQL Serevr service.
> > >
> > > Note: That domain user should have previlages in the remote share to
write
> > > the file as well as prev. to start the SQL server.
> > >
> > > After this you try to execute the Backup database command in Query
> > > Analyzer:-
> > >
> > >
> > > Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat'
with
> > > init ( With init will overwrite the backup file every time)
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > > "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> > > news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> > > > Anyone know if this can be done over TCP/IP connection?
> > > >
> > > > Thanks in advance..
> > > >
> > > > -BB
> > > >
> > > >
> > >
> > >
> >
> >
>
Remote Database backup/restore
Anyone know if this can be done over TCP/IP connection?
Thanks in advance..
-BBHi,
Yes, You can do that. All you have to do is , create a Alias for the SQL
server using TCP/IP protocol, Ip address and Port number.
Using that Alias name register the SQL server in enterprise manager or use
Query Analyzer to connect.
After that you could run the BACKUP DATABASE command to backup and RESTORE
DATABASE command to restore the database.
To backup the file remotely or restore the file from remote locaton you have
to start the SQL Server service
using an Domain OS user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Anyone know if this can be done over TCP/IP connection?
> Thanks in advance..
> -BB
>|||Hi Hari,
I had gotten this already. So the only file systems that can EVER be seen
are the ones available to the Server .. no matter where you execute the
BACKUP from? For example if I want to execute the backup from a remote
site, and put the dump file on client that is executing the BACKUP, then I
MUST have a network share enabled on the remote client computer?
Many thnx again,
-BB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, You can do that. All you have to do is , create a Alias for the SQL
> server using TCP/IP protocol, Ip address and Port number.
> Using that Alias name register the SQL server in enterprise manager or use
> Query Analyzer to connect.
> After that you could run the BACKUP DATABASE command to backup and RESTORE
> DATABASE command to restore the database.
> To backup the file remotely or restore the file from remote locaton you
have
> to start the SQL Server service
> using an Domain OS user which got write access to the remote share.
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option.
> There you give a Valid Domain OS user and password to start the service.
Now
> stop and start the MSSQL Serevr service.
> Note: That domain user should have previlages in the remote share to write
> the file as well as prev. to start the SQL server.
> After this you try to execute the Backup database command in Query
> Analyzer:-
>
> Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> init ( With init will overwrite the backup file every time)
> Thanks
> Hari
> MCDBA
>
>
> Thanks
> Hari
> MCDBA
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
>|||Correct. SQL Server does the backup, not the client from where you run the c
lient app to send the BACKUP
commend to SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message news:Ox0$yQseEHA.1356@.TK2MSFTNGP09
.phx.gbl...
> Hi Hari,
> I had gotten this already. So the only file systems that can EVER be see
n
> are the ones available to the Server .. no matter where you execute the
> BACKUP from? For example if I want to execute the backup from a remote
> site, and put the dump file on client that is executing the BACKUP, then I
> MUST have a network share enabled on the remote client computer?
> Many thnx again,
> -BB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> have
> sercice--
> Now
>|||ok thx
-BB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9njHXseEHA.396@.TK2MSFTNGP12.phx.gbl...
> Correct. SQL Server does the backup, not the client from where you run the
client app to send the BACKUP
> commend to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
seen[vbcol=seagreen]
remote[vbcol=seagreen]
I[vbcol=seagreen]
SQL[vbcol=seagreen]
use[vbcol=seagreen]
RESTORE[vbcol=seagreen]
you[vbcol=seagreen]
service.[vbcol=seagreen]
write[vbcol=seagreen]
with[vbcol=seagreen]
>
Thanks in advance..
-BBHi,
Yes, You can do that. All you have to do is , create a Alias for the SQL
server using TCP/IP protocol, Ip address and Port number.
Using that Alias name register the SQL server in enterprise manager or use
Query Analyzer to connect.
After that you could run the BACKUP DATABASE command to backup and RESTORE
DATABASE command to restore the database.
To backup the file remotely or restore the file from remote locaton you have
to start the SQL Server service
using an Domain OS user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Anyone know if this can be done over TCP/IP connection?
> Thanks in advance..
> -BB
>|||Hi Hari,
I had gotten this already. So the only file systems that can EVER be seen
are the ones available to the Server .. no matter where you execute the
BACKUP from? For example if I want to execute the backup from a remote
site, and put the dump file on client that is executing the BACKUP, then I
MUST have a network share enabled on the remote client computer?
Many thnx again,
-BB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, You can do that. All you have to do is , create a Alias for the SQL
> server using TCP/IP protocol, Ip address and Port number.
> Using that Alias name register the SQL server in enterprise manager or use
> Query Analyzer to connect.
> After that you could run the BACKUP DATABASE command to backup and RESTORE
> DATABASE command to restore the database.
> To backup the file remotely or restore the file from remote locaton you
have
> to start the SQL Server service
> using an Domain OS user which got write access to the remote share.
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option.
> There you give a Valid Domain OS user and password to start the service.
Now
> stop and start the MSSQL Serevr service.
> Note: That domain user should have previlages in the remote share to write
> the file as well as prev. to start the SQL server.
> After this you try to execute the Backup database command in Query
> Analyzer:-
>
> Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> init ( With init will overwrite the backup file every time)
> Thanks
> Hari
> MCDBA
>
>
> Thanks
> Hari
> MCDBA
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
>|||Correct. SQL Server does the backup, not the client from where you run the c
lient app to send the BACKUP
commend to SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message news:Ox0$yQseEHA.1356@.TK2MSFTNGP09
.phx.gbl...
> Hi Hari,
> I had gotten this already. So the only file systems that can EVER be see
n
> are the ones available to the Server .. no matter where you execute the
> BACKUP from? For example if I want to execute the backup from a remote
> site, and put the dump file on client that is executing the BACKUP, then I
> MUST have a network share enabled on the remote client computer?
> Many thnx again,
> -BB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> have
> sercice--
> Now
>|||ok thx
-BB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9njHXseEHA.396@.TK2MSFTNGP12.phx.gbl...
> Correct. SQL Server does the backup, not the client from where you run the
client app to send the BACKUP
> commend to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
seen[vbcol=seagreen]
remote[vbcol=seagreen]
I[vbcol=seagreen]
SQL[vbcol=seagreen]
use[vbcol=seagreen]
RESTORE[vbcol=seagreen]
you[vbcol=seagreen]
service.[vbcol=seagreen]
write[vbcol=seagreen]
with[vbcol=seagreen]
>
Remote Database backup/restore
Anyone know if this can be done over TCP/IP connection?
Thanks in advance..
-BB
Hi,
Yes, You can do that. All you have to do is , create a Alias for the SQL
server using TCP/IP protocol, Ip address and Port number.
Using that Alias name register the SQL server in enterprise manager or use
Query Analyzer to connect.
After that you could run the BACKUP DATABASE command to backup and RESTORE
DATABASE command to restore the database.
To backup the file remotely or restore the file from remote locaton you have
to start the SQL Server service
using an Domain OS user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Anyone know if this can be done over TCP/IP connection?
> Thanks in advance..
> -BB
>
|||Hi Hari,
I had gotten this already. So the only file systems that can EVER be seen
are the ones available to the Server .. no matter where you execute the
BACKUP from? For example if I want to execute the backup from a remote
site, and put the dump file on client that is executing the BACKUP, then I
MUST have a network share enabled on the remote client computer?
Many thnx again,
-BB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, You can do that. All you have to do is , create a Alias for the SQL
> server using TCP/IP protocol, Ip address and Port number.
> Using that Alias name register the SQL server in enterprise manager or use
> Query Analyzer to connect.
> After that you could run the BACKUP DATABASE command to backup and RESTORE
> DATABASE command to restore the database.
> To backup the file remotely or restore the file from remote locaton you
have
> to start the SQL Server service
> using an Domain OS user which got write access to the remote share.
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option.
> There you give a Valid Domain OS user and password to start the service.
Now
> stop and start the MSSQL Serevr service.
> Note: That domain user should have previlages in the remote share to write
> the file as well as prev. to start the SQL server.
> After this you try to execute the Backup database command in Query
> Analyzer:-
>
> Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> init ( With init will overwrite the backup file every time)
> Thanks
> Hari
> MCDBA
>
>
> Thanks
> Hari
> MCDBA
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
>
|||Correct. SQL Server does the backup, not the client from where you run the client app to send the BACKUP
commend to SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hi Hari,
> I had gotten this already. So the only file systems that can EVER be seen
> are the ones available to the Server .. no matter where you execute the
> BACKUP from? For example if I want to execute the backup from a remote
> site, and put the dump file on client that is executing the BACKUP, then I
> MUST have a network share enabled on the remote client computer?
> Many thnx again,
> -BB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> have
> sercice--
> Now
>
|||ok thx
-BB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9njHXseEHA.396@.TK2MSFTNGP12.phx.gbl...
> Correct. SQL Server does the backup, not the client from where you run the
client app to send the BACKUP
> commend to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
seen[vbcol=seagreen]
remote[vbcol=seagreen]
I[vbcol=seagreen]
SQL[vbcol=seagreen]
use[vbcol=seagreen]
RESTORE[vbcol=seagreen]
you[vbcol=seagreen]
service.[vbcol=seagreen]
write[vbcol=seagreen]
with
>
Thanks in advance..
-BB
Hi,
Yes, You can do that. All you have to do is , create a Alias for the SQL
server using TCP/IP protocol, Ip address and Port number.
Using that Alias name register the SQL server in enterprise manager or use
Query Analyzer to connect.
After that you could run the BACKUP DATABASE command to backup and RESTORE
DATABASE command to restore the database.
To backup the file remotely or restore the file from remote locaton you have
to start the SQL Server service
using an Domain OS user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Anyone know if this can be done over TCP/IP connection?
> Thanks in advance..
> -BB
>
|||Hi Hari,
I had gotten this already. So the only file systems that can EVER be seen
are the ones available to the Server .. no matter where you execute the
BACKUP from? For example if I want to execute the backup from a remote
site, and put the dump file on client that is executing the BACKUP, then I
MUST have a network share enabled on the remote client computer?
Many thnx again,
-BB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, You can do that. All you have to do is , create a Alias for the SQL
> server using TCP/IP protocol, Ip address and Port number.
> Using that Alias name register the SQL server in enterprise manager or use
> Query Analyzer to connect.
> After that you could run the BACKUP DATABASE command to backup and RESTORE
> DATABASE command to restore the database.
> To backup the file remotely or restore the file from remote locaton you
have
> to start the SQL Server service
> using an Domain OS user which got write access to the remote share.
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option.
> There you give a Valid Domain OS user and password to start the service.
Now
> stop and start the MSSQL Serevr service.
> Note: That domain user should have previlages in the remote share to write
> the file as well as prev. to start the SQL server.
> After this you try to execute the Backup database command in Query
> Analyzer:-
>
> Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
> init ( With init will overwrite the backup file every time)
> Thanks
> Hari
> MCDBA
>
>
> Thanks
> Hari
> MCDBA
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
> news:OlznK7qeEHA.3916@.TK2MSFTNGP11.phx.gbl...
>
|||Correct. SQL Server does the backup, not the client from where you run the client app to send the BACKUP
commend to SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Burgess" <bburgess66@.hotmail.com> wrote in message news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hi Hari,
> I had gotten this already. So the only file systems that can EVER be seen
> are the ones available to the Server .. no matter where you execute the
> BACKUP from? For example if I want to execute the backup from a remote
> site, and put the dump file on client that is executing the BACKUP, then I
> MUST have a network share enabled on the remote client computer?
> Many thnx again,
> -BB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWJgEreEHA.556@.tk2msftngp13.phx.gbl...
> have
> sercice--
> Now
>
|||ok thx
-BB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9njHXseEHA.396@.TK2MSFTNGP12.phx.gbl...
> Correct. SQL Server does the backup, not the client from where you run the
client app to send the BACKUP
> commend to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Burgess" <bburgess66@.hotmail.com> wrote in message
news:Ox0$yQseEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
seen[vbcol=seagreen]
remote[vbcol=seagreen]
I[vbcol=seagreen]
SQL[vbcol=seagreen]
use[vbcol=seagreen]
RESTORE[vbcol=seagreen]
you[vbcol=seagreen]
service.[vbcol=seagreen]
write[vbcol=seagreen]
with
>
Subscribe to:
Posts (Atom)