Backup ?

I do this to backup the database on our production server
BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='DP2'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='DP2')
RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
WITH FILE = @.i
It works fine. However if I change to a mapped drive it gives me the
following error. I had agent running under the system account with failures
so thought I would change to my account, it also fails. I have full control
of that directory from this machine in windows. What gives?
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.Hi
Mapped drives are not supported. You have to use UNC paths.
Regards
Mike
"jcantley" wrote:
> I do this to backup the database on our production server
> BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
> NOFORMAT DECLARE @.i INT
> select @.i = position from msdb..backupset where database_name='DP2'and
> type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
> where database_name='DP2')
> RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> WITH FILE = @.i
> It works fine. However if I change to a mapped drive it gives me the
> following error. I had agent running under the system account with failures
> so thought I would change to my account, it also fails. I have full control
> of that directory from this machine in windows. What gives?
> Executed as user: DP2SQL02\jcantley. Cannot open backup device
> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
> is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.|||Mike,
Here is what I get when i use UNC. We are running active dir could that be
an issue? I am running this under my account but would like to just run it
under system, but what ever works.
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line. See the
SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated
statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup
device '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201)
VERIFY DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Mapped drives are not supported. You have to use UNC paths.
> Regards
> Mike
> "jcantley" wrote:
> > I do this to backup the database on our production server
> >
> > BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> > WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
> > NOFORMAT DECLARE @.i INT
> > select @.i = position from msdb..backupset where database_name='DP2'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
> > where database_name='DP2')
> > RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> > WITH FILE = @.i
> >
> > It works fine. However if I change to a mapped drive it gives me the
> > following error. I had agent running under the system account with failures
> > so thought I would change to my account, it also fails. I have full control
> > of that directory from this machine in windows. What gives?
> >
> > Executed as user: DP2SQL02\jcantley. Cannot open backup device
> > 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> > log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
> > terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
> > is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
> > 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> > log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
> > terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.|||Hi
Have you verified, that when you log on to the server as the account that
runs SQL server and Agent Service, then the folder is available and you have
write access to the folder?
What about the syntax for the TO DISK = ... is that correct? You specify TO
DISK = \\dp2sql01\BACKUP\DailyBackup - could it be that SQL reads this as a
folder rather than as a file? What if you try with ...TO DISK ='\\dp2sql01\BACKUP\DailyBackup.bak' ?
Regards
Steen
StiderGuy wrote:
> Mike,
> Here is what I get when i use UNC. We are running active dir could
> that be an issue? I am running this under my account but would like
> to just run it under system, but what ever works.
> Executed as user: DP2SQL02\jcantley. Cannot open backup device
> '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
> See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE
> 42000] (Error 3013) Associated statement is not prepared [SQLSTATE
> HY007] (Error 0) Cannot open backup device
> '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
> See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201) VERIFY DATABASE is terminating abnormally. [SQLSTATE
> 42000] (Error 3013). The step failed.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Mapped drives are not supported. You have to use UNC paths.
>> Regards
>> Mike
>> "jcantley" wrote:
>> I do this to backup the database on our production server
>> BACKUP DATABASE [DP2] TO DISK =>> N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup' WITH INIT , NOUNLOAD
>> , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10, NOFORMAT
>> DECLARE @.i INT
>> select @.i = position from msdb..backupset where
>> database_name='DP2'and type!='F' and backup_set_id=(select
>> max(backup_set_id) from msdb..backupset where database_name='DP2')
>> RESTORE VERIFYONLY FROM DISK =>> N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup' WITH FILE = @.i
>> It works fine. However if I change to a mapped drive it gives me the
>> following error. I had agent running under the system account with
>> failures so thought I would change to my account, it also fails. I
>> have full control of that directory from this machine in windows.
>> What gives?
>> Executed as user: DP2SQL02\jcantley. Cannot open backup device
>> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL
>> Server error log for more details. [SQLSTATE 42000] (Error 3201)
>> BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error
>> 3013) Associated statement is not prepared [SQLSTATE HY007] (Error
>> 0) Cannot open backup device 'V:\DailyDbBackup'. Device error or
>> device off-line. See the SQL Server error log for more details.
>> [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is terminating
>> abnormally. [SQLSTATE 42000] (Error 3013). The step failed.