CODEWITHSUNDEEP
sql-server
sandalone
sandalone

Reputation: 41759

Attach .mdf file located on desktop?

In SQL Server 2008 I can attach databases located only in its predefined folder (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA). On may occasions, especially when I read a book, I need to attach test database from desktop rather then copy each database every time I need it, but SQL Server does not allow me to access desktop.

Any workaround to solve this issue?

Upvotes: 1

Views: 1137

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40319

When a database file (data or log) is first created, it is (of course) located in a specific drive and folder. When a backup is created, this information is stored as part of the backup. A database RESTORE command will assume that the database is to be restored in the exact same location, unless instructed otherwise. To do this, in the RESTORE command under the "with" option, you must include the "move" option. It looks something like this:

RESTORE ...
 with 
   move '<logcalFileName>' to 'physicalFileName'
  ,move '<logcalLogFileName>' to 'physicalLogFileName'

One move must be included for each file to be so moved, so you usually end up with at least two of these clauses. The tricky part is that you must know the database files' logical names. These can be found via sp_helpFile on an attached database, and 

RESTORE FILELISTONLY
 from disk = '<backupFile>'

On an existing backup.

(I'm sure all this can be done somehow with the SSMS backup/restore GUIs. I switched over to TSQL-based scripts years ago, to provide quick and flexible access to all the features wrapped in the backup and restore commands.)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

It's probably a matter of granting the account running the SQL service appropriate permissions to your desktop folder (C:\Documents and Settings\YourLogin\Desktop). But, rather than use a location like Desktop that is specific to your login and possibly inaccessible to the account running the SQL service, why not use a common holding location for these files? Something like C:\AdHocDBs or whatever you want to call it.

Upvotes: 1

Related Questions