469,904 Members | 2,531 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

Restore Database Fails

I'm trying to use ADO to restore a SQL 7 database using a backup file. The
database already exists on the target computer, and is named the same as on
the source computer, and the MDF and LDF files are named the same as well.

I am running the below in a stored procedure:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE 'DB1' TO 'c:\mssql7\data\DB1.mdf',
MOVE 'DB1_log' TO 'c:\mssql7\data\DB1_log.ldf'

And I get an error message "File 'DB1' is not a database file for database
'DB1' ".

I also tried it without the LDF file in the expression. Same results.

Thanks.

Jun 4 '07 #1
5 4275
Neil (no****@nospam.net) writes:
I'm trying to use ADO to restore a SQL 7 database using a backup file.
The database already exists on the target computer, and is named the
same as on the source computer, and the MDF and LDF files are named the
same as well.

I am running the below in a stored procedure:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE 'DB1' TO 'c:\mssql7\data\DB1.mdf',
MOVE 'DB1_log' TO 'c:\mssql7\data\DB1_log.ldf'

And I get an error message "File 'DB1' is not a database file for database
'DB1' ".
Add ", REPLACE" to your command.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 4 '07 #2
OK, replaced the original command with:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE DB1 TO 'c:\mssql7\data\DB1.mdf',
MOVE DB1_log' TO 'c:\mssql7\data\DB1_log.ldf',
REPLACE

Got the same error message.

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Neil (no****@nospam.net) writes:
>I'm trying to use ADO to restore a SQL 7 database using a backup file.
The database already exists on the target computer, and is named the
same as on the source computer, and the MDF and LDF files are named the
same as well.

I am running the below in a stored procedure:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE 'DB1' TO 'c:\mssql7\data\DB1.mdf',
MOVE 'DB1_log' TO 'c:\mssql7\data\DB1_log.ldf'

And I get an error message "File 'DB1' is not a database file for
database
'DB1' ".

Add ", REPLACE" to your command.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 4 '07 #3
As a followup to my previous message, I also tried the modified command
directly from Query Analyzer, and got the same message. I then tried to
restore the backup file from Enterprise Manager, and it worked fine. But
couldn't get the SQL to work.

However, I still need to get it to work, as I have an associate who needs to
restore the same backup set, but he doesn't have QA or EM. He's running the
SQL in a stored procedure using ADO.

Thanks,

Neil
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Neil (no****@nospam.net) writes:
>I'm trying to use ADO to restore a SQL 7 database using a backup file.
The database already exists on the target computer, and is named the
same as on the source computer, and the MDF and LDF files are named the
same as well.

I am running the below in a stored procedure:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE 'DB1' TO 'c:\mssql7\data\DB1.mdf',
MOVE 'DB1_log' TO 'c:\mssql7\data\DB1_log.ldf'

And I get an error message "File 'DB1' is not a database file for
database
'DB1' ".

Add ", REPLACE" to your command.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 5 '07 #4
OK, I got it to work. Seems I was using MOVE to move the MDF and LDF to
those locations; but the MDF and LDF for the database that was being
overwritten were already at those locations. So I removed the MOVE commands,
and it worked fine. The final version was:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH RESTORE

Thanks!

Neil
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Neil (no****@nospam.net) writes:
>I'm trying to use ADO to restore a SQL 7 database using a backup file.
The database already exists on the target computer, and is named the
same as on the source computer, and the MDF and LDF files are named the
same as well.

I am running the below in a stored procedure:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE 'DB1' TO 'c:\mssql7\data\DB1.mdf',
MOVE 'DB1_log' TO 'c:\mssql7\data\DB1_log.ldf'

And I get an error message "File 'DB1' is not a database file for
database
'DB1' ".

Add ", REPLACE" to your command.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 5 '07 #5
Neil (no****@nospam.net) writes:
OK, replaced the original command with:

RESTORE DATABASE DB1
FROM DISK = 'c:\mssql7\backup\DB1.bak'
WITH MOVE DB1 TO 'c:\mssql7\data\DB1.mdf',
MOVE DB1_log' TO 'c:\mssql7\data\DB1_log.ldf',
REPLACE

Got the same error message.

The the problem is that the logical names of the files are not DB1 and
DB1_log. You can retrieve these names with RESTORE FILELISTONLY.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 5 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by FoxRunner | last post: by
1 post views Thread by ad | last post: by
1 post views Thread by Utahduck | last post: by
3 posts views Thread by Bill E. | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.