MS SQL SERVER DBA

By taking appropriate precautions, most of these breaches can be prevented or detected before they get out of hand.  1. Use a dedicated server for your database Host your database on a dedicated server. Whether it is local or in the cloud, spend the extra cash on a dedicated server to prevent security leaks and breaches. 2. Harden the Operating System On your dedicated server, the first step is to implement operating system hardening. Many hardening techniques exist. At a minimum, you need to: Change the default ports, as described below. Hide SQL instances from showing in the network, as described below. Allow only network protocols that are needed. CONNECT permission should be granted only on endpoints to logins that need to use them. If there is a need to work with SQL Login, install an SSL certificate from a trusted CA rather than SQL Server's self-signed certificates. Avoid the exposure of SQL Server to the public internet/intranet. Change default ...

     Now we are going to see how we are going to take a table backup. Backup single table exec master .. xp_cmdshell 'BCP export1.dbo.tab1 out new.txt -c -U sa -P sa -S ION-0481AD1D360\SQLTEST' Restore from file to table exec master .. xp_cmdshell 'BCP export1.dbo.tab1 in new.txt -c -U sa -P sa -S ION-0481AD1D360\SQLTEST'

I recently had to setup Database Mail on dozens of SQL Server instances.   Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here.    My needs were simple so I only needed a single SMTP account and profile.  I decided to make the profile the default public one so that all msdb users would use this profile unless a different sp_send_dbmail @profile value was explicitly specified.  You might want to extend this script if you need other accounts/profiles, such as separate ones for administrative alerts or user reports. Setup Script Below is the template script I used for my task.  The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and you will of course need to customize the mail server name and addresses for your environment. -- Enable Database Mail for this instance E...

Tail-Log Backups This topic is relevant only for databases that are using the full or bulk-logged recovery models. In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup. SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure. Not all restore scenarios require a tail-log backup. You do not have to have a tail-l...