This article mainly for SQL server learners, who is going to become a DBA, in this article I am going to explain the backup and restore using T-SQL. =======================make sure you got the following information, see the example in the bottom DB size – Sp_helpdb ‘Dbname’ – 80 GB Source DB latest backup date and availability – Check the backup history and check backup is available in drive – Yes Destination DB latest backup date and availability – Optional (latest backup available ) Can we overwrite destination with or without backup – Check with the requester – Yes, overwrite take a backup before ======================= Why I choose the T-SQL…
-
-
Clustered index vs Non clustered index
Clustered index vs Non clustered index structure Clustered and Non clustered indexes are stored in a B – tree structure . Clustered Index Clustered index enforce the logical order. (Misconception: Clustered index does not enforce the physical order) A table has only one clustered index because, the original table stored at the leaf level of the clustered index (Data pages). When you create a primary key by default clustered index will be created internally. (If the table has clustered index already then the non clustered index will be created internally) If the table does not have clustered index it’s called “Heap” Non Clustered Index Non clustered indexes…
-
INDEX DEFRAGMENTATION SCRIPT
INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005 I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script. I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC). http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/ For VLDB – Please use Ola Hallengren’s script https://ola.hallengren.com/, Which has many parameters. Script changes: · I used while loop instead of a cursor. · I used to rebuild the index if the page level locking is disabled. Msg 2552, Level 16, State 2, Line 1 The index “index name” (partition 1) on table “table name” cannot be reorganized because page level locking is disabled Index…
-
SQL server basics for beginners
In this article, I am going to discuss about the basics of SQL server which is helpful for newbies. SQL–Structured Query Language (pronounced as SEQUEL) is a Database, computer language designed for managing data in relational database management system (RDBMS). Most of the people have to know SQL server query has designed for set based (declarative) . The SQL commands are DQL (Select) DML (Insert, Update, Delete) DDL (Create, Alter, Drop, Truncate) DCL (Grant, Revoke, Deny) TCL (Commit, Rollback) Database A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one…
-
sql server internal architecture
Database Physical Structure & Internals: SQL server internals Allocation Map is important for DBAs. It will help to understand the database internals. Database: A Database is a collection of data that is stored (or) organized. We can manage and access data easily. When we create a new database, by default two files will be created physically. We can create more than one log files and data files (NDF). Data file (MDF) Log file (LDF) Data File: All the data will be stored in a data file. Internally the data’s are stored in data pages. A database file has unique FileID and PageID. Log files do not have pages. They have…