SQL Server – MDF Backup ‘Login Failed’ Error

backupsql serversql-server-express

I'm creating a VC# application using Visual Studio.
On startup, it opens a MDF file (SQL Server Express database).

When I execute

BACKUP DATABASE db.mdf TO DISK=backup.mdf

I get the backup.mdf created.

Then I do the following:

  1. Close the application
  2. Delete the db.mdf
  3. Rename backup.mdf to db.mdf
  4. Run the application

I get the error "Login failed for user 'PC\User'"

How can I solve this?

Best Answer

Backups in SQL Server don't work that way, they aren't just copies of the database files in another location but instead they are backups of the database content stored in a backup file with a different structure than the database files (you could make a backup of just a database file but even in that case the backup format would be different).

To perform a backup the most basic way is doing something like this:

BACKUP DATABASE DBName TO DISK='C:\Backup directory\DB_backup.bak' WITH FORMAT

The backup files can't be used directly by SQL Server, you need to restore it on a database first (either overwriting an existing database or creating a new one):

RESTORE DATABASE DBName FROM DISK='C:\Backup directory\DB_backup.bak' WITH REPLACE

You could need additional backup/restore parameters depending of the DB properties and the exact operation you are doing (also you can perform and restore backups from SQL Server Management Studio)

Related Question