October 10, 2005 at 10:42 pm
I restored a db from a .bak file. The sysfiles table for the newly restored db has two records with the following values for the name & filename fields:
TestingMedstarData D:\bbnj\Data\Medstar_Dev\TestingMedstar_Data.mdf
TestingMedstarLog D:\bbnj\Data\Medstar_Dev\TestingMedstar_Log.ldf
However the master..sysaltfiles table has three records as follows for this database:
TestingMedstarData D:\bbnj\Data\Medstar_Dev\TestingMedstar_Data.mdf
TestingMedstarLog D:\bbnj\Data\Medstar_Dev\TestingMedstar_Log.ldf
medstar_log C:\Program Files\Microsoft SQL Server\MSSQL$PIXON\Data\medstarLog.LDF
The record highlighted in red was not expected. The path doesn't even exist on my SQL Server. I'm wondering if the .bak I used for the restore isn't good. Has anyone seen this type of thing before? I'll appreciate any help you can provide.
October 11, 2005 at 1:53 am
Hi,
Do the database in readonly state or single user mode.
from
Killer
October 11, 2005 at 6:54 am
Hi,
This db is not read-only. Also I forgot to mention that the .bak was provided to me from a client. That explains why the path in the 3rd record is unfamiliar.
What I can't understand is why it's there to begin with. I found the following article that seems to address a similar issue. I tried the steps recommended but they didn't result in the removal of that 3rd record.
http://support.microsoft.com/default.aspx?scid=kb;en-us;817089
October 12, 2005 at 7:27 am
I'm guesssing the original DB had that file. If you do a RESTORE FILELISTONLY from the .bak file you can see the DB files that are part of the original DB.
In any case, I don't think it matters much as long as the DB is working correctly.
Dylan Peters
SQL Server DBA
October 12, 2005 at 12:41 pm
I would be interested in knowing how you did this restore. I think when the restore was done, the with move options were probably incorrect.
October 13, 2005 at 4:37 am
No the WITH MOVE options are correct. I tested my restore process several times and each time it resulted in this 3rd record being placed into sysaltfiles.
I've also run a RESTORE VERIFYONLY, which returned "The backup set is valid.", although I'm not sure just how reliable that is.
I ran a RESTORE FILELISTONLY as well which returned just the two files that appear in sysfiles (and which also appear in sysaltfiles) but did not return that 3rd record which was encouraging. The bottom line at this point is that I still don't know where this 3rd record came from and what the significance of it is. The database has so far not given us any problems.
October 20, 2005 at 7:43 pm
Hi,
As working on the issue i reached to the point where i got the same result as u.
When doing backup on dump device can have some issues.Which i tried to explain below.
In cases where the log has become full and no logged events can take place until log space is freed up, you can specify the No_Log option. This option truncates the inactive portion of the log without logging the truncation. This dump does not create a backup, either, so neither of these options is useful for creating backups. They should only be used in special circumstances, when you want to clear the log. If the data is important, you should immediately follow one of these dumps with a full database dump.
The third option is No_Truncate. This option was created as a solution for a special problem — the database and log are on different devices, and the data device is lost but the log device is not. In this situation, you have good backups up to a point, and the log file contains the data changed since the last backup, but you cannot get to the log file because the data portion is missing. This condition is known as an orphaned log file and it caused a lot of unhappiness in earlier versions of SQL Server. You can now use the No_Truncate option to dump the transaction log before you drop and re-create the database and restore all of your backups in order. In effect, this option tells SQL Server, “I know the database is broken, but give me this important data before I fix it.”
The other parameters in the Dump command specify whether tapes are to be ejected after dumps, whether the dump devices are to be initialized before.
Hope this help u.
Sorry if i am wrong.
from
Killer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply