March 11, 2014 at 12:51 am
I need to take backup of sql server database and to do so ,I have two options:
1-ON another machine on network (Do i need to change SQL SERVER Account Settings as mentioned in link : how-to- backup-sql-databases-to-a-network-share[/url]
)
OR
2- Backup database on another machine Off network (Do i need to use FTP for that )
Need to know step by step instructions,which one is preferred and ? How to achieve that
March 11, 2014 at 4:40 am
The best approach is to take the backup locally on the server with the SQL Server instance, and then copy that backup across the network. The reason is that network hops like that can cause serious slow downs on the backup process itself leading to resource contention.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2014 at 5:00 am
Ok..Can I use SQL Backup Pro 7 to backup schedule ?
March 11, 2014 at 7:21 am
Sure. If you're using that you can even have it do the copy across the network for you. Step 3 of the Wizard when you're setting up backups includes "Copy backup to network." That's one of the easiest ways to get the job done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 12:08 am
I used SQL Backup Pro 7 on My Pc to Backup my local sql database (i.e. localserver) to both the desired servers (eg. server1 and server2) which is a sucess.
but When I tryed to take the backup of :
1- server1 on My PC drive -----Cant browse the resourse and you dont have permissions to do so....
2- server2 on My PC drive -----Cant browse the resourse and you dont have permissions to do so...
3- server1 on server2-----Cant browse the resourse and you dont have permissions to do so....
2- server2 on server1 -----Cant browse the resourse and you dont have permissions to do so....
I assume that something is related to permissions for copy backup ,I really dont have any knowledge related to network ,permissions etc.
what to do 🙁
ERROR:
Performing full backups - Successful
------------------------------------
This operation completed with warnings.
Backing up SAMTEST2 (full database) on SQL2008 instance to:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\FULL_SQL2008_SAMTEST2_20140311_175828.sqb
Database size : 954.125 MB
Compressed data size: 105.960 MB
Compression rate : 88.89%
Processed 61760 pages for database 'SAMTEST2', file 'SAM_ERP' on file 1.
Processed 1 pages for database 'SAMTEST2', file 'SAM_ERP_log' on file 1.
BACKUP DATABASE successfully processed 61761 pages in 16.131 seconds (29.911 MB/sec).
Warning 141: COPYTO error: Unable to copy C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\FULL_SQL2008_SAMTEST2_20140311_175828.sqb to \\192.168.0.206\f$\DBBackup\TestDatabase\Test\FULL_SQL2008_SAMTEST2_20140311_175828.sqb (3/11/2014
5:59:45 PM: Copy re-attempt 1:
3/11/2014 6:00:15 PM: Copy re-attempt 2:
3/11/2014 6:00:45 PM: Copy re-attempt 3:
3/11/2014 6:01:15 PM: Copy re-attempt 4:
3/11/2014 6:01:45 PM: Copy re-attempt 5:
3/11/2014 6:02:15 PM: Copy re-attempt 6:
3/11/2014 6:02:45 PM: Copy re-attempt 7:
3/11/2014 6:03:15 PM: Copy re-attempt 8:
3/11/2014 6:03:45 PM: Copy re-attempt 9:
3/11/2014 6:04:15 PM: Copy re-attempt 10:
3/11/2014 6:04:15 PM: COPYTO error: Failed to create folder: \\192.168.0.206\f$\DBBackup\TestDatabase\Test\).
SQL Backup exit code: 141
while I try full backup and Folder already exist at that drive
March 13, 2014 at 6:12 am
It all has to do with permissions. I suspect your SQL Server instances are running under local accounts instead of under networked accounts. You'll have issues trying to communicate across the network with that type of setup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 7:03 am
Can I resolve it without changing the sql server account settings or any other tool to be used for that purpose ???
Thanks for repling 🙂
March 13, 2014 at 7:12 am
If you have an account that has access to all the file shares that your backups are going to, you could use that account to run a powershell or batch command to move the backups around. But there's nothing magic. In order to communicate between servers, you have to have permissions to communicate between servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 8:12 am
maida_rh (3/13/2014)
I used SQL Backup Pro 7 on My Pc to Backup my local sql database (i.e. localserver) to both the desired servers (eg. server1 and server2) which is a sucess.but When I tryed to take the backup of :
1- server1 on My PC drive -----Cant browse the resourse and you dont have permissions to do so....
2- server2 on My PC drive -----Cant browse the resourse and you dont have permissions to do so...
3- server1 on server2-----Cant browse the resourse and you dont have permissions to do so....
2- server2 on server1 -----Cant browse the resourse and you dont have permissions to do so....
I assume that something is related to permissions for copy backup ,I really dont have any knowledge related to network ,permissions etc.
what to do 🙁
ERROR:
Performing full backups - Successful
------------------------------------
This operation completed with warnings.
Backing up SAMTEST2 (full database) on SQL2008 instance to:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\FULL_SQL2008_SAMTEST2_20140311_175828.sqb
Database size : 954.125 MB
Compressed data size: 105.960 MB
Compression rate : 88.89%
Processed 61760 pages for database 'SAMTEST2', file 'SAM_ERP' on file 1.
Processed 1 pages for database 'SAMTEST2', file 'SAM_ERP_log' on file 1.
BACKUP DATABASE successfully processed 61761 pages in 16.131 seconds (29.911 MB/sec).
Warning 141: COPYTO error: Unable to copy C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\FULL_SQL2008_SAMTEST2_20140311_175828.sqb to \\192.168.0.206\f$\DBBackup\TestDatabase\Test\FULL_SQL2008_SAMTEST2_20140311_175828.sqb (3/11/2014
5:59:45 PM: Copy re-attempt 1:
3/11/2014 6:00:15 PM: Copy re-attempt 2:
3/11/2014 6:00:45 PM: Copy re-attempt 3:
3/11/2014 6:01:15 PM: Copy re-attempt 4:
3/11/2014 6:01:45 PM: Copy re-attempt 5:
3/11/2014 6:02:15 PM: Copy re-attempt 6:
3/11/2014 6:02:45 PM: Copy re-attempt 7:
3/11/2014 6:03:15 PM: Copy re-attempt 8:
3/11/2014 6:03:45 PM: Copy re-attempt 9:
3/11/2014 6:04:15 PM: Copy re-attempt 10:
3/11/2014 6:04:15 PM: COPYTO error: Failed to create folder: \\192.168.0.206\f$\DBBackup\TestDatabase\Test\).
SQL Backup exit code: 141
while I try full backup and Folder already exist at that drive
Is the sql server agent service running under a domain account, local account, or builtin account?
Are all machines a member of the same domain?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply