How to create linked server in SQL Server using TSQL ?

What is linked server?
Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers.
It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers.
The remote servers can be SQL Server, Oracle, Mysql etc. which means those databases that support OLE DB can be used for linking servers.

First create a user in mysql which will have permission for atleast select command.

In mysql Server:-
mysql> grant select ON `koopkrachtdb `.* TO 'koopreport'@'192.168.2.100' identified by 'Rghdwf4324Fvxg';
Query OK, 0 rows affected (0.00 sec)



In SQL Server:-
Change the fields accordingly to your server settings.



/****** Object:  LinkedServer [KOOPKRATCHMYSQL]    Script Date: 11/04/2016 08:13:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'KOOPKRATCHMYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=52.71.55.125;DATABASE=koopkrachtdb; USER=koopreport; PASSWORD=Rghdwf4324Fvxg;option=3'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'KOOPKRATCHMYSQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Restore SQL Server MDF Database File Without LDF File

SQL Server is a relational database management system designed for large-scale transactions, e-commerce applications, data mining, and so on. Moreover, it is widely used on Business platforms for data analysis, data integration and processing components as it keeps all records fast, flexible and secure. The SQL server database contains three files i.e.

• Primary Database File (MDF file)
• Secondary Database File (NDF file)
• Log file (LDF file)

First, database file is Primary File amongst the three, which consists all the data and schema and also has the file format as .mdf. The second data file is Log File that maintains all the database transactions logs so the desired information can be accessed later to recover SQL server database. There must exist a single log file for each database and it is possible that many log files can be created for an individual database. The file extension for saving the transaction log is .ldf format.

Why to Restore MDF File Without Log file

If there is any corruption happens in LDF file, users unable to take the backup of Log file. According to this case, users need to restore SQL Server database without LDF file and need to recreate the Log file as well.

Solutions to Recover SQL database from MDF file

There are two methods to restore .mdf database file without .ldf file those are mentioned below:

• Using SQL Server Management Studio
• Using Transaction-SQL Script

By Utilizing SQL Server Management Studio (SSMS)

Take a look at the steps mentioned below to restore the SQL .mdf file without Log file:

• First of all, open SQL Server Management Studio.
• Then, right-click on the databases > click on Attach from the drop-down list.
• Now, click on Add button > browse the location of database file (MDF) file > choose the file and click on OK button.
• Finally, display the details in attach dialog box and select LDf file and press Remove button. After that, click on OK button for restoring the MDF file without LDF file. During the restoration of database, SQL server will create a new Log file.

• At last, check the database in desired databases folder.

By Utilizing Transact-SQL Script

Restore SQL Server MDF file without LDF file using Transact SQL script:

• Click on “New Query” from the Server Management Studio toolbar
• Execute the following query

CREATE DATABASE DatabaseName ON
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DatabaseName.mdf')
FOR ATTACH_REBUILD_LOG
GO

Conclusion

Here, we end up with the procedure to restore SQL Server database from MDF file only (without LDF file) using SQL Server Management Studio and Transact-SQL script.