How do I apply a LDF log file to a restored database?

I only have the ldf file and the bak file.

My bak file was current to 3/2/2007

My ldf file was current to 3/25/2007

I do not have the mdf file.

How to I apply the transaction from the ldf file to my resotred database?

Hello David,

According to my knowledge it not is possible to apply a .ldf file but if you are using some external tool like Logexplorer then you can atleast view the transactions in that .ldf file.

|||If this is the case it would seem to negate the purpose of having a log file?|||

The purposes of the log file are:

To facilitate rollback/rollforward when a database is brought online after an unusual shutdown|||

you said you have a .bak file and a log file. My gut feeling is that , this bak file is a full backup of your database. In that case you can restore the database from this back file as on the bak file is created.

YOu have mentioned that you have a ldf file. is it physical file or is it a bakup file. confirm this

run the following command to know what all are the contents inside the bak file

RESTORE FILELISTONLY FROM DISK = 'D:\your bak filename.bak'

if it is a full backup use restore command to restore it. Read about Restore command in BOL

Madhu

|||

No David ,

It would not negate the use of a log file. if you had a .mdf file then you could attach the log file with the .mdf file. .trn files are the log back up file not the .ldf files.

How do I apply a LDF log file to a restored database?

I only have the ldf file and the bak file.

My bak file was current to 3/2/2007

My ldf file was current to 3/25/2007

I do not have the mdf file.

How to I apply the transaction from the ldf file to my resotred database?

Hello David,

According to my knowledge it not is possible to apply a .ldf file but if you are using some external tool like Logexplorer then you can atleast view the transactions in that .ldf file.

|||If this is the case it would seem to negate the purpose of having a log file?|||

The purposes of the log file are:

To facilitate rollback/rollforward when a database is brought online after an unusual shutdown|||

you said you have a .bak file and a log file. My gut feeling is that , this bak file is a full backup of your database. In that case you can restore the database from this back file as on the bak file is created.

YOu have mentioned that you have a ldf file. is it physical file or is it a bakup file. confirm this

run the following command to know what all are the contents inside the bak file

RESTORE FILELISTONLY FROM DISK = 'D:\your bak filename.bak'

if it is a full backup use restore command to restore it. Read about Restore command in BOL

Madhu

|||

No David ,

It would not negate the use of a log file. if you had a .mdf file then you could attach the log file with the .mdf file. .trn files are the log back up file not the .ldf files.

How do i add my database to "data sources"?

In Lesson 9 as you can see here (at 16:50), a connection is created against his .mdf database, and the dataset is shown in the "Data Source" window/panel.
He can then simply drag the data source onto the form, and controls are created and bound automaticly - or drag them as a datagridview to the form.

That is what i want to do, but i have some problems...

I have set up a connection to my SQL Express database, and a dataset is created without problems. The problem is that this dataset does not show up in "Data Source". Actually, i have never seen anything in that panel before, in any of the data sources i have added!

What am i doing wrong? In the menu i click "Data" --> Add new data source --> finish the "wizard". Then i click "Data" --> Show Data sources --> It's EMPTY!

Does this also happen if you just connect to a regular database, not through .mdf file? When i tried it with regular database it works, but although "Data Source" panel is not empty, it contains only DataSources names which are empty inside and not possible to drag onto designer. Is it what you mean by "empty"? If so, then you can click on "Server Explorer" and drag and drop your dataset from there. Then your data sources are going to get populated also.|||

I am trying to connect to a regular database, stored on a different computer running MSSQL Express. Connection and everything is okay.

By "empty", i mean completely empty - theres nothing in the panel - no items at all.

See this screenshot:

http://dritbra.com/temp/datasource.png

As you can see on the screenshot, the items appears as soon as something else than the form designer is active...

Btw, when viewing the server explorer i can't drag the items to the form... The mouse has this "not allowed" icon.

|||So theres really no one who can even confirm that the data set is supposed to appear in the data source panel, available for "drag and drop"?|||

Ok, this appears to be a known issue - probably occuring because i have had a beta installed (allthough i have completely reinstalled final version two times on this computer).

SOLUTION:

OPEN:
C:\Documents and Settings\<UserName>\Application Data\Microsoft\VisualStudio\8.0\DataBindingSetting\WinFormControls.xml

ADD THIS:

<DataType Name="System.Collections.IList">
<BindableControlInfo Name="DataGridView" Type="System.Windows.Forms.DataGridView" AssemblyName="System.Windows.Forms, Version=2.6.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<BindableControlInfo Name="[None]" Type="Microsoft.VSDesigner.Data.BindManager.NoneControlPlaceHolder" />
</DataType>

How do i add my database to "data sources"?

In Lesson 9 as you can see here (at 16:50), a connection is created against his .mdf database, and the dataset is shown in the "Data Source" window/panel.
He can then simply drag the data source onto the form, and controls are created and bound automaticly - or drag them as a datagridview to the form.

