Mapped Drive not availible for backup

To save space on our database server I have shared a folder on our web
server, and mapped it as a drive (S on our database server. It shows up as
a drive in Windows Explorer, and in any file save dialog box, but is not
availible as a backup option from SQL Enterprise manager. If I type in
s:\DbBackups as the backup device and try to back up I get the following
error:
************************************************** ******
Cannot open backup device 'S:\DBBACKUPS\MyDB.bak'. Device error or device
off-line. See the SQL Server error log for more details. BACKUP DATABASE is
terminating abnormally.
************************************************** ******
The error log shows
************************************************** *******
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(6)
Event ID:17055
Date:9/15/2004
Time:4:17:47 PM
User:N/A
Computer:SYNCDB
Description:
3041 :
BACKUP failed to complete the command BACKUP DATABASE [DigitalDraw_Sys] TO
DISK = N'S:\DBBACKUPS\MyDB.bak' WITH NOINIT , NOUNLOAD , NAME = N'MyDB
backup', NOSKIP , STATS = 10, NOFORMAT
************************************************** *********
What is keeping me from backing up to this mapped drive?
Is it some sort of permissions issue? If so what acct do I need to grant
permissions for?
Any help is greatly appreciated.
-Dan
You can't use a mapped drive for backups but you can use the UNC name:
BACKUP DATABASE mydb TO DISK = '\\servername\share name\DBBACKUPS\MyDB.bak'
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0DD86426-C9C6-48B6-8DE8-0F64C592F07E@.microsoft.com...
> To save space on our database server I have shared a folder on our web
> server, and mapped it as a drive (S on our database server. It shows up
> as
> a drive in Windows Explorer, and in any file save dialog box, but is not
> availible as a backup option from SQL Enterprise manager. If I type in
> s:\DbBackups as the backup device and try to back up I get the following
> error:
> ************************************************** ******
> Cannot open backup device 'S:\DBBACKUPS\MyDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details. BACKUP DATABASE
> is
> terminating abnormally.
> ************************************************** ******
> The error log shows
> ************************************************** *******
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 9/15/2004
> Time: 4:17:47 PM
> User: N/A
> Computer: SYNCDB
> Description:
> 3041 :
> BACKUP failed to complete the command BACKUP DATABASE [DigitalDraw_Sys] TO
> DISK = N'S:\DBBACKUPS\MyDB.bak' WITH NOINIT , NOUNLOAD , NAME = N'MyDB
> backup', NOSKIP , STATS = 10, NOFORMAT
> ************************************************** *********
> What is keeping me from backing up to this mapped drive?
> Is it some sort of permissions issue? If so what acct do I need to grant
> permissions for?
> Any help is greatly appreciated.
> -Dan
>
|||Hi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0DD86426-C9C6-48B6-8DE8-0F64C592F07E@.microsoft.com...
> To save space on our database server I have shared a folder on our web
> server, and mapped it as a drive (S on our database server. It shows up
> as
> a drive in Windows Explorer, and in any file save dialog box, but is not
> availible as a backup option from SQL Enterprise manager. If I type in
> s:\DbBackups as the backup device and try to back up I get the following
> error:
> ************************************************** ******
> Cannot open backup device 'S:\DBBACKUPS\MyDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details. BACKUP DATABASE
> is
> terminating abnormally.
> ************************************************** ******
> The error log shows
> ************************************************** *******
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 9/15/2004
> Time: 4:17:47 PM
> User: N/A
> Computer: SYNCDB
> Description:
> 3041 :
> BACKUP failed to complete the command BACKUP DATABASE [DigitalDraw_Sys] TO
> DISK = N'S:\DBBACKUPS\MyDB.bak' WITH NOINIT , NOUNLOAD , NAME = N'MyDB
> backup', NOSKIP , STATS = 10, NOFORMAT
> ************************************************** *********
> What is keeping me from backing up to this mapped drive?
> Is it some sort of permissions issue? If so what acct do I need to grant
> permissions for?
> Any help is greatly appreciated.
> -Dan
>

Manipulating Text,nText data types filed in tsql

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..

EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO

As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".

DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566

So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.

Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.

I would appreciate if any body respond to this.

Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.

So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:

http://www.sommarskog.se/dynamic_sql.html

|||Thanks, I had to break the query into pieces to get this working.

Management Studio, Save as... dialog before running External Tools

Hi.

In Management Studio I have defined some external tools. When I call my external tool from Tools menu and I have query scripts that are not saved I get "Save File As..." dialog for EVERY not saved script before running my External Tool. This is frustrating because sometimes I have a lot of not saved scripts and my External Tool doesn't need saved script files. How to run External tool in Mangement Studio without this frustrating "Save File As..." dialog box?

Kamil

I get the same thing. I can click cancel and the tool still opens, but it is annoying.