DB


Microsoft SQL server – Database permissions:

Open Microsoft SQL server management studio express > select the database > select user > assign permission to the following

1. db_access admin
2. db_datareader
3. db_datawriter
4. db_owner
5. db_securityadmin
——————————–
If a customer want to backup a database from the server and wants to store it on his local machine, please follow the below steps:-

1. Open Microsoft SQL server management studio express in the local machine
2. Login
3. Database > right click the database > task > choose ‘backup’
4. In the window, ‘remove’ the default location and press ‘add’
5. Add the location of the customer domain’s httpdocs.
6. Now backup the database

When the customer press ‘backup’ > the database will be ‘backed up’ to the customer httpdocs folder.

He can now download the backup using filezilla or other third party applications.

——————————————————————————————————————————

Database showing ‘0 items’ in MsSQL mgmt studio:

If a working database shows no items. Then

MsSQL mgmt studio -> Right click the database -> Bring it to offline

Now bring it back to online after some time.

Database will show all the items that was before.
————————————————————————————————–
Command to SHRINK DATABASE FILES

Open Microsoft SQL mgmt studio -> right click the db and select ‘new query’

DBCC SHRINKFILE (dbname_log, 2)
—-where dbname is the databasename and leave underscore and log as its. 2 represents that the log file will be shrinked to 50%. Suppose if you have 10MB log then you can get 5MB. Similarly you can use 1 and 4.

BACKUP LOG dbname WITH TRUNCATE_ONLY

————————————————————————————————————————–

MS SQL server:

Sometimes when you restore a database, it will give error showing that, db might be used by other program….

To counter this, you have to stop the database and then you can restore it.
> Open Microsoft SQL server management studio express
> select the database.
> Select ‘New Query’ from the menu
> In the right hand side type the below query
> Press ‘Execute’
> This will stop [offline] the database, now you can restore the db.
> After the restoration, make the database online by the second command

To bring DB to offline or online
sp_dboption ‘dbname’,’offline’,’true’ (It will make the database to become offline)
sp_dboption ‘dbname’,’offline’,’false’ (It will bring the database back to online)

Note: you have to give the quotes too. Sometimes it will not work, in that case, delete those single quotes and type single quotes from your keyboard

If still its not working….

STop the MSSQL service in the mgmt studio (by right clicking the IP / system name) and start the service again.

Now try restoring the db

————————————
Permissions

—————————————-—————————————-

To login MySQL
a. via command line
b. Graphical method

MySQL granting permission:

GRANT privileges
ON databasename.tablename
TO username
Identified by ‘password’;

example:

GRANT SELECT, INSERT     [I]//or GRANT ALL to grant all privileges[/I]
ON DB1.*
TO AMEZIS
IDENTIFIED BY ‘PIZZA’;

MySQL commands:

http://www.bios.niu.edu/johns/bioinform/mysql_commands.htm
—————————————
Resetting MySQL administrator password in windows

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

—————————————
Restoring MSSQL database using MDF and LDF files

1.Open the Management Studio console application.

2
Select “new query”

3
Type the following command into your query console:

sp_attach_db ‘Database_Name’,’c:\backupDB.mdf’,’C:\backupLogs.ldf’

The first parameter is the “Database_Name.” This is the name of the database that registers with SQL Server. This can be the original name of the backed up database, or you can give it a new name. The second parameter is the location and file name for the MDF file. The third is the location for the LDF file. You can restore from the local hard drive, an external drive or a shared network location.

4
Press the “F5” key. This executes the SQL statement and restores the database along with the log file.

Text from here: http://www.ehow.com/how_6539778_restore-mdf-ldf-files.html

—————————————
MySQL root password in the server
You can change this is at the plesk control panel.
Login with admin login credentials -> database -> MYsql -> preferences.
After resetting it, make sure its working with the updated password.

OR follow the steps here (this works 100%)

Lost your MySQL root password? Resetting MySQL root password is simple. Please follow these steps-

# Login to your MySQL server.
# Locate the mysql.ini file.
-This should be something like C:\MySQL\my.ini or “C:\Program files\MySQL\bin\mysqld-nt.exe” –defaults-file=”C:\Program files\MySQL\Data\my.ini” MySQL
-You can also check this by viewing the Properties of the MySQL service command line under the Services MMC.
# Edit the appropriate *.ini file and add the following line immediately after [mysqld] ;
skip_grant_tables = 1
# Restart the MySQL service. Please note that MySQL is now running unsecured.
# From a command prompt, change to the /bin directory (Ususally C:\MySQL\bin or whereever under MySQL installed location) and enter the following command to login as root:
mysql -u root
# Then input the following command:
update mysql.user SET Password = Password(’newpassword’) WHERE User=’root’;
# Now remove the line you added to my.ini in previous step and restart MySQL.
# Make sure you can login to MySQL using the updated password.

Note:
If you are working on plesk, the location can be
C:\Program Files\SWsoft\Plesk\Databases\MySQL\Data

– This is posted by Arun Rajaraam (My former Colleague)
Website: a-r-u-n.com/b

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s