Backup and Restore to 2nd Server

Hi,

Im looking to setup a automated process whereby each Friday at 12:45pm, the live server db is backed up and restored to a 2nd server.

How would this be done?

dw
daiwhyteAsked:
Who is Participating?
 
JaseemKConnect With a Mentor Commented:
Yes you will now need to add the restore on the remote server as a second step because so far we have only backed the database up on that server. This will have to be done from the second server so your second step will be independent from the backup task. Best solution is to put the restore statement within a stored procedure on the second server and call that from the original sched task as a second step.
HTH.
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
Please follow the link
http://www.codeproject.com/KB/database/assiduous.aspx

Thanks
0
 
Alpesh PatelAssistant ConsultantCommented:
Hey, SSIS (BIDS development ) is the best way. Use maintenance task in that Back up and resore using SQl script or Script task (.net code) are available.

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
daiwhyteAuthor Commented:
is there no inbuilt process within SQL Server Management Studio?
0
 
JaseemKCommented:
You can set up a SQL Agent scheduled task that runs every Friday at 12:45 which performs a database backup directly to the remote server. However, you need to ensure SQL Agent is running with a service account that has folder level privileges on the remote server (e.g. is an Administrator on the remote server).
The actual dump statement will be:
BACKUP DATABASE <liveDBName> TO DISK = '\\REMOTESRVR\D$\DumpFile.BAK'

HTH.
0
 
daiwhyteAuthor Commented:
Ok, can you go into more detail about creating the job? I have accounts which have folder level permission so it shouldnt be a problem.
0
 
daiwhyteAuthor Commented:
When I try an parse the command on this screen, I get an error


TITLE: Parse Command Text
------------------------------

The following errors were detected in the command text.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Incorrect syntax near '<'. (Microsoft SQL Server, Error: 102)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Experts.jpg
0
 
JaseemKCommented:
You shouldn't use the <> brackets - I just used these to show where you should put your database name. You should use it something like this:
BACKUP DATABASE name_of_your_database_here TO DISK = '\\REMOTESRVR\D$\DumpFile.BAK'
-- substitute name_of_your_database_here with your DB name and REMOTESRVR with the name of your remote server.
0
 
daiwhyteAuthor Commented:
Ok, done what you've advised and Ive been able to parse the command. When I run the job, I get the following error

 Executed as user: LOANSWAREHOUSE\ADMINSQL. Incorrect syntax near '<'. [SQLSTATE 42000] (Error 102).  The step failed.
0
 
JaseemKCommented:
Make sure you don't have the < or > anywhere.
0
 
daiwhyteAuthor Commented:
Ok, got that sorted. do I add a secondary step in this wizard to restore the DB to the 2nd Server or is this done on the 2nd Server SQL Management Console?

Thanks so far for your help JaseemK
0
 
Anthony PerkinsCommented:
Once you have restored the database you will have to reconcile any orphan users.  Read up on the sp_change_users_login
0
 
daiwhyteAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.