db backup simple vs. full recovery mode

When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?

When you do a backup, markers are written to the Transaction Log file, however, the backup process does not change anything about the datafiles -therefore the 'trn' file gets a new datetime and the data file does not.

The Transaction Log file does not shrink UNLESS specifically so instructed. See Books Online for DBCC 'Shrinkfile'.

|||

Hi,

You can schedule half/hourly t-log backup to keep it in shape, how ever if its growing unpexctingly refer below thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221599&SiteID=1

Hemantgiri S. Goswami

|||

What we are seeing are current timestamps on the trn file, current timestamps on the ldf, but about a six month old modified date on the mdf. I would assume that the trn file would have the most recent transactions, the ldf the intermediate, and then the mdf.

With the truncate command on the trn file, do the transactions immediately hit the mdf file or the ldf (I would think the ldf)? however when does the mdf get updated by the ldf file?

Am I completely lost--I thought that the ldf (a locked mdf file, correct?) would eventually post the edits/updates to the mdf.

|||

The ldf is the transaction log file. Data changes are moved to the mdf (data file) on a regular basis -usually within seconds.

The OS stamps the file date. SQL Server has a data file (mdf) open with a, perhaps, large, amount of empty space. The OS does not know what is happening inside the mdf file unless there are specific interactions between SQL Server and the OS regarding the file.

It seems like you are confused because the mdf file date is not changing. It most likely will not change unless one of the following actions occur: Filegrowth, Fileshrink, Detach/Attach.

DB Backup from 1 PC & Restore to diff. PC

Friends,

I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:\...." directory.

Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:\...." directory only, as it has no partitions.

This restore process gives me error :

Restore failed for Server 'HSVM\MICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ?

Please try to solve the same for me.... W8ing...

Whe u move database from one machine to another , u can either do it by Detach/attach or backup /resotre method. U mentioned that u have backup of MDF and LDF. How have u taken this backup. Did u detached the database first and then copied or u stoped the services and then copied. Please let us know the process u carried out to take backup of MDF and LDF

If u have taken backup of database using Backup Database statement, then u must restore the database from this backup using With MOVE Option . Read about Restore Database with MOVE Option in BOL

From BOL

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Madhu

|||
I have taken Backup from PC(1) without stopping any services, from SQL Management Studio 2005 directly, using UI.
Database files were on "E:\...." directory.

Copied that "xyz.bak" file into other PC(2), which is having only "C:\..." Drive.|||

Now u follow these steps

(a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak' :- This command will give u the logical file names in the backup set

(b) RESTORE DATABASE YourNewDatabaseName
FROM DISK = 'c:\xyz.bak'
WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from Step(a)' TO 'c:\yourfolder\YourNewDatabaseName.ldf'

Above statement will restore the database to new location.

Madhu

|||
Practically I just tried with your suggession.... and ofcourse it worked. Thank you for that.

Are you aware of VB.NET ? and if yes, then MicroSoft.SqlServer.Management.Smo Object Model ?

Bcoz I have taken backup file manually, but restoring from .NET implementation.

If you aware of same, then plz guide me to use Restore Class of SMO Object Model.|||

check this

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

Madhu

|||
still i am not satisfied.

while restoring DB it is giving me the error :

Exception >> Restore failed for Server 'HSVM\MICROFIT'.
Inner Exception >> Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

where HSVM is computer name & MICROFIT is instance on SQL server.