SQL Server 2014 Restore failure

if the database exists, membership of dbcreator role should be sufficient (https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017)

One simple way is backup by using T-SQL

Create a Full Database Backup using Transact- SQL or T-SQL to restore the database from .BAK file

1. Type the BACKUP DATABASE statement which creates Full Database Backup using following specifications:

   RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'

   • Name of Database to backup
   • Place where to write the Full Database backup
   • The Syntax for full DB-backup is:

Hi, 

Kindly make sure change the DB to an offline or single user to restore properly some time if it's in use the backup will not restore.

I have had no problem doing a db restore to a production SQL server by simply stopping the IIS service first. I also make sure the option to overwrite (replace) is set as part of the restore.
Hope this helps.

If doing a restore to a different SQL Server (not the one backed up) e.g., restoring a staging SQL Server db to production SQL Server db or the other way the Login and dbUser GUIDs will not match. To correct that you will need to sync the user with the login by running the script:

USE [database]
GO
EXEC sp_change_users_login 'update_one', '[loginname]', '[username]'
GO 

Generally the loginname and username will be the same.

Hope this helps

Hi, 

Kindly make sure change the DB to an offline or single user to restore properly some time if it's in use the backup will not restore.