restore database without mdf or ldf file

Hi,

Is it possible to restore a database with only a .bak file??
When I try to restore the system comes up with 2 paths where probably the original .mdf and .ldf files existed, but on my system they don't exist. How can I avoid this problem? (it's not possible to get the ldf and mdf files anymore...)

Grtz
Carloscreate a new database like so...

CREATE DATABASE MyDATABASE

use sp_helpdb to get the filenames for the mdf and the ldf you just created

sp_helpdb MYDATABASE

use RESTORE FILELISTONLY to get the logical filenames of the mdf and ldf in the backup file like

RESTORE FILELISTONLY
FROM DISK = 'C:\Mybackup.bak'

Use RESTORE with MOVE like so

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Mybackup.bak'
WITH REPLACE,RECOVERY,
MOVE 'Logicalfile_Data' TO 'D:\Microsoft SQL Server\MSSQL\data\physicalfile.mdf' , MOVE 'Logicalfile_Log' TO 'D:\Microsoft SQL Server\MSSQL\data\physicalfile_log.LDF'|||Its quite possible to restore the db even if the underlying db is not preset. U need to provide the correct path where u want the mdf and ldf files to be created.|||Thnx for the quick reply!

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo