SQL Query by Date and Get 4 Nearest Record

Today here to play around with the SQL script and I try to revise my SQL script.

When come to date calculation, there is a build in function to calculate the date different. The function name is datediff(). How to use this function as it simple and you have to pass 2 value into this function and it will return the difference of day between the 2 value.

Example:
Value A: 2015-08-20; Value B: 2015-08-12 and the different of these2 day are 8

SELECT datediff('2015-08-20 00:00:00','2015-08-12') as 'dayto'

For today date there is a build in function too which is CURDATE()

In SQL if you want to get specific numbers of result return by SQL server you may using LIMITthen follow by the number of record.

Example to return 10 record.
Limit 10

Well the complete SQL script to get the top 4 nearest evetStartDate date from today as follow.

SELECT datediff(`evetStartDate`,CURDATE()) as 'dayto', `eventguid`,`eventName`,`evetStartDate`
FROM event
WHERE `evetStartDate` > NOW()
ORDER BY `evetStartDate` ASC
LIMIT 4"

Backup MS SQL Server

In order to ensure the database data is always goes well, so here I am to blog on how to perform the backup for MS SQL. For sure you may need to have access to the database server by using the SQL Server Management Studio. Select the database that you wish to backup and right click -> task -> backup

After the action, there will be a new window pop up and that the configuration page that how the backup to be done. There is few backup type available and in my case i would like to perform a full backup to backup the entire database. At the bottom there is a destination of the backup, if you wish to change it, you may need to remove it. For adding the custom backup destination, click on add and select the destination of the directory. 

There is more backup option available which locate at top right of the window. Select the Option, you may see the overwrite media configuration. You may able to replace the backup by select Overwrite all existing backup sets. So old the old backup file will be replace with the new backup.

After finish configure, it will start the backup process

Lastly, when the backup is finish, there will prompt a message.

Database is Suspect Mode

There is a time the electric blackout in my place and cause the database is unable to open. There is a (Suspect) after the database name as show as screen shot below. This know as Suspect Mode.

This issues may cause by:

• There is not enough space available for the SQL Server to recover the database during startup.
• The database cannot be opened due to inaccessible files or insufficient memory or disk space.
• The database files are being held by operating system, third party backup software etc.
• There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

In order to resolve this problem, recovering is needed to access again the database. So first of all you may need a database administrator account login the MS SQL Management and click on new query button. And start the script as below:

EXEC sp_resetstatus [YourDatabaseName];

ALTER DATABASE [YourDatabaseName] SET EMERGENCY

DBCC checkdb([YourDatabaseName])

ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([YourDatabaseName], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [YourDatabaseName] SET MULTI_USER




Replace YourDatabaseName with your database name. In my example will be as follow.




EXEC sp_resetstatus [test_dr];

ALTER DATABASE [test_dr] SET EMERGENCY

DBCC checkdb([test_dr])

ALTER DATABASE [test_dr] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([test_dr], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [test_dr] SET MULTI_USER




Once you have done the script, click on execute query at the top. If for the first time is fail, double click again the make the script run. Then refresh your Object explorer and the (Suspect) will get rid and able to access the database again.

Microsoft SQL Server Error 926

In my development life, there is many issues coming up to resists you in development. Database is one of it,  as today I face this issue below. This error able me to start the MS SQL Management Studio but unable to see any one of the db on it. After some time of troubleshooting, finally get this issues resolve.

In order to resolve this issues, you may need another working database. Copy the MSDBDATA.mdf and MSDBLOG.ldf  from another environment machine.

By default, the files directory  will some where around here. (If you have custom path, please check your own database setting.)

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

In order to copy and replace both of the files, you need to stop your SQL Server Service  then only able to copy and replace both of the file.

Please make sure you make a backup before you replace the file as a safe keeping for just in-case purpose.

Once you replace the working MSDBDATA and MSDBLOG files, start the SQL Server service again.