?
Solved

SQL Maintenance Plan - backup to another server

Posted on 2015-02-13
11
Medium Priority
?
95 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
  • 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 53

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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