Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Maintenance Plan - backup to another server

Posted on 2015-02-13
11
Medium Priority
?
90 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 52

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

618 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