November 13, 2019 at 11:59 pm
We need to copy a large sql database backup that is generated daily to a new 2016 sql server in the same domain. I've looked at robocopy but it refuses to cooperate. It generates errors that make no sense to me.
The compressed database backup is about 15 gigs. We need to automate copying this backup to a different server.
Any recommendations? Maybe the export function in Sql Server?
Blessings
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 14, 2019 at 12:29 am
Why not just take the backup in that server?
Just share a folder/drive on that server and give the sql server service account permissions to write there.
November 14, 2019 at 9:48 am
Hi,
if you like powershell, take a look at this page:
dbatools.io
There are a lot of usefull powershell command, one of them is backup & restore, and it is quite simple to use.
Kind regards,
Andreas
November 14, 2019 at 10:24 am
Running Robocopy from the SQL Agent works fine.
You need to create a proxy with rights to both the backup directory and the share:
USE [msdb]
GO
CREATE CREDENTIAL YourBackupUser WITH IDENTITY = 'YourDomain\YourBackupUser'
,SECRET = 'password';
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'YourBackupUser',@credential_name=N'YourBackupUser',
@enabled=1
GO
-- CMD
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'YourBackupUser', @subsystem_id=3
GO
-- PowerShell
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'YourBackupUser', @subsystem_id=12
GO
If you are having problems with the ACLs then the /B switch will ignore them.
If are running Robocopy from PowerShell first do something like Set-Location C:\. (When SQLPS starts the location defaults to the SQL Server path and it can get confused with File System paths.)
The following are good references for Robocopy:
November 14, 2019 at 4:26 pm
I like your idea. Are you saying run the backup on the original server and store it on the new one using a mapped drive?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 14, 2019 at 8:17 pm
You could also just perform the restore from the file located on the original server - instead of copying the file across to the new server. All that requires is sharing the folder where the backup file(s) reside and granting permission to the service account on the new server.
Backing up - or restoring - across the network may take longer than normal, and if that becomes an issue then copying the file across could help. With that said - I would restore a 90GB database (uncompressed) from an old 2005 system to a 2012/2016 system across the network with no problems. It did take a couple of hours...but the requirement was just to have the database available during business hours and a couple of hours each night for the restore was well within the allowable time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 14, 2019 at 8:24 pm
Thanks Jeffrey. You idea also has Merit. I'll do some homework.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 14, 2019 at 8:38 pm
Mapped drives can be difficult to work with because they are specific to each user, I'd recommend using the UNC name of the shared folder directly like:
\\servername\sharedfoldername\
15 GB is not really that large. You should be able to have a SQL Agent job that takes the backup directly to the UNC folder in step 1, or if you prefer to backup locally first for speed, then have step 2 copy it to the other server using a OperatingSystem step calling ROBOCOPY, or PowerShell step using Copy-Item.
Where Ken's idea of a proxy comes into play, if the user that SQL Agent runs as does not have access to the other server's shared folder, then you can setup a proxy using credentials of an account that does have access to the shared folder, and have the SQL Agent step "Run As:" the proxy user.
November 16, 2019 at 10:53 pm
I took a look at mapping drives and granting service account permissions. I use the default service accounts. After years mucking around with creating custom domain accounts, I've gotten fond of the defaults.
I don't support it is possible to grant permission to these default accounts to have read/write access to a folder on a different server?
If not, maybe I'm back to robocopy. As I said before, I find that utility a royal PITA. The prior postings suggesting Powershell and proxies did nothing to diminish my suffering. Can I buy a vowel? Other suggestions?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 18, 2019 at 5:52 pm
I believe when those local accounts come across the network to the shared folder they would be using the computer's name as the account name, not their own name. It's at least worth a try to setup permissions to the computer for the share, and try it that way.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply