2

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?

  • Why are you trying to backup this database? Are you trying to move it? – swasheck Oct 25 '17 at 1:06
  • 4
    Principle of Least Surprise: please don't start calling your SQL Server backup file .mdf - everyone in the SQL Server community expects this to be a valid SQL Server data file - NOT a backup! Backups ought to have a .bak extension – marc_s Oct 25 '17 at 8:43
7

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)

| improve this answer | |
  • You can get a complete answer by highlighting the SQL Login that is needed to connect for the database in the target Instance. That is what is likely missing, along with the issues of orphaned SID accounts. – clifton_h Oct 25 '17 at 5:30
  • @clifton_h not necessarily, it's most likely a problem with the database not being accessible. The message is a generic one, but the error has a lot of facets, it's actually the state of the error that matters, see Aaron's article on this. – Marian Oct 25 '17 at 9:43
  • 1
    @Marian I agree, I didn't take into account the actual error message because the steps he/she followed are "Delete the db.mdf" and "Rename backup to db.mdf", so after this you just have destroyed the whole database and all the bets are off. – Alberto Martinez Oct 25 '17 at 20:36
  • RESTORE-ing seems to be ok. I just had to switch to another database (master) before restoring (since the db is in use). Thank you. – Iskra Oct 25 '17 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.