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
Solved

SQL Maintenance Plan - backup to another server

Posted on 2015-02-13
11
68 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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