Take a SQL Server Database Offline

Take a Database Offline using T-SQL and wait for existing connections to close

ALTER DATABASE AdventureWorks SET OFFLINE

The command waits for existing connections to close and also does not accept any new connections. Use at discretion!

Take a Database Offline Immediately using T-SQL

ALTER DATABASE AdventureWorks
SET OFFLINE WITH ROLLBACK IMMEDIATE

Bring back the Database Online

ALTER DATABASE AdventureWorks

Paging Records In SQL Server 2005 Database

SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications.  ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.
If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 1 AND Row <= 10

The second page of 10 records would then be as follows:

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

How to Restoring SQL Server 2005 Suspect Database

If u have a SQL Server 2005 database in suspect mode,we can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode which is introduced in SQL Server 2005.Please follow the following steps to change the database status from suspect to emergency than to it's normal state:

EXEC sp_resetstatus 'DBname'


ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

MySQL Commands

MySQL Commands




This is a list of handy MySQL commands


To login (from unix shell) use -h only if needed.


# [mysql dir]/bin/mysql -h hostname -u root -p


Create a database on the sql server.


mysql> create database [databasename];


List all databases on the sql server.


mysql> show databases;


Switch to a database.


mysql> use [db name];


To see all the tables in the db.


mysql> show tables;


To see database's field formats.


mysql> describe [table name];


To delete a db.


mysql> drop database [database name];


To delete a table.


mysql> drop table [table name];


Show all data in a table.


mysql> SELECT * FROM [table name];


Returns the columns and column information pertaining to the designated table.


mysql> show columns from [table name];


Show certain selected rows with the value "whatever".


mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";


Show all records containing the name "Bob" AND the phone number '3444444'.


mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';


Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.


mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;


Show all records starting with the letters 'bob' AND the phone number '3444444'.


mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';


Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.


mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;


Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.


mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";


Show unique records.


mysql> SELECT DISTINCT [column name] FROM [table name];


Show selected records sorted in an ascending (asc) or descending (desc).


mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;


Return number of rows.


mysql> SELECT COUNT(*) FROM [table name];


Sum column.


mysql> SELECT SUM(*) FROM [table name];


Join tables on common columns.


mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;


Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.


# mysql -u root -p mysql> use mysql;


mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password')); mysql> flush privileges;


Change a users password from unix shell.


# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'


Change a users password from MySQL prompt. Login as root. Set the password. Update privs.


# mysql -u root -p


mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); mysql> flush privileges;


Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.


# /etc/init.d/mysql stop 


# mysqld_safe --skip-grant-tables & 


# mysql -u root 
mysql> use mysql;


mysql> update user set password=PASSWORD("newrootpassword") where User='root'; mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop 


# /etc/init.d/mysql start 


Set a root password if there is on root password.




# mysqladmin -u root password newpassword


Update a root password.


# mysqladmin -u root -p oldpassword newpassword


Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.


# mysql -u root -p mysql> use mysql;


mysql> grant usage on *.* to bob@localhost identified by 'passwd'; mysql> flush privileges;


Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.


# mysql -u root -p mysql> use mysql; mysql> INSERT INTO db


(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;


or


mysql> grant all privileges on databasename.* to username@localhost; mysql> flush privileges;


To update info already in a table.


mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';


Delete a row(s) from a table.


mysql> DELETE from [table name] where [field name] = 'whatever';


Update database permissions/privilages.


mysql> flush privileges;


Delete a column.


mysql> alter table [table name] drop column [column name];


Add a new column to db.


mysql> alter table [table name] add column [new column name] varchar (20);


Change column name.


mysql> alter table [table name] change [old column name] [new column name] varchar (50);


Make a unique column so you get no dupes.


mysql> alter table [table name] add unique ([column name]);


Make a column bigger.


mysql> alter table [table name] modify [column name] VARCHAR(3);


Delete unique from table.


mysql> alter table [table name] drop index [colmn name];


Load a CSV file into a table.


mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);


Dump all databases for backup. Backup file is sql commands to recreate all db's.




# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql


Dump one database for backup.


# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql


Dump a table from a database.


# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql


Restore database (or database table) from backup.


# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql


Create Table Example 1.


mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));


Create Table Example 2.


mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');


MYSQL Statements and clauses


ALTER  DATABASE


ALTER  TABLE


ALTER  VIEW


ANALYZE  TABLE


BACKUP  TABLE


CACHE  INDEX


CHANGE  MASTER  TO


CHECK  TABLE


CHECKSUM  TABLE


COMMIT


CREATE  DATABASE


CREATE  INDEX


CREATE  TABLE


CREATE  VIEW


DELETE


DESCRIBE


DO


DROP  DATABASE


DROP  INDEX


DROP  TABLE


DROP  USER


DROP  VIEW


EXPLAIN


FLUSH


GRANT


HANDLER


INSERT


JOIN


KILL


LOAD  DATA  FROM  MASTER




LOCK  TABLES


OPTIMIZE  TABLE


PURGE  MASTER  LOGS


RENAME  TABLE


REPAIR  TABLE


REPLACE


RESET


RESET  MASTER


RESET  SLAVE


RESTORE  TABLE


REVOKE


ROLLBACK


ROLLBACK  TO  SAVEPOINT


SAVEPOINT


SELECT


SET


SET  PASSWORD


SET  SQL_LOG_BIN


SET  TRANSACTION


SHOW  BINLOG  EVENTS


SHOW  CHARACTER  SET


SHOW  COLLATION


SHOW  COLUMNS


SHOW  CREATE  DATABASE


SHOW  CREATE  TABLE


SHOW  CREATE  VIEW


SHOW  DATABASES


SHOW  ENGINES


SHOW  ERRORS


SHOW  GRANTS


SHOW  INDEX


SHOW  INNODB  STATUS


SHOW  LOGS


SHOW  MASTER  LOGS


SHOW  MASTER  STATUS


SHOW  PRIVILEGES


SHOW  PROCESSLIST


SHOW  SLAVE  HOSTS


SHOW  SLAVE  STATUS


SHOW  STATUS


SHOW  TABLE  STATUS


SHOW  TABLES


SHOW  VARIABLES


SHOW  WARNINGS


START  SLAVE


STOP  SLAVE


TRUNCATE  TABLE


UNION


UNLOCK  TABLES


USE


String Functions


AES_DECRYPT


AES_ENCRYPT


ASCII


BIN


BINARY


BIT_LENGTH


CHAR


CHAR_LENGTH


CHARACTER_LENGTH


COMPRESS


CONCAT


CONCAT_WS


CONV


DECODE


DES_DECRYPT


DES_ENCRYPT


ELT


ENCODE


ENCRYPT


EXPORT_SET


FIELD


FIND_IN_SET


HEX


INET_ATON


INET_NTOA


INSERT


INSTR


LCASE


LEFT


LENGTH


LOAD_FILE


LOCATE


LOWER


LPAD


LTRIM


MAKE_SET




MD5


MID


OCT


OCTET_LENGTH


OLD_PASSWORD


ORD


PASSWORD


POSITION


QUOTE


REPEAT


REPLACE


REVERSE


RIGHT


RPAD


RTRIM


SHA


SHA1


SOUNDEX


SPACE


STRCMP


SUBSTRING


SUBSTRING_INDEX


TRIM


UCASE


UNCOMPRESS


UNCOMPRESSED_LENGTH


UNHEX


UPPER


Date and Time Functions


ADDDATE


ADDTIME


CONVERT_TZ


CURDATE


CURRENT_DATE


CURRENT_TIME


CURRENT_TIMESTAMP


CURTIME


DATE


DATE_ADD


DATE_FORMAT


DATE_SUB


DATEDIFF


DAY


DAYNAME


DAYOFMONTH


DAYOFWEEK


DAYOFYEAR


EXTRACT


FROM_DAYS


