• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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
0
daiwhyte
Asked:
daiwhyte
1 Solution
 
Imran Javed ZiaCommented:
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
 
daiwhyteAuthor Commented:
is there no inbuilt process within SQL Server Management Studio?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
JaseemKCommented:
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
 
daiwhyteAuthor Commented:
Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now