February 9, 2011 at 12:22 am
hi
i want to take the backup from my server to localdisk , is it possible in 2005 , if so please guide me how should i do.
February 9, 2011 at 12:46 am
What backups are you referring to?
M&M
February 9, 2011 at 1:41 am
I am refere to database backup
and the syntex is
BACKUP DATABASE [MyAddressBook] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyAddressBook.bak' WITH NOFORMAT, NOINIT, NAME = N'MyAddressBook-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
the disk is showing the path where the server is location , if i say disk = N'\\MYMACHINENAME\FILENAME'
the error it show is
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
February 9, 2011 at 2:03 am
You won't be able to take the backup directly on to your local machine.
First let the backup command run to take the backup on server location
and then copy it over to your local machine.
M&M
February 9, 2011 at 2:17 am
If the server does not have the space . then what should be done.
February 9, 2011 at 2:27 am
Check for the files that could be removed from the backup drive. Old backup files, unwanted files etc.
M&M
February 9, 2011 at 5:00 am
You can back up to network shares, but the SQL Server service startup account needs to have read/write rights to that network share.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 9, 2011 at 6:00 am
mohammed moinudheen (2/9/2011)
You won't be able to take the backup directly on to your local machine.First let the backup command run to take the backup on server location
and then copy it over to your local machine.
I'm sorry, that's not entirely accurate.
You can take a backup to your local machine, or any other machine, if the security context under which SQL Server is running has access to that location.
I don't recommend performing backups across the network. They usually perform more slowly and are more likely to fail due to things beyond your control. But you absolutely can do it.
"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
February 9, 2011 at 7:50 am
Thank you Grant for the clarification.
Usually, I had not taken backups this way 🙂
Thank you Again.
M&M
February 9, 2011 at 8:01 am
mohammed moinudheen (2/9/2011)
Thank you Grant for the clarification.Usually, I had not taken backups this way 🙂
Thank you Again.
I agree with you. I don't think it's a good way to get your backups 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
February 9, 2011 at 8:45 am
Grant is correct, but a clarification. The backup runs from the server, so
BACKUP DATABASE MYDatabase to disk = 'c:\mybackup.bak'
runs to the C: drive on the server, not your workstation. The server, and server service account, cannot "see" your local drives except through a UNC path. So to back up from a server to my PC, named "Steve", I must run:
BACKUP DATABASE MYDatabase to disk = '\\Steve\c$\mybackup.bak'
Note that permissions must exist, and any network hiccups can cause this to fail.
If you don't have space on the server for any backup, you have a problem. I would strongly suggest you argue with your manager for money for drives to get backups onto the server.
February 9, 2011 at 9:13 am
Steve Jones - SSC Editor (2/9/2011)
Grant is correct, but a clarification. The backup runs from the server, so
BACKUP DATABASE MYDatabase to disk = 'c:\mybackup.bak'runs to the C: drive on the server, not your workstation. The server, and server service account, cannot "see" your local drives except through a UNC path. So to back up from a server to my PC, named "Steve", I must run:
BACKUP DATABASE MYDatabase to disk = '\\Steve\c$\mybackup.bak'Note that permissions must exist, and any network hiccups can cause this to fail.
If you don't have space on the server for any backup, you have a problem. I would strongly suggest you argue with your manager for money for drives to get backups onto the server.
Excellent point. Thanks for point it out. I should have mentioned it.
"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
February 9, 2011 at 9:24 am
I believe the OP knew that he would need to provide a network share path in order to save the backups to the remote share. In his original post, he did mention the following:
if i say disk = N'\\MYMACHINENAME\FILENAME'
the error it show is
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 9, 2011 at 9:38 am
Steve Jones - SSC Editor (2/9/2011)
Grant is correct, but a clarification. The backup runs from the server, so
BACKUP DATABASE MYDatabase to disk = 'c:\mybackup.bak'runs to the C: drive on the server, not your workstation. The server, and server service account, cannot "see" your local drives except through a UNC path. So to back up from a server to my PC, named "Steve", I must run:
BACKUP DATABASE MYDatabase to disk = '\\Steve\c$\mybackup.bak'Note that permissions must exist, and any network hiccups can cause this to fail.
If you don't have space on the server for any backup, you have a problem. I would strongly suggest you argue with your manager for money for drives to get backups onto the server.
Backing up to a file share in not necessarily a bad way to go, provided you have the network bandwidth, the network is stable, and the file servers you backup to can support it.
We backup a couple of hundred servers just this way, including some with databases in the 500 GB+ range, and do daily full backups and 15 minute transaction log backups for several thousand databases.
I would recommend that each SQL Server have at least a 1 GBit/sec network interface, and that you use some backup compression technology, especially for servers with a large amount of data to backup.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply