rebuild a database from backupfile only?  
Author Message
Vitom





PostPosted: Thu Mar 22 02:48:55 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only?

Hi NG,

in case this is not the right news group, please advise me to the right one
for my question.

How can I rebuild a database from a plain backup?

Here is my problem:
From within a C# Program I am generating backups of my database by sending
the following sql commands:

BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete
backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10"

as well as:

BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT, NAME
= N'OekoTemp-Transaktionsprotokoll backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10

This works fine, and I am able to do a restore from within my C# program by
sending the sql command:
RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
But if the original .mdf and .log files are deleted, I can no longer do a
restore. Not by my program, and not by the SQL SERVER MANAGEMENT STUDIO.

When ever and how ever I try to restore my database from the plain backup I
receive error messages like (translated from german):

The backup set contains backups for a other database as the present
'OekoTemp'-Database.
Message 3013, Level 16, Status 1, Line 1
RESTORE DATABASE terminated due to errors.

As said : the OekoTemp-Database is not existing any more at all so how can
it be present like mentioned in the error message?

Thanks for any help and hints!
Rainer Queck

SQL Server166  
 
 
=?ISO-8859-1?Q?=22Steen_Schl=FCter_Persson_=28DK=29=22?=





PostPosted: Thu Mar 22 02:48:55 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only?
> Hi NG,
>
> in case this is not the right news group, please advise me to the right one
> for my question.
>
> How can I rebuild a database from a plain backup?
>
> Here is my problem:
> From within a C# Program I am generating backups of my database by sending
> the following sql commands:
>
> BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
> DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete
> backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10"
>
> as well as:
>
> BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT, NAME
> = N'OekoTemp-Transaktionsprotokoll backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
>
> This works fine, and I am able to do a restore from within my C# program by
> sending the sql command:
> RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
> But if the original .mdf and .log files are deleted, I can no longer do a
> restore. Not by my program, and not by the SQL SERVER MANAGEMENT STUDIO.
>
> When ever and how ever I try to restore my database from the plain backup I
> receive error messages like (translated from german):
>
> The backup set contains backups for a other database as the present
> 'OekoTemp'-Database.
> Message 3013, Level 16, Status 1, Line 1
> RESTORE DATABASE terminated due to errors.
>
> As said : the OekoTemp-Database is not existing any more at all so how can
> it be present like mentioned in the error message?
>
> Thanks for any help and hints!
> Rainer Queck
>
>
>

Hi Rainer,

If a database with the name "OekoTemp" doesn't exists, there shouldn't
be a problem in restoring it with the command you issue. You say that
the original mdf and ldf files doesn't exists, but does that mean that
the files has been deleted manually but the database still exists in SQL
server?
If the database exists and you'd like to restore the backup to this
database, you'll have to use the REPLACE option of the restore command.
I'll suggest that you read up on the RESTORE DATABASE command/syntax in
Books On Line - that will hopefully help you.

--
Regards
S**** Schlüter Persson
Database Administrator / System Administrator
 
 
Tibor





PostPosted: Thu Mar 22 02:57:53 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? In addition, study RESTORE FILELISTONLY and RESTORE HEADERONLY.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/





>> Hi NG,
>>
>> in case this is not the right news group, please advise me to the right one for my question.
>>
>> How can I rebuild a database from a plain backup?
>>
>> Here is my problem:
>> From within a C# Program I am generating backups of my database by sending the following sql
>> commands:
>>
>> BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
>> DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete backup, SKIP, NOREWIND,
>> NOUNLOAD, STATS = 10"
>>
>> as well as:
>>
>> BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT, NAME =
>> N'OekoTemp-Transaktionsprotokoll backup', SKIP,
>> NOREWIND, NOUNLOAD, STATS = 10
>>
>> This works fine, and I am able to do a restore from within my C# program by sending the sql
>> command:
>> RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1, NORECOVERY, REPLACE,
>> NOUNLOAD, STATS = 10
>> But if the original .mdf and .log files are deleted, I can no longer do a restore. Not by my
>> program, and not by the SQL SERVER MANAGEMENT STUDIO.
>>
>> When ever and how ever I try to restore my database from the plain backup I receive error
>> messages like (translated from german):
>>
>> The backup set contains backups for a other database as the present 'OekoTemp'-Database.
>> Message 3013, Level 16, Status 1, Line 1
>> RESTORE DATABASE terminated due to errors.
>>
>> As said : the OekoTemp-Database is not existing any more at all so how can it be present like
>> mentioned in the error message?
>>
>> Thanks for any help and hints!
>> Rainer Queck
>>
>>
>>
>
> Hi Rainer,
>
> If a database with the name "OekoTemp" doesn't exists, there shouldn't be a problem in restoring
> it with the command you issue. You say that the original mdf and ldf files doesn't exists, but
> does that mean that the files has been deleted manually but the database still exists in SQL
> server?
> If the database exists and you'd like to restore the backup to this database, you'll have to use
> the REPLACE option of the restore command.
> I'll suggest that you read up on the RESTORE DATABASE command/syntax in Books On Line - that will
> hopefully help you.
>
> --
> Regards
> S**** Schlüter Persson
> Database Administrator / System Administrator


 
 
Rainer





PostPosted: Thu Mar 22 03:53:58 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? Hello S****,

thanks for your fast reply!
I didn't expect a answer that fast, since I have the same question placed in
a german ng since 4 days without a answer.



> If a database with the name "OekoTemp" doesn't exists, there shouldn't be
> a problem in restoring it with the command you issue. You say that the
> original mdf and ldf files doesn't exists, but does that mean that the
> files has been deleted manually but the database still exists in SQL
> server?
Yes and no. My intention is to backup my database in intervals and then by
program execute a script which drops / creates my database.
This way I get backups for certain periods in time. Then if it is necessary
to load a "old" database I thought I could do a "backup current restore old
database".
Well this did not (yet) work. So I manually deletet my database in SQL
SERVER 2005 and then deleted the database files, hoping to be able to now do
a restore of the old database. This did not work (Yet) and I received the
errormessages mentioned before.

> If the database exists and you'd like to restore the backup to this
> database, you'll have to use the REPLACE option of the restore command.
> I'll suggest that you read up on the RESTORE DATABASE command/syntax in
> Books On Line - that will hopefully help you.
Thanks for the hints. But as you can see by my original post, I am using the
REPLACE option on my restore efforts, but may be there is still a wrong
selection of options in my sql?

Regards
Rainer Queck


 
 
Rainer





PostPosted: Thu Mar 22 03:58:19 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? Hi Tibor,

thanks for the additional hint.



> In addition, study RESTORE FILELISTONLY and RESTORE HEADERONLY.

How would the complete SQL string look like for my case?

Since the project I am working on is a weekend project, I won't be able to
continue with my tries before saturday.
Also the current database is not yet important since the project is under
developement and I am currently working with test data and therefore a
dataloss is not yet a pain ;-)
Still it would be great, if I could get this problem solved soon.

Regards
Rainer


 
 
petery





PostPosted: Thu Mar 22 05:50:06 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? Hello Rainer,

I understand that you cannot restore a database when the existing mdf/ldf
is remmoved and the database does not exist on the server. If I'm off-base,
please let me know.

You may first run****command:

restore headeronly FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak'


Please see the Databasename in the result, and this shall be the database
name you use in restore command. Please double check the name is correct.
From the error message you encounter, it seems the database name is not
correct in your restore command


In my sample, it is dbacc. If run the command you mentioned, you may see
the following error message:

RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10


Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Line 1
File 'dbacc' cannot be restored to
'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf'. Use WITH MOVE to identify a
valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Line 1
File 'dbacc_log' cannot be restored to
'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_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.


If so, you need to use "with move" statmend in restore command. For example:


RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10,
move 'dbacc' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.mdf',
move 'dbacc_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc_log.ldf'

If the issue still occurs, it might be the backup file is corrupted. You
may want to try a new backup/restore to test. Also, you may want to test
directly in QA or mangement studio(2005) to see if there is any difference

More related informaiton:


Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE
http://www.hide-link.com/

314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.hide-link.com/

Please let's know if you have any further questions or concerns. Thanks.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://www.hide-link.com/ #notif
ications
< http://www.hide-link.com/ ;.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
< http://www.hide-link.com/ ;.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
Rainer





PostPosted: Thu Mar 22 06:29:32 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? Hello Peter,

thank you very much for your detailed answer.
I will follow your suggestions as soon as I continue work on my project
(saturday) and let you know if I was successful or need more help.

Considering the answers I have up to now, there is hope ;-)

Regards
Rainer



> Hello Rainer,
>
> I understand that you cannot restore a database when the existing mdf/ldf
> is remmoved and the database does not exist on the server. If I'm
> off-base,
> please let me know.
>
> You may first run****command:
>
> restore headeronly FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak'
>
>
> Please see the Databasename in the result, and this shall be the database
> name you use in restore command. Please double check the name is correct.
> From the error message you encounter, it seems the database name is not
> correct in your restore command
>
>
> In my sample, it is dbacc. If run the command you mentioned, you may see
> the following error message:
>
> RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
>
>
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf"
> failed with the operating system error 3(The system cannot find the path
> specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'dbacc' cannot be restored to
> 'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file
> "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf"
> failed with the operating system error 3(The system cannot find the path
> specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'dbacc_log' cannot be restored to
> 'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_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.
>
>
> If so, you need to use "with move" statmend in restore command. For
> example:
>
>
> RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10,
> move 'dbacc' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.mdf',
> move 'dbacc_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc_log.ldf'
>
> If the issue still occurs, it might be the backup file is corrupted. You
> may want to try a new backup/restore to test. Also, you may want to test
> directly in QA or mangement studio(2005) to see if there is any difference
>
> More related informaiton:
>
>
> Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE
> http://www.hide-link.com/
>
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL
> Server
> http://www.hide-link.com/
>
> Please let's know if you have any further questions or concerns. Thanks.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://www.hide-link.com/ #notif
> ications
> < http://www.hide-link.com/ ;.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> < http://www.hide-link.com/ ;.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>


 
 
petery





PostPosted: Thu Mar 22 20:53:41 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? Hello Rainer,

Please feel free to post back if you have any update. :-)

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


 
 
Rainer





PostPosted: Sat Mar 24 03:44:23 CDT 2007 Top

SQL Server >> rebuild a database from backupfile only? Hello Peter,

thank you very much for your help!
Following your advise I can now (re)generate a database completely from just
a backupfile.

Doing these 4 step give me exactly what I need:
DROP DATABASE OekoTemp

GO

restore headeronly FROM DISK = N'<backupfile>'

GO

RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10

GO

RESTORE LOG [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 2, NOUNLOAD,
STATS = 10


Regards
Rainer Queck