FROM_UNIXTIME


GET_FORMAT


HOUR


LAST_DAY


LOCALTIME


LOCALTIMESTAMP


MAKEDATE


MAKETIME


MICROSECOND


MINUTE


MONTH


MONTHNAME


NOW


PERIOD_ADD


PERIOD_DIFF


QUARTER


SEC_TO_TIME


SECOND


STR_TO_DATE


SUBDATE


SUBTIME


SYSDATE


TIME


TIMEDIFF


TIMESTAMP


TIMESTAMPDIFF


TIMESTAMPADD


TIME_FORMAT


TIME_TO_SEC


TO_DAYS


UNIX_TIMESTAMP


UTC_DATE


UTC_TIME


UTC_TIMESTAMP


WEEK


WEEKDAY


WEEKOFYEAR


YEAR


YEARWEEK


Mathematical and Aggregate Functions


ABS


ACOS


ASIN


ATAN


ATAN2


AVG


BIT_AND


BIT_OR


BIT_XOR


CEIL


CEILING


COS


COT


COUNT


CRC32


DEGREES


EXP


FLOOR


FORMAT


GREATEST


GROUP_CONCAT


LEAST


LN


LOG


LOG2


LOG10


MAX


MIN


MOD


PI


POW


POWER


RADIANS


RAND


ROUND


SIGN


SIN


SQRT


STD


STDDEV


SUM


TAN


TRUNCATE


VARIANCE


Flow Control Functions


CASE


IF


IFNULL


NULLIF


Command-Line Utilities


comp_err


isamchk


make_binary_distribution


msql2mysql


my_print_defaults


myisamchk


myisamlog
myisampack


mysqlaccess


mysqladmin


mysqlbinlog


mysqlbug


mysqlcheck


mysqldump


mysqldumpslow


mysqlhotcopy


mysqlimport


mysqlshow


perror


Perl API - using functions and methods built into the Perl DBI with MySQL


available_drivers


begin_work


bind_col


bind_columns


bind_param


bind_param_array


bind_param_inout


can


clone


column_info


commit


connect


connect_cached


data_sources


disconnect


do


dump_results


err


errstr


execute


execute_array


execute_for_fetch


fetch


fetchall_arrayref


fetchall_hashref


fetchrow_array


fetchrow_arrayref


fetchrow_hashref


finish


foreign_key_info


func


get_info


installed_versions


last_insert_id




looks_like_number


neat


neat_list


parse_dsn


parse_trace_flag


parse_trace_flags


ping


prepare


prepare_cached


primary_key


primary_key_info


quote


quote_identifier


rollback


rows


selectall_arrayref


selectall_hashref


selectcol_arrayref


selectrow_array


selectrow_arrayref


selectrow_hashref


set_err


state


table_info


table_info_all


tables


trace


trace_msg


type_info


type_info_all


Attributes  for  Handles


PHP API - using functions built into PHP with MySQL


mysql_affected_rows


mysql_change_user


mysql_client_encoding


mysql_close


mysql_connect


mysql_create_db


mysql_data_seek


mysql_db_name


mysql_db_query


mysql_drop_db


mysql_errno


mysql_error


mysql_escape_string


mysql_fetch_array


mysql_fetch_assoc






mysql_fetch_field


mysql_fetch_lengths


mysql_fetch_object


mysql_fetch_row


mysql_field_flags


mysql_field_len


mysql_field_name


mysql_field_seek


mysql_field_table


mysql_field_type


mysql_free_result


mysql_get_client_info


mysql_get_host_info


mysql_get_proto_info


mysql_get_server_info


mysql_info


mysql_insert_id


mysql_list_dbs


mysql_list_fields


mysql_list_processes


mysql_list_tables


mysql_num_fields


mysql_num_rows


mysql_pconnect


mysql_ping


mysql_query


mysql_real_escape_string


mysql_result


mysql_select_db


mysql_stat


mysql_tablename


mysql_thread_id


mysql_unbuffered_query