That is what i want to do, but i have some problems...

I have set up a connection to my SQL Express database, and a dataset is created without problems. The problem is that this dataset does not show up in "Data Source". Actually, i have never seen anything in that panel before, in any of the data sources i have added!

What am i doing wrong? In the menu i click "Data" --> Add new data source --> finish the "wizard". Then i click "Data" --> Show Data sources --> It's EMPTY!

Does this also happen if you just connect to a regular database, not through .mdf file? When i tried it with regular database it works, but although "Data Source" panel is not empty, it contains only DataSources names which are empty inside and not possible to drag onto designer. Is it what you mean by "empty"? If so, then you can click on "Server Explorer" and drag and drop your dataset from there. Then your data sources are going to get populated also.|||

I am trying to connect to a regular database, stored on a different computer running MSSQL Express. Connection and everything is okay.

By "empty", i mean completely empty - theres nothing in the panel - no items at all.

See this screenshot:

http://dritbra.com/temp/datasource.png

As you can see on the screenshot, the items appears as soon as something else than the form designer is active...

Btw, when viewing the server explorer i can't drag the items to the form... The mouse has this "not allowed" icon.

|||So theres really no one who can even confirm that the data set is supposed to appear in the data source panel, available for "drag and drop"?|||

Ok, this appears to be a known issue - probably occuring because i have had a beta installed (allthough i have completely reinstalled final version two times on this computer).

SOLUTION:

OPEN:
C:\Documents and Settings\<UserName>\Application Data\Microsoft\VisualStudio\8.0\DataBindingSetting\WinFormControls.xml

ADD THIS:

<DataType Name="System.Collections.IList">
<BindableControlInfo Name="DataGridView" Type="System.Windows.Forms.DataGridView" AssemblyName="System.Windows.Forms, Version=2.6.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<BindableControlInfo Name="[None]" Type="Microsoft.VSDesigner.Data.BindManager.NoneControlPlaceHolder" />
</DataType>

How do attach database while installing msde on client pc ?

I have added MSDE merge modules in VS Installer as well as MDF and LDF file,
now what should I do, so that setup can automatically Attach the database
after installing MSDE client's Machine.
Best Regards,
Luqman
Hi Luqman,
see this thread here:
http://groups.google.de/group/micros...8f627e88171d02
HTH, Jens Suessmeyer.

How delete a log file

I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
Thanks
Try DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:

> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','empty file')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
sql

How delete a log file

I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:

> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_dele
te','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
>

How delete a log file

I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
--
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
> > I have unfortunately create a second log file for a database.
> > So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> > I would delete DataLog2.ldf, but it's now the active log file, and the
> > operation can't terminate.
> >
> > Thanks

How can we avoid somebody to access the MDF data by doing User Instance connection?

I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).

For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.

But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.

How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?

Thanks!

Hi,

AFAIK their is no way to restrict access to MDF/LDF files physically... refer below thread which has discussion the same issue.

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

Hemantgiri S. Goswami

|||

Alright, but what about the User Instance? Is there a way to prevent somebody of getting connected on a server using our MDF file and the User Instance option into his connection string?

|||No, you wil lhave to do this via NTFS permissions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How can import mdf file to mssql 2005

Dear All,

I have a mdf file and want to import the file to mssql 2005, But i can't find the function when i right click the database name in SQL Server Management Studio. What should i do?

Regards,

Ricky

You can choose to attach the mdf file as a new database. Right-click on the 'Databases' option on SSMS and click attach or use T-SQL (sp_attach_db).|||

hi

Gargi is very right,

I would like to Add,

the thing is you need to attached the .mdf file as well as its log file with that mdf file.

(this is the process of attaching, there is no direct process of importing a mdf file)

Regrds,

Thanks.

Gurpreet S. Gill

|||I just want to add once you attach a 2000 database in 2005 you can't go back just make sure you have a copy.

How can import mdf file to mssql 2005

Dear All,

I have a mdf file and want to import the file to mssql 2005, But i can't find the function when i right click the database name in SQL Server Management Studio. What should i do?

Regards,

Ricky

You can choose to attach the mdf file as a new database. Right-click on the 'Databases' option on SSMS and click attach or use T-SQL (sp_attach_db).|||

hi

Gargi is very right,

I would like to Add,

the thing is you need to attached the .mdf file as well as its log file with that mdf file.

(this is the process of attaching, there is no direct process of importing a mdf file)

Regrds,

Thanks.

Gurpreet S. Gill

|||I just want to add once you attach a 2000 database in 2005 you can't go back just make sure you have a copy.

How can I use mdf files from sql server 2005 exp. edition.

Hello,

I am a candidate for web programming..I would like to desing a website and I have a good host which is supporting .net 2.0 and sql server 2005 service. So I would like to ask you

