restore DB with SQLSever DMO

Hi,
SQL 2000 had an example for using DMO programing with VB to restore and
backup a database, which named BackRestEvents.
I want to extend this project to build a tool, which can restore from a
backup file to a new database (new name). But I don't know, how I can set
the database filenames to another name (such as test1_data.mdf and
test1_log..ldf), because the database test (with test_data.mdf and
test_log..ldf) still exists on the system.
How can I do?
The backup file serves only as a template to create a new database. Is there
another way?
I use MSDE 2000, I don't have any tool such as Enterprise manager or SQL
Analyzer.
Thanks for help
Martin
hi Martin,
Martin wrote:
> Hi,
> SQL 2000 had an example for using DMO programing with VB to restore
> and backup a database, which named BackRestEvents.
> I want to extend this project to build a tool, which can restore from
> a backup file to a new database (new name). But I don't know, how I
> can set the database filenames to another name (such as
> test1_data.mdf and test1_log..ldf), because the database test (with
> test_data.mdf and test_log..ldf) still exists on the system.
> How can I do?
assuming your relative T-SQL syntax is
RESTORE DATABASE [Pubs2] FROM DISK = N'C:\Pubs.bak' WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'pubs' TO N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\pubs2.mdf',
MOVE N'pubs_log' TO N'C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs_log2.ldf'
where you restore to Pubs2 database and move file to other location, you
have to specify the
oRestore.RelocateFiles = "[pubs],[C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs2.mdf],[pubs_log],[C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs_log2.ldf]"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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.Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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" <esquel@.sommarskog.sewrote in message
news:Xns9945EC53F9A4BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>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, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||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" <esquel@.sommarskog.sewrote in message
news:Xns9945EC53F9A4BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>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, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||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" <esquel@.sommarskog.sewrote in message
news:Xns9945EC53F9A4BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>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, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

restore *.bak file to DB

hi there......
i got stuck when i tried to restore backup file (*.bak) into my existing db.

for u'r info i just make a backup from my DBase1 named BackupDB1.bak then,
i would like to restore this BackupDB1.bak into existing DBase2 db.
then i got an error...

please give me some ideas or solution...
Tq..hi

you have to use the REPLACE option with the RESTORE command.

Look for help in the Book on line: RESTORE, RESTORE (described)|||What is the exact error you are getting|||True you should specify the error message to assess the issue.

Also are you trying to restore SQL database to a dbase2 database?