Microsoft SQL Server/Database Manipulation

Creation

To create a database, its files have to be defined:

  • Master Database File (.mdf)
  • Log Database File (.ldf)
CREATE DATABASE [MyDB] ON  PRIMARY 
( NAME = N'MyDB', FILENAME = N'D:\DATASQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDB.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'D:\DATASQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf' , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Reading

Then to select it, it's either possible to set it at the beginning of the script:

USE MyDB;
SELECT * FROM MyTable;

or to call all the objects with their full path. Eg:

SELECT * FROM [MyDB].[dbo].[MyTable];

Backup

Restoration

If the database to restore doesn't exist on the destination server, it's necessary to create it empty first. In SSMS, with a right click on Databases, New database.

The process is a right click on the database to restore, and Restore the database, select the .bak.

Otherwise in SQL it gives:

RESTORE DATABASE MyDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\2016-02-16-MyDB.bak'
WITH REPLACE

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.


Microsoft_SQL_Server/Database_manipulation
 

Manage research, learning and skills at IT1me. Create an account using LinkedIn to manage and organize your IT knowledge. IT1me works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us  |  About IT1me.com |  IT Training & References |  IT Careers |  IT Hardware |  IT Software |  IT Books