Attach .mdf file to a remote database

Question

I have a .mdf file stored in local.

I work remotely on server through ssms. I am in need to restore the .mdf file. Is it possible to attach such file to remotely working server?

Answer 1

Yes and no. It depends on your SKU (ironically, more expensive versions are less capable).

• SQL Server Standard / Enterprise: Yes, but but you can only use CREATE DATABASE with the DB path specified as a network share, which means:
  • You need CREATE DATABASE permission on the server (e.g. be an administrator or be assigned that right).
  • The network share needs appropriate ACLs for both NTFS and SMB for the user-account which the SQL Server is using.
  • The AttachDbFilename option is not supported by SQL Server Standard or Enterprise, only Express.
• SQL Server LocalDb: No. LocalDb only works locally. The clue's in the name.
• SQL Server Express: Maybe.
  • You can use the same CREATE DATABASE command as Standard Edition.
  • While you can use AttachDbFilename this only works for local files, you cannot specify a UNC share: https://social.msdn.microsoft.com/Forums/en-US/a5adeef5-93e5-409c-b476-238e2f6dfcd2/how-to-connect-database-from-shared-drive-in-c?forum=csharplanguage

So your only option for mounting an *.mdf remotely is to use a network share and to attach it using CREATE DATABASE, you cannot use AttachDbFilename.

Note that performance will be poor because SQL Server can't use the magic tricks it uses to obtain low-level disk access for faster IO.

https://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/

Answer 2

No, using ssms it is not possible.Either you can generate database scripts or upload the .mdf file on the remote db using your hosting admin panel and restore from there.