Backup and Restore to 2nd Server

Hi,
Please follow the link
http://www.codeproject.com/KB/database/assiduous.aspx

Thanks

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.

is there no inbuilt process within SQL Server Management Studio?

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.

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.

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

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.

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.

Make sure you don't have the < or > anywhere.

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

Once you have restored the database you will have to reconcile any orphan users.  Read up on the sp_change_users_login

Thank you.