This is my situation: I want to make a small application in C# using SQL server, but I don't want it to be a server based application...
I simply want a database that I can copy and paste into my application folder and reference it there, then use the application on any other PC that wont have SQL Server installed...
You can't do that with SQL Server database. You can use Microsoft Access database or SQLite for this purpose or you can use SQL Server CE.
See: How to: Deploy a SQL Server Compact 4.0 Database with an Application
You can use embedded solution such as SQL SERVER CE. Look at this
You can use SQLite or Microsoft LocalDb which are both filebased and you can copy/paste and you're done.
For compatibility with SQL Server, Microsoft LocalDb is your winner, in the sense that if you want you can switch connection strings to a fully fledged SQL Server that has the same schema and it will work.
If you don't need that then SQLite is a lightweight and sufficient choice, there is a free Ado.net provider for it, you can use it with EntityFramework, absolutely no problem, just don't expect it to work Model-first or Code-first. You will have to go Database-first.
