July 15, 2009 at 12:56 am
I am doing a backup of our database using the following command:
BACKUP DATABASE [Northwind] TO DISK = '\\externaldevice\databasename.bak',
DISK='\\serverB\databasename.bak'
WITH INIT , NOUNLOAD , NAME = N'northwind backup', NOSKIP , STATS = 10, NOFORMAT
This is a full backup, and i'm directing my backup to two destinations. first is an external device, and the second is another server.
Let's say we have servers A, B, and C.
When I run this command in server A, it succeeds. But when I execute the same command in server C, it fails and returns this error message:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\serverB\databasename.bak''. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Why do I get this error when I execute the above backup command in Server C, when it runs successfully in server A?
July 15, 2009 at 1:29 am
isabel (7/15/2009)
I am doing a backup of our database using the following command:BACKUP DATABASE [Northwind] TO DISK = '\\externaldevice\databasename.bak',
DISK='\\serverB\databasename.bak'
WITH INIT , NOUNLOAD , NAME = N'northwind backup', NOSKIP , STATS = 10, NOFORMAT
This is a full backup, and i'm directing my backup to two destinations. first is an external device, and the second is another server.
Just checking, but you do realise that your backup is getting striped across those two devices, half on each, and that you'll need both to restore? If you want the full backup on each device, you need to use the MIRROR TO clause
BACKUP DATABASE [Northwind]
TO DISK = '\\externaldevice\databasename.bak'
MIRROR TO DISK='\\serverB\databasename.bak'
WITH INIT , NOUNLOAD , NAME = N'northwind backup', NOSKIP , STATS = 10, NOFORMAT
When I run this command in server A, it succeeds. But when I execute the same command in server C, it fails and returns this error message:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\serverB\databasename.bak''. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Can you connect from server C to Server B? Does the SQL Server service account on Server C have read and write access to that share?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2009 at 2:16 am
Does your command for MIRROR TO DISK supported by MS SQL Server 2000? Because I get the following error when I execute it:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'MIRROR'.
July 15, 2009 at 2:24 am
isabel (7/15/2009)
Does your command for MIRROR TO DISK supported by MS SQL Server 2000?
No, but you posted in the 2005 forum, that's why I specified 2005 features.
On SQL 2000 if you want the entire backup on two places, you have to back it up to one and then copy the backup (file-system copy) to the other.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply