Run SQL Server Management Studio. Select Database Engine as Server Type, . for Server Name and Windows Authentication for Authentication type. What you see after you connect is server. Do not create tables, views, objects (triggers, functions) etc in system databases. SQL Sever does not guarantee that tables, objects and views will be updated as you upgrade the version later on.
Creating Database
Right click on Databases click New Database. Name it Test. You will see two files.. Hit Ok.
1. Test
2. Test_log
The idea here is that you want to populate your Test database with different things like tables and views. But before we do that let’s look into two things. Let’s find out the location of Test & Test_Log and differentiate between an .mdf and .ldf file. Just go to Program Files and Click on Microsoft Sql Server then MSSQLL10_50.MSSQLSERVER. The snapshot shows the location of the database we just created.
What is the difference between .mdf and.ldf file?
MDF file, known as the primary database file, contains the schema and data. A LDF file contains the logs. A database may also use secondary database file, which normally uses a .ndf extension.
Backing Up and Restoring Database
Right click your database Test. Click on Tasks and then Backup tab.
If you want to restore, the way to do it is simply by right clicking the database you want to restore. Click Task and Restore. It will Rollback to where you last backed up. You can even rollback to specific date and time. More later.
Attaching Database
Let’s attach our database three ‘Adventureworks’ file to server. To do so, right click Databases and select ‘attach’ file. Now locate the mdf files and you are all set.