SQL Server Restore Error – Fix ‘WITH REPLACE’ Restore Failing with Same Logical Filenames

Good deal with getting the connection string out. Based on that and the error it looks like you'll need to change some things around with your SQL config.

First, SQL 2012 does not support the database compatibility level for SQL Server 7. You'll need to go back down to at least SQL Server 2005.

Based on that connection string info the app is for a SQL server named SCFRANGOS. You can try to create a host entry for SCFRANGOS on the machine you're running the app from and give it the IP of your SQL server, or create it in DNS.

The database name you need to use FVBraganca. Since you have both the mdf (data) and ldf (log) you can try to reattach the database to your SQL Server.

You also need to create a local user on the server and name it usuario. Then you need to create a Windows login on the SQL server for usario and that login needs to be added to the FVBraganca dabase as a user.

Alternatively, if you're comfortable with editing the binary you could edit your own connection information into the exe.

Steps:

1. Install SQL Server 2005 or earlier on your server. You can download SQL Server 2005 Express from here. Again, SQL 2005 is the newest version you can use that will support the SQL 7 database format.
2. Make sure the server where SQL Server is installed can be resolved as SCFRANGOS on your network from wherever you are running the application. You can add a host entry or an alias in DNS. Or you can add an entry in the hosts file where you will run the application (c:\windows\system32\drives\etc\hosts typically). When this step is complete you should be able to receive replies when doing a ping SCFRANGOS from the machine where you want to run the application.

3. Attach the database and name it FVBraganca. Connect to SQL Server and run this (assuming your mdf and ldf files are in c:\myapp and are called myapp.mdf and myapp_log.ldf):

   CREATE DATABASE FVBraganca
ON (FILENAME='c:\myapp\myapp.mdf'),
    (FILENAME='c:\myapp\myapp_log.ldf'),
FOR ATTACH; GO

4. Create a user called usario on the server where SQL Server is installed. Instructions are found here.

5. Create a login in SQL Server for the user created above

   CREATE LOGIN [myserver\usario] FROM WINDOWS
GO

6. Add the new login as a user of the database. If the user already exists you'll need to remove it first so you don't have a mismatch of the security identifiers. The existing user will have the security identifier tied to it from the original SQL Server where the database was last used.

   USE FVBraganca
GO
DROP USER usario
GO
CREATE USER usario FOR LOGIN usario
GO

7. Since you don't know what the user needs access to yet you might just want to grant it dbo access to the database. This is typically not a good idea but in this case it'll be easier until you know for sure what objects in the database the application uses.

   sp_addrolemember @rolename='db_owner', @membername='usario'

That should be it. As long as the database attaches cleanly I think this should get you going.

The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.

This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+. Just tested in 2012.

/*
Find Where DB Backups Went Physical Location
For last two days.  
backupset.type  
D --> FULL
I --> DIff or incrimental 
L --> Log backups
*/

DECLARE @dbname     sysname
SET @dbname = ''

SELECT  
     @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Date]
    ,msdb.dbo.backupset.user_name
    ,datediff(second, msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]    
    ,msdb.dbo.backupset.type
FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset 
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name  
WHERE 
    msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY    
    master.sys.sysdatabases.name
    ,msdb.dbo.backupset.backup_start_date 
    ,msdb.dbo.backupset.backup_finish_date
    ,msdb.dbo.backupmediafamily.physical_device_name
    ,msdb.dbo.backupset.type