Author Archives: Jugal Shah

About Jugal Shah

Jugal Shah has 12 plus years of experience in SQL Server and MySQL database technologies. You can check his profile on https://sqldbpool.com/certificationawards/ URL.

MongoDB Post Installation Configuration

On the completion of MongoDB installation https://sqldbpool.com/2015/02/24/mongodb-installation-on-windows-server/, next step is to configure the MongoDB.

Create a MongoDB config file using any text editor (e.g. NOTEPAD) and save it as MONGO.config into bin directory.

##to store data
dbpath=C:\Program Files\MongoDB 2.6 Standard\Data
 
##all output loging
logpath=C:\Program Files\MongoDB 2.6 Standard\Log\mongo.log
 
##log read and write operations
diaglog=3

Start MongoDB Server, make sure to open the command prompt as Administrator. Go to MongoDB bin directory and write the below command. It will start the MongoDB Server and keep the command prompt running, don’t close it.

mongod.exe --config mongo.config

MongoDBServer

To connect to MongoDB open another window of command prompt, browse the bin directory and type the  MONGO command.
startmongo

You can also create the window service of MongoDB by executing below command. Make sure to open the command prompt as Aadministrator, otherwise you will get the access denied error.

mongod --config mongo.config --install

mongoservice

MongoDB Installation on Windows Server

You can download the MongoDB https://www.mongodb.org/downloads. Once you download the MongoDB (Version 2.6.7), follow below step by step to process to install MongoDB. Double click on mongodb-win32-x86_64-2008plus-2.6.7-signed.msi and follow below screen.

 

 

You will see the below files into MongoDB folder.

Script to list out SQL Logins and Database User Mappings

You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings.

Syntax
sp_msloginmappings @Loginname , @Flags

@Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
@Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0

use master
go
exec sp_msloginmappings 'sa', 0

Image2

use master
go
exec sp_msloginmappings 'sa', 1

Image1

In case you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or powershell. You can use the below script.

 create table #loginmappings(  
  LoginName  nvarchar(128) NULL,  
  DBName     nvarchar(128) NULL,  
  UserName   nvarchar(128) NULL,  
  AliasName  nvarchar(128) NULL  
 )  
 
 insert into #loginmappings
 EXEC master..sp_msloginmappings
 
 select * from #loginmappings
 
 drop table #loginmappings

How to restart an Interrupted Database Restore in SQL Server?

Steps to restart an Interrupted Database Restore in SQL Server

There may scenario when you are restoring VLDB database backup and the restore database command interrupted due network failure, power failure, SQL Server Service restart or cluster failover. In this kind of scenario you can easily start the interrupted restore from the point of failure using WITH RESTART command.

Before you start restoring the database using WITH RESTART option check database is in Restoring State not in any other state.

You can check the database state either from SSMS object explorer or by executing below T-SQL command

select name,state_desc from sys.databases

Once confirmed that database is in restoring state execute the backup command WITH RESTART clause

RESTORE DATABASE [AdventureWorks]
FROM DISK ='D:\Backup\AdventureWorks.bak'
WITH RESTART, STATS = 20
GO

Database Backup WITH CHECKSUM

Backup WITH CHECKSUM

When WITH CHECKSUM option is used while taking backup, the backup process will verify each page for checksums and torn page. In case bad page checksum found, the backup will stop. Using backup checksums may affect workload and backup throughput.

If you want to continue the backup, you have to write CONTINUE_AFTER_ERROR in backup WITH clause.
In case backup completes successfully it means no bad checksums.

Example

backup database sqldba to disk = 'c:\jspace\sqldba1.bak'
with checksum

You can also use the 3023 trace flag for the check sum option, when 3023 trace flag is on WITH CHECKSUM option will be applied all the backups by default.

DBCC TRACEON(3023)
GO                              

backup database sqldba to disk = 'c:\jspace\sqldbatraceflag.bak'

DBCC TRACEOFF(3023)
GO                              

Let’s check if the 3023 trace flag applied the page checksum to above backup command or not. Execute below query for it.

RESTORE HEADERONLY
FROM DISK = 'c:\jspace\sqldbatraceflag.bak'
GO  

HasBackupCheckSums value is 1, it means that WITH CHECKSUM option applied to backup using 3023 trace flag.
checksum