MS SQL – disable constraint to update/delete records

30 Jan

In the relational db you have many relations, and in order to delete a certain record you have to delete all of the relations that record have in all the referenced tables. One way is to temporary disable the constraints – update the records – enable the constraints. You can use the command below:

* Disable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX NOCHECK CONSTRAINT all"

* Re-enable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX WITH CHECK CHECK CONSTRAINT all"


* Disable the constraints on a table called tableName:
ALTER TABLE tableXX NOCHECK CONSTRAINT ALL

* Re-enable the constraints on a table called tableName:
ALTER TABLE tableXX WITH CHECK CHECK CONSTRAINT ALL

Read more:
http://stackoverflow.com/questions/737115/turn-off-constraints-temporarily-ms-sql
http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql/161410#161410


Mysql – Copy database to another server

30 Jan

I used the sqlyog to copy database – which you can download from:

https://code.google.com/p/sqlyog/

https://code.google.com/p/sqlyog/wiki/Downloads

 

Or you can use sqldump command:

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

Can read more:

http://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another

http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database

http://serverfault.com/questions/194462/any-way-to-copy-the-schema-from-one-mysql-database-to-another

http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html

http://dev.mysql.com/doc/refman/5.6/en/copying-databases.html

 

 

 

 

 

 

Log Backup Failed to Complete

12 Nov

When you get this alert in the MS SQL 2012 server, then you probably haven’t managed to perform a full backup and transaction log backup of the database after creating the database the first time. This is when you have “Recovery model” set to full. Run this command in the editor for that database:

BACKUP DATABASE [DATABASENN] TO DISK = N’e:\mssql\backup\DATABASENN_1.bak’
GO
BACKUP LOG [DATABASENN] TO DISK = N’e:\mssql\backup\DATABASENN_1log.bak’
GO

Read more:

http://support2.microsoft.com/kb/2646329

http://stackoverflow.com/questions/3131258/backup-failed-to-complete-the-command-backup-database

http://blog.sqlauthority.com/2010/03/27/sql-server-fix-error-4214-backup-log-cannot-be-performed-because-there-is-no-current-database-backup/

 

 

SQL server 2012 – fail/restart results in reseed of identity

11 Nov

In MS SQL Server 2012 we experience jump in identity column when something fails in the database or a restart of the instance – jumps 1000. In order to fix this issue you have to add a trace flag:

  1. Open “SQL Server Configuration Manager”
    * Click”SQL Server Services” on the left pane
    * Right-click on your SQL Server instance name on the right pane
    * Click “Properties”
    * Click “Startup Parameters”
    * On the “specify a startup parameter” textbox type “-T272”
    * Click “Add”
    * Confirm the change
  2. Restart service

 

You can read more

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

http://sqlservice.se/sv/start/blogg/updated-microsoft-sql-server-trace-flag-list.aspx

http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry

http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-

http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server

 

SQL server – find location of databasefiles

7 Nov

You can run this select to find location of your data and logfiles for all of the databases on your server

SELECT name, physical_name AS current_file_location FROM sys.master_files

 

Read more

http://blog.sqlauthority.com/2009/02/17/sql-server-find-current-location-of-data-and-log-file-of-all-the-database/

Create new user in mysql and grant access

24 Oct

How to create new user and grant access in mysql:

GRANT ALL PRIVILEGES ON mydatabase.* To 'user'@'hostname' IDENTIFIED BY 'password';

Using .* after mydatabase - means that you want grant all the tables in that database to the user


Read more
http://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost

http://stackoverflow.com/questions/1720244/create-new-user-in-mysql-and-give-it-full-access-to-one-database

Import databasedump into mysql

1 Oct

If you have a database dump file, and you need to import into mysql database – you can user the following:

  • mysql command line client
    • mysql.exe -u root -p -D DBNAME < dumpfile.sql

 

Read more

http://stackoverflow.com/questions/17666249/how-to-import-a-sql-file-using-the-command-line-in-mysql

http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/

https://support.tigertech.net/mysql-import