how to take backup from one server to remote serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

how to take backup from one server to remote serve

Author

Topic

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-12 : 12:25:36

Hi
I want to take backup of rapport database.
and copy those .bak files from this local server to remote server automatically.

can any one help me plzzzzzzz.........

Thanks in advance
Madhu.......

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-12 : 13:20:19

Did you check Backup command in Booksonline?

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-13 : 00:54:06

no sodeep
will u plz.. explain me clearly....

Thanks...
Madhu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 01:34:55

BACKUP DATABASE rapport
TO DISK = 'F:\Backup\rapport.bak'

EXEC master.dbo.xp_cmdshell 'xcopy F:\Backup\rapport.bak \\RemoteServer\Share\'

Or

BACKUP DATABASE rapport
TO DISK = '\\RemoteServer\Share\rapport.bak'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-13 : 01:47:13

hi tara
thanks for ur response..

I need to take backup regularly for every 2 hours.
so it need to create new file in the specified folder every time.
how can i do this...

Thanks & Best Regards
Madhu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 01:49:41

Hopefully you are talking about transaction log backups as you won't want full backups every 2 hours.

Check out my isp_Backup which can be found here:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

It'll create new files each time.

If you decide to use it, be sure to either subscribe to my blog or check back for updates as I'll be posting a change to it in the next few weeks. I update it a few times per year with features, bug fixes, and requests.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-13 : 02:30:12

hi tara
how can i give the remote server path..

Thanks
Madhu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 02:35:49

Just pass a UNC path to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-13 : 02:55:04

hi tara,
u UNC path means can i give it with IP address

Thanks
Madhu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 02:56:21

Yes, but it needs to be in a specific format:

\\ServerName\ShareName\...
\\IPAdress\ShareName\...
\\AliasName\ShareName\...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-13 : 03:24:00

Hi Tara
one final question ?

I given the path like this
exec isp_Backup @path='\\192.168.0.16\test', @dbType = '-Rapport', @bkpType = 'Full', @retention = -1, @liteSpeed = 'N'

It is giving the following error

Msg 3201, Level 16, State 1, Procedure isp_Backup, Line 361
Cannot open backup device '\\192.168.0.16\test\Rapport\Rapport_20080613124823.BAK'. Operating system error 1265(The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.).
Msg 3013, Level 16, State 1, Procedure isp_Backup, Line 361
BACKUP DATABASE is terminating abnormally.

suggest me...

Thanks & Best Regards
Madhu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 03:27:13

Make sure that the SQL Server service account has write access to the Rapport directory on 192.168.0.16 and also it has access to the test share.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhu.maddula
Starting Member

10 Posts

Posted - 2008-06-13 : 03:47:18

Hi tara
sorry for disturbing u..

where can check for SQL Server service account write access permissions ??????

and if it don't has permission How can i set the write access permissin.

Thanks & Best Regards
Madhu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 13:12:21

If you don't know how to do it, then please ask a server administrator to help you.

To be honest though, I've never tested my isp_Backup with a UNC path. I pointed you to my code as you asked how to create backups every day using different file names.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources