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
>

Mapped drive database backups

How would I schedule full database backups to backup to
another disk on a mapped shared network drive?
I have tried adding a disk dump device using
sp_addumpdevice. Then running the BACKUP DATABASE command
but I get the following error message:
Cannot open backup device 'pdmsdiskdump'. Device error or
device off-line.
Thanks,
Anjelina> How would I schedule full database backups to backup to
> another disk on a mapped shared network drive?
If you set up a mapped drive H: while logged in as user A, user A is the
only person who can see an H:!
Use \\UNCPath\, unless you have set SQL Server and agent to run as a domain
account with access to the share, and you set up the mapped drive while
logged in as that user.

mapped drive and backups

I mapped a drive to a different server, which has space, then tried to use
Ent. Manager to create a backup job to the mapped drive. Only, the mapped
isn't there only the local drives but the mapped appears locally to the OS.
I forgot something yes?
Thanks
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks
|||Hi
EM will not see mapped drives. Use T-SQL and backup to the UNC path
\\servername\sharename\file.bak.
Make sure that the SQL Server account is a domain account and has
permissions on the share.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks
|||This actually worked.
EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext'
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> EM will not see mapped drives. Use T-SQL and backup to the UNC path
> \\servername\sharename\file.bak.
> Make sure that the SQL Server account is a domain account and has
> permissions on the share.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
> message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>
>

mapped drive and backups

I mapped a drive to a different server, which has space, then tried to use
Ent. Manager to create a backup job to the mapped drive. Only, the mapped
isn't there only the local drives but the mapped appears locally to the OS.
I forgot something yes?
ThanksHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||Hi
EM will not see mapped drives. Use T-SQL and backup to the UNC path
\\servername\sharename\file.bak.
Make sure that the SQL Server account is a domain account and has
permissions on the share.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||This actually worked.
EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext'
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> EM will not see mapped drives. Use T-SQL and backup to the UNC path
> \\servername\sharename\file.bak.
> Make sure that the SQL Server account is a domain account and has
> permissions on the share.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
> message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
> >I mapped a drive to a different server, which has space, then tried to use
> > Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> > isn't there only the local drives but the mapped appears locally to the
> > OS.
> > I forgot something yes?
> > Thanks
>
>sql

mapped drive and backups

I mapped a drive to a different server, which has space, then tried to use
Ent. Manager to create a backup job to the mapped drive. Only, the mapped
isn't there only the local drives but the mapped appears locally to the OS.
I forgot something yes?
ThanksHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||Hi
EM will not see mapped drives. Use T-SQL and backup to the UNC path
\\servername\sharename\file.bak.
Make sure that the SQL Server account is a domain account and has
permissions on the share.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||This actually worked.
EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext'
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> EM will not see mapped drives. Use T-SQL and backup to the UNC path
> \\servername\sharename\file.bak.
> Make sure that the SQL Server account is a domain account and has
> permissions on the share.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote
in
> message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>
>

Mapped Drive

Can I make my "default location" for new databases be a mapped drive? When
I go into the properties and select the database tab and select the ...
box to list my drives, the shared drive does not show up. If I go ahead
and enter the path of where I want the files to be located on the "mapped"
drive, it accepts it, but does not use it when I create a new database.
My situation is I have 2 computers in a single room and I want then to be
pointed at the same file location for all databases... Both machines have
MSDE version of sql server loaded...Can I somehow point one machine to the
second machines SQL server such that we are working on the same database?
Any ideas/suggestions'> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
I don't think you can share databases between engines (even between
instances on the same machine).
Why not just point one machine's client tools to the other machine's SQL
Server? Then one machine will be working locally, and the other will be
working "remotely" so to speak. But they will be working on the same copy
of exactly one database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||No.
First, SQL does not support mapped drives for database or transaction log
files.
Second, when SQL does open a database file, it is completely exclusive for
that server instance. Even a second instance on the same host nod could not
access the same underlying database files.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns9495971974DBBJHeaveyBDUP@.207.46.248.16...
> Can I make my "default location" for new databases be a mapped drive?
When
> I go into the properties and select the database tab and select the ...
> box to list my drives, the shared drive does not show up. If I go ahead
> and enter the path of where I want the files to be located on the "mapped"
> drive, it accepts it, but does not use it when I create a new database.
> My situation is I have 2 computers in a single room and I want then to be
> pointed at the same file location for all databases... Both machines
have
> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
> Any ideas/suggestions'

Mapped Drive

Can I make my "default location" for new databases be a mapped drive? When
I go into the properties and select the database tab and select the ...
box to list my drives, the shared drive does not show up. If I go ahead
and enter the path of where I want the files to be located on the "mapped"
drive, it accepts it, but does not use it when I create a new database.
My situation is I have 2 computers in a single room and I want then to be
pointed at the same file location for all databases... Both machines have
MSDE version of sql server loaded...Can I somehow point one machine to the
second machines SQL server such that we are working on the same database?
Any ideas/suggestions'> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
I don't think you can share databases between engines (even between
instances on the same machine).
Why not just point one machine's client tools to the other machine's SQL
Server? Then one machine will be working locally, and the other will be
working "remotely" so to speak. But they will be working on the same copy
of exactly one database.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||No.
First, SQL does not support mapped drives for database or transaction log
files.
Second, when SQL does open a database file, it is completely exclusive for
that server instance. Even a second instance on the same host nod could not
access the same underlying database files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns9495971974DBBJHeaveyBDUP@.207.46.248.16...
> Can I make my "default location" for new databases be a mapped drive?
When
> I go into the properties and select the database tab and select the ...
> box to list my drives, the shared drive does not show up. If I go ahead
> and enter the path of where I want the files to be located on the "mapped"
> drive, it accepts it, but does not use it when I create a new database.
> My situation is I have 2 computers in a single room and I want then to be
> pointed at the same file location for all databases... Both machines
have
> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
> Any ideas/suggestions'

map drive

If my computer has a map drive, i want to delete it, how to do it? Is that right if i right click the map drive and click the disconnect?Yes, that should allow you to disconnect the mapped drive.

-PatP|||From a command prompt you can also run

net use z: /del

(replace z: with the mapped drive letter)|||From a command prompt you can also run

net use z: /del

(replace z: with the mapped drive letter)

well if we are in the sql server forum, how about

exec xp_cmdshell 'net use x: /delete'

Many data files, SAME Filegroup, SAME drive

Hello. I'm looking for any pros/cons to having multiple
files, SAME filegroup, SAME drive... Say you have a
database, where 4 data files reside on the one PRIMARY
Filegroup.
my_db_Data_1.MDF
my_db_Data_2.NDF
my_db_Data_3.NDF
my_db_Data_4.NDF
When you do an action like BULK INSERT a large amount of
data to a table, SQL Server will scatter that table's data
across the 4 data files.
I understand the reasons one might WANT to have 4 files on
teh same drive, like if they want to copy smaller data
files around, etc... But, performance-wise, I'd think it
would be at LEAST somewhat slower writing to 4 files, then
writing to ONE file on the same drive. Any ideas on that?
Also, wouldn't you have to deal with 4 shrinkings instead
of just one?
THanks, BruceBruce
What is a perfomance gain do you get by putting all files on the same
physical disk and the same filegroup?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:01b801c34bc6$55421db0$a401280a@.phx.gbl...
> Hello. I'm looking for any pros/cons to having multiple
> files, SAME filegroup, SAME drive... Say you have a
> database, where 4 data files reside on the one PRIMARY
> Filegroup.
> my_db_Data_1.MDF
> my_db_Data_2.NDF
> my_db_Data_3.NDF
> my_db_Data_4.NDF
> When you do an action like BULK INSERT a large amount of
> data to a table, SQL Server will scatter that table's data
> across the 4 data files.
> I understand the reasons one might WANT to have 4 files on
> teh same drive, like if they want to copy smaller data
> files around, etc... But, performance-wise, I'd think it
> would be at LEAST somewhat slower writing to 4 files, then
> writing to ONE file on the same drive. Any ideas on that?
> Also, wouldn't you have to deal with 4 shrinkings instead
> of just one?
> THanks, Brucesql

Managing Big Database

H
I have a Database over 50G. It increases 1G per day
Today I detach the current database and move the data&log file to E drive. Then I use DTS transfering those data to a new database on D drive. The database becomes 18G!!!! I check the tables, everything is there.
How did this happen? I thought there must be something about fragments. But I am not sure. Can anyone tell me
And is there any other way to defrag the database without taking off-line
thank you very muchDBCC ShrinkDatabase?
Most of the suggestions in http://www.aspfaq.com/2446 apply to any database.
Also see http://www.aspfaq.com/2471
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...
> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.
Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.
> How did this happen? I thought there must be something about fragments.
But I am not sure. Can anyone tell me?
> And is there any other way to defrag the database without taking off-line?
>
> thank you very much|||I have found the cause! It's because that I didn't build the index.|||I shrink database every day. The log file is quite small.|||and make sure you take backups of the database in order to be able to shrink
the transaction log in case of a full recovery model.
Check books online on dbcc shrinkdatabase, dbcc shrinkfile, and shrinking
the transaction log
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...
> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.
Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.
> How did this happen? I thought there must be something about fragments.
But I am not sure. Can anyone tell me?
> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

Managing Big Database

Hi
I have a Database over 50G. It increases 1G per day!
Today I detach the current database and move the data&log file to E drive. T
hen I use DTS transfering those data to a new database on D drive. The data
base becomes 18G!!!! I check the tables, everything is there.
How did this happen? I thought there must be something about fragments. But
I am not sure. Can anyone tell me?
And is there any other way to defrag the database without taking off-line?
thank you very muchDBCC ShrinkDatabase?
Most of the suggestions in http://www.aspfaq.com/2446 apply to any database.
Also see http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...

quote:

---

> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.

---

Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.

quote:

---

> How did this happen? I thought there must be something about fragments.

---

But I am not sure. Can anyone tell me?

quote:

---

> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

---

|||I have found the cause! It's because that I didn't build the index.|||I shrink database every day. The log file is quite small.|||and make sure you take backups of the database in order to be able to shrink
the transaction log in case of a full recovery model.
Check books online on dbcc shrinkdatabase, dbcc shrinkfile, and shrinking
the transaction log
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...

quote:

---

> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.

---

Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.

quote:

---

> How did this happen? I thought there must be something about fragments.

---

But I am not sure. Can anyone tell me?

quote:

---

> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

---