?
Solved

SQL Maintenance Plan - backup to another server

Posted on 2015-02-13
11
Medium Priority
?
82 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 2000 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 51

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

801 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