Solved

SQL Maintenance Plan - backup to another server

Posted on 2015-02-13
11
73 Views
Last Modified: 2015-12-05
Let me start by saying I am very new to SQL Server Management.  

We are using SQL 2005.

I need to create a Maintenance Plan that will perform backups but save them on another server.  We need to keep several months of backups.  Everything I have seen online has been to save it to the local server first and write a script to move a specific file name, but I need this to be dynamic because each filename will be unique with a timestamp, right?

If backups should be kept on a different server, why does it look like this is so difficult to do???
0
Comment
Question by:UniqueData
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40608607
>Everything I have seen online has been to save it to the local server first
That's because of the speed, writing locally is faster compared to writing on a remote server
>If backups should be kept on a different server
If both the servers are on the same network, create a share on the destination server and grant read/write permission to the SQL Server service account (I assume this is a domain account). Once you did this, you can tell SQL Backup task to write to this network share.
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40608630
More information on backing up to a network share can be found in Microsoft Technet.

I've always found that more problematic than writing a simple batch script with Robocopy to copy what I needed with wildcards.
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40610446
I just stumbled upon Mirror To.  This would work, right?

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
MIRROR TO DISK = '\\anotherServer\MirrorFile\AdventureWorks.bak'
WITH FORMAT
 GO
0
Independent Software Vendors: 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!

 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40610738
You mentioned that you were using SQL 2005.  The documentation indicates that for SQL 2005, "This option is available only in SQL Server 2005 Enterprise Edition and later versions."
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40611005
I believe we have enterprise. I will check on Tuesday. Is mirror "safe". Any downside for using mirror?
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40611023
The only downsize I remember reading about using the Mirror clause in backup is that if the mirrored location becomes unavailable, the whole backup operation fails.  (I.e. just losing the network drive where the mirror is being written will cause the whole operation to fail, and you also won't have a local backup.)
Otherwise, Mirror should also work, with the caveat that the operation will need permissions to the destination, as Aneesh mentioned before.
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40615036
Although we have Enterprise, It sounds like they don't want to use Mirror.  I have tried to google how to use RoboCopy inside a backup script but I can't seem to find instructions for a complete solution (backup then copy in one script???)

Sorry, I am extremely new to SQL Server and need some hand holding :(
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40615123
You might be able to create a separate job step that calls either a CmdShell or Powershell script to perform the robocopy, and it's certainly easier in SQL 2008 and above.  It would be two steps within the same SQL job.  Unfortunately, if a developer came to me, asking to do this on one of my production SQL Servers, I probably wouldn't let them.  (And I certainly wouldn't let them in SQL 2005, because it would probably involve enabling xp_cmdshell.)  They might allow it, and enable the function, in which case, the line would be:
exec xp_cmdshell 'ROBOCOPY.EXE <source> <destination> <options>'

Instead, I'd schedule/manage the robocopy script from Task Scheduler (or scheduled tasks if this is Windows 2003).
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 40615151
here is the one I use, I have put this script in a batch file and execute the batch file thru SQL agent. For my purpose, I need to mirror the backup folders, you can remove the '/MIR' switch

ROBOCOPY C:\SourceFolder  \\DestinationServer\DestinationFolder  /MIR /COPY:DT /LOG:"c:\temp\robocopy.txt"

    if %ERRORLEVEL% EQU 16 echo ***FATAL ERROR*** & goto end
    if %ERRORLEVEL% EQU 15 echo OKCOPY + FAIL + MISMATCHES + XTRA & goto end
    if %ERRORLEVEL% EQU 14 echo FAIL + MISMATCHES + XTRA & goto end
    if %ERRORLEVEL% EQU 13 echo OKCOPY + FAIL + MISMATCHES & goto end
    if %ERRORLEVEL% EQU 12 echo FAIL + MISMATCHES& goto end
    if %ERRORLEVEL% EQU 11 echo OKCOPY + FAIL + XTRA & goto end
    if %ERRORLEVEL% EQU 10 echo FAIL + XTRA & goto end
    if %ERRORLEVEL% EQU 9 echo OKCOPY + FAIL & goto end
    if %ERRORLEVEL% EQU 8 echo FAIL & goto end
    if %ERRORLEVEL% EQU 7 echo OKCOPY + MISMATCHES + XTRA & goto end
    if %ERRORLEVEL% EQU 6 echo MISMATCHES + XTRA & goto end
    if %ERRORLEVEL% EQU 5 echo OKCOPY + MISMATCHES & goto end
    if %ERRORLEVEL% EQU 4 echo MISMATCHES & goto end
    if %ERRORLEVEL% EQU 3 echo OKCOPY + XTRA & goto end
    if %ERRORLEVEL% EQU 2 echo XTRA & goto end
    if %ERRORLEVEL% EQU 1 echo OKCOPY & goto end
    if %ERRORLEVEL% EQU 0 echo No Change & goto end
    :end
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40616468
Everything I have seen online has been to save it to the local server first and write a script to move a specific file name, but I need this to be dynamic because each filename will be unique with a timestamp, right?
Only if you want to. You can even use only a single file.

If backups should be kept on a different server, why does it look like this is so difficult to do???
Isn't difficult at all. With the right solution (backups products, fast network and storage) you can achieve a solution easily. Of course the trade is a lot of money. Really a lot and that's why in small and in some medium companies the solution is to backup locally and move the backups later (during night and weekends) to a server.
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40627427
I have read some posts stating that xcopy is now reliable in regards to memory management.  I believe a post stated to use /u with xcopy.

Is this true?
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question