MDF Backup: "Login failed" Error

Question

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?

Answer 1

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)