1) how can I use sql server express edition's mdf database file there. or Can I store and use this "mdf"s in ftp folder? What should I do. ..

2) I have only one database "right" there and should I manage everything in one database. For example asp.net configuration manager created ASPNETDB.mdf and ldf...so if Can you inform me about this issue?

Maybe my questions is not advance, they are very basic but I want to learn

Thank you very much for your helps.

Hi,

From your description, it seems that you want to deploy the database in your SQLExpress to the SQLServer2005 database on your host server, right?

If so, I suggest that you should use some tools to achieve that instead of attaching MDF manually. First, you may use aspnet_regsql.exe tool convert the database in SQLExpress to your local SQLServer series database. In you case it's SQLServer2005. After that, use SQLServer export and import wizard to upload the database (including table and schema) to the SQLServer2005 instance on your server.

For aspnet_regsql.exe, see:

http://msdn2.microsoft.com/en-us/library/ms229862.aspx

Thanks.

How can i treate Huge DB size in sql server 2000

Hi all,

I have DB in operation its MDF size reached 8.38 GB and the system that work on the queries of insert timeout and the operation failed and many problems happens ...

actually the reason of the huge size of the DB is just one table that contain image field which we store word files in it in each row in the table .........

so how can i solve this problem without affecting the structure of the DB ..... coz we don't wanna to make code changes in the application that use this DB

thanks

if it 8.38 GB.. this can not be considered as a "Huge" DB from SQL Server point of view ... SQL Server handles much much larger databases... this is basically... architecture flaw... when u store word file in database it is bound to have some performance issue... its again if you have sufficient Hardware resources u can do that... you could have store the path of word file instead of file itself... anyhow, if u don't want to change the architecture... you can move the table which stores word file to a different Filegroup/disk ... or archive the unwanted data… add more hardware… etc.. etc…

Madhu

|||

I hear about the posibility of partioning the huge size table into physical partitions with out affect the logical structure so the performance of accessing the table will be faster ......

so if this process can be done on sql server 2000 on already exist DB how can it be done ...

i need an article about doing that .........

|||

If you are refering to partition of tables its supported in SQL 2005. This article might help you.

http://msdn2.microsoft.com/en-us/library/ms190787.aspx

Thanks,

|||

What about making partionning in sql server 2000?

|||

there are many article available... google it...

i think this will give some hints

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3861.mspx?mfr=true

regards

Madhu

|||You can create filegroups in SQL Server 2000. 8+GB is not a huge database as I have been handling databases more than 100GB and SQL Server is still functioning well. If your application is the one timing out, you have to revisit your application design and your codes as well as your database structure.|||When was the last time the database has been checked for consistency and redinexed for stats update?|||

Let me honest with that never happen ......

so .... is there any suggestions?!

|||

use DBCC DBREINDEX to reindex all the tables and use sp_updatestats to update all the table statistics and see the performance

also use sp_spaceused @.updateusage = 'TRUE' to get the space used by the data...

Madhu

How can i transfer a database from Access to Sql Server 2000?

Hy ,

How can i transfer a database from Access to Sql Server 2000?

Exactly : i want to transfer a .mdb into .mdf, and i want to keep the structure of .mdb, a structure which have tables, queries, macros, modules... When i start the transfer with Data Transformation Services ( DTS- Import Data ) , i can see that DTS shows me only tables and queries. At the end of transfer, in my .mdf database, on server, i have only tables, without queries, macros, modules.

My question is how can i transfer the queries and macros ( exactly how are in .mdb ) to .mdf database?

Thanks in advance for any advice..

Emanuel.

The way you did it, SQL Server will materialize the queries to tables. The problem is that Access does not use the SQL that SQL Server does, so this cannot be easily converted. Additionally SQL Server does not have a clue about Macros, so they can′t be imported either. You can use the upsizing wizard in Access if you want to keep your existing frontend in Access (because it will link automatically the new tables stored in SQL Server).

HTH, Jens K. Suessmeyer.

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

My very own first posting here. :)

I have tried using the DTS for the SQL Server 2000 with Data Source MS Access to destination SQL Server.Correct me if am wrong but I think you can get the Queries also but I dont know if the macro can be import as well. My best guess is to create another functions w/c works similar as your Macros.

Best Regards,
Vince

|||

Hi,

see more on this on this paper here: http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

SQL Server surely won’t transform macros, forms, and modules because it doesn’t have the equivalents. So the question for you is: Do you want to rewrite your application on another development platform – C#, VB, C++, you name it, or do you want to continue developing in Access. If you choose the former, you’ve already seen what you can migrate. If you choose the latter, you can migrate your tables to SQL Server, get rid of the native Access Tables, and then link the SQL Server tables from Access. If you give the linked tables the same names they had when they were native in Access, you won’t have to modify your queries at all.

Zlatko