SQL Server - How can I add .MDF and LDF file to sql server

Asked By mostafa hamdy on 20-Sep-06 04:28 AM

Hi all

I have two file one with extention .MDF and the other one is .LDF I want to see the database tables of these files in the enterprise manager databases , in which folder on the server I have to set these files to see them in the enterprise manager ? plz If any body can help me send me

best regards

Mostafa

Attach .mdf and ldf to sql server - K Pravin Kumar Reddy replied to mostafa hamdy on 20-Sep-06 05:10 AM

hello

1.Use Enterprise Manager on the SQL Server machine, and right-click on the ‘Databases’

folder name and select “All Tasks->Attach Database…”

2) In the dialogue box, click the “…” browse button and find the empty ‘MonitorIT.mdf’ file

that you just copied, and select it.

3) In the ‘Current File(s) Location’, there should be Green checkmarks for both the MDF and

LDF files; edit the file name if necessary so it is the name of the empty file just copied.

4) In the ‘Attach as’ field, leave it as MonitorIT60 or change it to another unique name for

the database.

5) Click OK. The database should then be attached and show in the ‘Databases’ tree.

------------------------------------------------------------

Use the “MonitorIT.mdf” and “MonitorIT_log.ldf” Files from MonitorIT to Attach To SQL

as an Empty Database

1) Stop the MonitorIT Server service.

2) Copy the empty MonitorIT SQL database files called ‘MonitorIT.mdf’ and

‘MonitorIT_log.ldf’ from the “…\MonitorIT\Database” folder to the SQL data folder

(“\Program Files\Microsoft SQL Server\MSSQL\Data”) where the other SQL database

files are kept. You may need to make a backup copy and rename it first before copying

to the SQL data folder if there is already a ‘MonitorIT.mdf’ there. Instead of renaming it,

you could make a new folder under “…\Data” and copy the ‘MonitorIT.mdf’ there.

3) If the SQL Server service (MSSQLSERVER) is running under a user account that does

not have security rights for the ‘MonitorIT.mdf’ and ‘MonitorIT_log.ldf’, then right-click on

each of these files, select ‘Properties’ then the ‘Security’ tab and then make sure the user

account that SQL Server service is running under has rights to these files. Otherwise

when you ‘Attach Database’, the database will attach as ‘Read Only’.

4) Use Enterprise Manager on the SQL Server machine, and right-click on the ‘Databases’

folder name and select “All Tasks->Attach Database…”

5) In the dialogue box, click the “…” browse button and find the empty ‘MonitorIT.mdf’ file

that you just copied, and select it.

6) In the ‘Current File(s) Location’, there should be Green checkmarks for both the MDF and

LDF files; edit the file name if necessary so it is the name of the empty file just copied.

7) In the ‘Attach as’ field, leave it as MonitorIT60 or change it to another unique name for

the database.

8) Click OK. The database should then be attached and show in the ‘Databases’ tree.

9) Right click on the new database name in the tree, and select ‘Properties’, then on the

‘Properties’ dialogue box, click the ‘Options’ tab, and change the ‘Recovery Model’ from

‘Full’ to ‘Simple’.

 

Now Import the Data from the Original MonitorIT SQL Database

10) Right click on the new database name in the tree, and select ‘All Tasks->Import Data’.

This opens the ‘DTS Import/Export Wizard’; click ‘Next’.

11) Data Source is the ‘Microsoft OLE DB Provider for SQL Server’; select the original

MonitorIT Database from the ‘Database’ drop-down; click ‘Next’.

12) Destination is also ‘Microsoft OLE DB Provider for SQL Server’; select the new, empty

MonitorIT Database that you just attached from the ‘Database’ drop-down; click ‘Next’.

13) Select ‘Copy table(s) and view(s) from the source database; click ‘Next’.

14) In the ‘Select Source Tables and Views’, click ‘Select all’ to select all the tables from the

original MonitorIT database.

15) Widen the ‘Source’ column so you can see all the table names, then UNSELECT the

following tables so that the data from these tables is NOT imported. These are the tables

with monitored data that would normally be auto-purged:

-CustomCheckLogData,

-EventLogData,

-FileCheckLogData,

-NTServicesLog,

-ProcessCheckLogData,

-ServicesLog,

-SNMPTrapLog,

-SNMPTrapVbLog,

-SyslogCheckLog,

-Value.

16) Click ‘Next’; make sure just ‘Run Immediately’ is checked, then click ‘Next’, then ‘Finish’.

It could take 5-10 minutes to copy the selected tables of your configuration data. When

done, it should display a message box that states ’32 Tables Successfully Copied’. Click

‘Done.

 

Now Change the DSN to Point to this New Database So MonitorIT Can Use It

1) Use the ODBC Data Sources utility in the Administrative Tools, and go to the ‘System

DSN’ tab.

2) Select the DSN named ‘BreakoutRPM’ , and the click ‘Configure’.

3) Click ‘Next’ two times and go to the 3rd Wizard screen , and select the new MonitorIT

database from the drop-down called ‘Change the default database to:’

4) Then click ‘Next’ to the end, and the ‘Test Data Source’ Connection. Click OK and Save.

5) Restart the MonitorIT Server service to being using the new database. All your

configuration data (Servers/Devices, Watches, Security, Groups, etc) should be present.

 


How to add mdf file to database? - Ravi shankar replied to K Pravin Kumar Reddy on 11-Oct-06 01:39 AM

I have only mdf file in Appdata folder in my asp.net project. Is it possible to add it into sqlserver database?

Pl tell me the procedure to do that.

Thanks in advance.

Ravishankar

attach mdf file sql server - K Pravin Kumar Reddy replied to Ravi shankar on 11-Oct-06 01:55 AM

hello

check this

You can use the attach database function of SQL Server to attach the MDF file and create the database.

This can be done one of two ways:

1. Using Enterprise Manager

  1. Expand the Databases tree
  2. Right click and select All Tasks
  3. Select Attach Database
  4. Find and select the MDF file
  5. The GUI will display the name of the MDF and LDF files and there should be an X on the LDF file name

  6. Change the name of the database and owner as needed and select OK
  7. An error message will appear that says "Filename specified for log is incorrect. Do you want to continue", click on Yes

  8. The database should now be attached to your server. If the database came from another server you will probably need to take care of user and login mismatches.

2. Using T-SQL

EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'


reference

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1118069,00.html