How to backup DB Online

Question

How to backup DB Online

Viewing 12 posts - 46 through 56 (of 56 total)

You must be logged in to reply to this topic. Login to reply

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 1

I face to another problem:

When I run SP to restore StandbyDB from PrimaryDB via Server Linked, I have error:

Executed as user: XYZ\sqlagent. Could not relay results of procedure 'bgrestoredb' from remote server 'A.B.C.D'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.

Both server are: SQL2000 SP4, network is very good, performance is enough.

Data file is about 2 GB

If I test to restore with very small DB, it's OK

If I run restore SP from Standby server, it's OK

I check Server Linked, it's correct and OK

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I really don't think restoring a database over a linked server is a supported option. Pretty sure you need to be on the server where you are attempting the restore. You can restore to a server from a network share, but that isn't a linked server.

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 3

Let me explain more

My Primary server is "PRI", my Standby server is "STB". I put SP restoreDB in Master of STB

So from taskjob of PRI I run SP: exec [STB].master.dbo.restoreDB

ps. One Orange Chip Points: 29423 More actions · Answer 4

check if this user has sufficient permissions to restore the database.

and this user has sufficient permissions to access the file share containing the backup file

You can write something like this

create procedure Restore_db with execute as 'myDomain\User'

as

restore log test from disk='c:\testfull.bak'

with norecovery

here mydomain\user has admin rights on the target db.

Pradeep Singh

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 5

Yes, I map linking by SA user, and I tested with small DB, it restored OK

Problem with big data

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

You may be having network latency issues when attempting to restore a large database over the network. You may need to copy the file to a local directory on the target server and restore it from there. The copy over the network may handle network issues easier than the restore.

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 7

Yes, I already copy backup data file from PRI to STB then recover

So I don't thing problem by network, may be by Server Link

ps. One Orange Chip Points: 29423 More actions · Answer 8

thang_ngo_2002 (6/24/2009)
Yes, I already copy backup data file from PRI to STB then recover
So I don't thing problem by network, may be by Server Link

This may happen because of query timeout on the remote server.

This will forc the query for infinite wait.

sp_configure 'remote query timeout', 0

go

reconfigure with override

go

Ref: http://support.microsoft.com/kb/314530%5B/url%5D

Pradeep Singh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

The other option is to create a job on the standby server and after moving the file across, use sp_start_job on the standby server to start the job that runs the restore.

I did that to run import processes when an successful ftp completed on one server to import the file(s) on a second. Worked like a charm.

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 10

Lynn Pettis (6/24/2009)
The other option is to create a job on the standby server and after moving the file across, use sp_start_job on the standby server to start the job that runs the restore.
I did that to run import processes when an successful ftp completed on one server to import the file(s) on a second. Worked like a charm.

You're great. I've done. Thanks so much

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Glad it works for you, and thanks for the feedback.

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 12

Hi everybody

Should I back to this topic. I have some questions:

1) We can get current LSN of file created by Backup Log via topic http://msdn.microsoft.com/en-us/library/ms178536.aspx

But how to get LSN of current DB (our standby DB)? I want to know status of applying log file in standby DB.

2) I setup 3 jobs (Generate Log file from Primary DB; Copy log file to Standby Server; Apply log file to Standby DB) automatically via SQL Agent

Sometime I have problem with "RESTORE LOG ....", for example

Msg 4305, Level 16, State 1, Server ABCD, Procedure TBRestoreLog, Line 16

The log in this backup set begins at LSN 2761983000000032200001, which is too late to apply to the database. An earlier log backup that includes LSN 2761948000000185600001 can be restored.

Msg 3013, Level 16, State 1, Server ABCD, Procedure TBRestoreLog, Line 16 RESTORE LOG is terminating abnormally.

My Stored Procedure is:

RESTORE LOG ....
if (@@ERROR<>0)
begin
ALERT TO ADMIN ........
end

But "RESTORE LOG ...." has problem, @@ERROR is still equal zero (0)

My question is how to recognize when Restore Log has problem?

Thanks