DBASearch

SQLCMD helps DBAs at the time of crisis

2012-05-08T04:29:00.001-07:00

We do set default DB for logins other than master database in SQL server for security reasons. There is a scenario if default database gets offline or corrupts.

Suppose default database is XYZ that goes offline or corrupts then SQL does not allow connection to all logins those having XYZ as a default database. Because it tries to connect the default database first.

Thanks to SQLCMD that has a way to get in, using to -d switch

sqlcmd -S<servername> -d <Other_database_Name_ABC> -U<login> -P<password>

It is helpful in case we disable Built-In logins, SA and don't have choice of other logins to connect to SQL server.

SQL Login locked out status check script

2011-10-30T09:10:00.000-07:00

This script is designed to find the logins those are locked out in the database Server.

It is tested to run on:

SQL 2000 - NO (Login lock feature was not introduced in this SQL version)

SQL 2005 - YES

SQL 2008 - YES

set nocount on

declare Cur_locked_login cursor for select name from master..syslogins order by name

declare @loginname nvarchar(100)

open Cur_locked_login

fetch next from Cur_locked_login into @loginname

while @@FETCH_STATUS >= 0

begin

if (select LOGINPROPERTY(@loginname,'islocked')) =1

print 'Login -> ' + @loginname +' is LOCKED'

fetch next from Cur_locked_login into @loginname

end

close Cur_locked_login

deallocate Cur_locked_login

Run SQLCMD from SQL 2008 management studio query window

2011-10-27T20:36:00.000-07:00

Microsoft embedded sqlcmd to run thru query-window. Now you can run SQLCMD without enabling xp_cmdshell from SQL management Studio. ( As you know, it is risky to enable this surface area configuration parameter ).

Open Management Studio -> Query -> SQLCMD Mode

Now, you can run a sqlcmd statements from a query window like below -

!!sqlcmd -S<ServerName> -E -Q"select * from sysfiles"

If you don't have a tool to run query against multiple database servers. No worries !! You can add your all database servers as a linked server in a server and run SQLCMD to query against multiple servers.

If you have more than 50 servers, there are many SQL diagnosis tools are available in the market. I worked with idera multi query tool. It is easy to use. There are a lot other things can be done using this tool. You can get more details here -
http://www.idera.com/Product-Tour/SQL-admin-toolset/SQL-Multi-Query/

Database backup tool

2011-10-27T12:04:00.000-07:00

SQL database BackupTool:
hyperbac is a tool to run the compressed SQL backups. It has feature to encrypt the backupfile. Why the encryption is required for backup !! In many organization there is policy to FTP the backup file and most of the customers now a days has mandate to provide encrypted backup file. In your organization you can refer this tool.

More important, hyperbac can be used to restore a single table from a backup file. You just need to have to create a LINKEDSERVER and you can fire query against object.

You can check more details - http://www.red-gate.com/products/dba/sql-hyperbac/