Getting Backups off local drives
-
Hi All
I have tried doing sql server backups to a file share and that has been taking too long. So I've decided to backup locally and then taking those backups and getting them off the server. For those that are doing this what do you use to get your backups off the server?
Thanks
Kathy
-
We use the ola hallengren scripts to backup to a local drive, then added a step to the job to move the files to the file share we want them stored at.
-
You could do it with a second step in the job that uses robocopy in a batch file. I a lot of clients that use that. Or SCHTASKS windowing scheduler functionality. Powershell or wscript/cscript is another option.
I note that if you do a local backup you are at risk of losing everything for the period of time that your files are not yet completely transferred off of the storage.
Oh, and have you looked into tuning your backups? There are a lot of things that can be done along those lines. Also evaluate your network and remote disks to see if those could be causing slowness and work to improve them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
There is a small risk with doing this, but I think the risk is higher writing straight to a network share. Guess I've seen way to many 'network errors' right in the middle of a big backup. Personally I think its a safer approach. Its more likely for the backup to finis if you write local and copy remote then to write the backup remote.
-
plamannkathy (12/8/2014)
Hi AllI have tried doing sql server backups to a file share and that has been taking too long. So I've decided to backup locally and then taking those backups and getting them off the server. For those that are doing this what do you use to get your backups off the server?
Thanks
Kathy
What edition of sql srever are you using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
I have sql server 6.5, 7.0, 2000, 2005, 2008, 2008R2, and 2012.
Thanks
Kathy
-
all are Standard Edition
-
I do think the risk is small (-ish), but unless we're talking domain hops, most networks these days should be able to handle running the backup across to a shared location.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
don't know if this helps, but i use a powershell script as a SQL Agent job step, which in turn calls robocopy to copy all my redgate *.sqb files
that job step uses Run As for a network accoutn which has access to the destination and the local folder.
$SourceLocation = "L:\SQLBackup\";
$DestinationLocation = "\\BackupServer\SHARE\CRITICALBACKUPS\GDC\SQL-BACKUPS\PRODUCTION" ;
#/E copy directories even if empty
#/NFL no file logging in $CaptureOutput
#/NDL no directory logging in $CaptureOutput
#/NJH : No Job Header.
#/NJS : No Job Summary.
#/R:0 retry 0 times on failure
#getting robocopy to work and double quoting the paths was a bitch. hardcoding is easier.
$CaptureOutput = robocopy "L:\SQLBackup" "*.sqb" "\\BackupServer\SHARE\CRITICALBACKUPS\GDC\SQL-BACKUPS\PRODUCTION" /NFL /NDL /NJH /NJS /E /R:0 ;
#write-verbose -Message "param1 $CaptureOutput" -verbose;
#check if there was an error
$Isfailure = $CaptureOutput -match "ERROR ";
if ($Isfailure)
{
throw("Failure in copying $SourceLocation files to $DestinationLocation. Details: $CaptureOutput");
}
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you so much for all of your input. I will have to look into the suggested solutions to see what works for each environment.
Thanks again
Kathy
-
we do this using either the Ola Hallengren job or a TSQL Backup command in the first step and a second step of type Operating system (cmdexec) which reads
xcopy "\\server\drive\*.*" "etworkarea\DBBackups\servernameBackups\*.*" /S /Y
I've also got a ssis job that tidies up old backups where the backup files are dated in the name so don't just get replaced. Or I use a version of the Ola Hallengren scripts that I bodged to not put the date and time on the filename so always use the same file. Our tape backups will have copied them off by the time they are overwritten.
-
Hi Lowell
I am trying your script. It is executing successfully but the backups are not being copied over. Have you experience this? I have to do some research into robocopy to see what I have to do to get it to work but any input would be wonderful.
Thanks
Kathy
-
well, there's a few pieces to this puzzle
1. You had to create a credential for a network user who has permissions
2. you had to create an operator in SQL Agent for the credential
3. You had to grant that operator access to specific subsystems (or all of them)
4. you had to make sure the Run As of the job step selected that operator.
my classic script for steps 1,2,3, adding a credential, operator and the grants is here; substitute your domain\user, and password for your real life example.
--Dependency: this credential must exist:
IF NOT EXISTS(SELECT * FROM sys.credentials WHERE name = 'TaskRunner')
BEGIN
CREATE CREDENTIAL TaskRunner WITH IDENTITY = 'MyDomain\SQLBackupNetworkTaskRunner', SECRET = 'NotTheRealPassword';
END
GO
USE msdb;
GO
--add my proxy
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysproxies WHERE name = 'NetworkTaskRunner')
BEGIN
EXECUTE msdb.dbo.sp_add_proxy @proxy_name=N'NetworkTaskRunner',@credential_name=N'TaskRunner',@enabled=1
END
--#################################################################################################
--each subsystem: add if not existing
--#################################################################################################
DECLARE @isql VARCHAR(2000),
@subsystemid VARCHAR(64)
DECLARE c1 CURSOR FOR
SELECT
subz.subsystem_id
FROM msdb.dbo.syssubsystems subz
LEFT OUTER JOIN (SELECT
mapz.subsystem_id
FROM msdb.dbo.sysproxysubsystem mapz
INNER JOIN msdb.dbo.sysproxies proxz
ON mapz.proxy_id = proxz.proxy_id
WHERE proxz.name = 'NetworkTaskRunner'
) X
ON subz.subsystem_id=X.subsystem_id
WHERE X.subsystem_id IS NULL
AND subz.subsystem_dll <> '[Internal]' --no proxy for internal methods!
OPEN c1
FETCH NEXT FROM c1 INTO @subsystemid
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql = 'EXECUTE msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N''NetworkTaskRunner'', @subsystem_id= ' + @subsystemid + ';'
PRINT @isql
EXEC(@isql)
FETCH NEXT FROM c1 INTO @subsystemid
END
CLOSE c1
DEALLOCATE c1
--each subsystem###############################################################################
with that in place, you'd see the new user in the proxys,and be able to select it as your Run As in your step:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
the beauty of robocopy is that it checks if the file was already moved/exists/changed;
it doesn't re-copy files that are already exist on the destination folder, which is very nice when you are copying big backups.
i'm not sure if XCOPY does the same...i thought xcopy would move files regardless, and need an overwrite /y flag, so robocopy's so much better for me.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell have a look at XXCOPY[/url].
Kathy there are some free or low cost things that will help.
First is SQLCMD. I use that all the time to run scripts with the -i option. Look in Books On Line or do a Google search.
There are various tools to move and copy files. RoboCopy has already been mentioned and that is a good one. I have used XXCOPY for years but it requires a license. In a Windows command file I have also used MOVE.
You said Standard Edition for your SQL servers. I have been faced with having to do things like you are talking about and SQL Agent was not an option. So ...
Write your backup scripts so that they run in SSMS and test them completely.
Next write a Windows command file that executes your script using SQLCMD and has the step to move your backup file to the destination using your favorite file move tool. Test it by double-clicking on the command file in Windows Explorer. Make sure that in your move operation that you do not use drive letters for anything that is not local to the machine where the command file will run
Then set up a Windows scheduled task to run your command file.
ATBCharles Kincaid
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply