MS SQL

Standard Maintenance Plans

Daily Maintenance Plan

1. History Cleanup Task
   • Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
   • Older than 2 weeks
2. Reorganise Index Task
   • All user databases
   • Compact large objects
3. Update Statistics Task
   • All user databases
   • Update: All existing statistics
   • Scan type: Full scan
4. Back Up Database Task
   • Backup type: Full
   • Databases: All databases
   • Backup will expire: Leave unticked
   • Create a backup file for every database: Update as appropriate
5. Maintenance Cleanup Task
   • Delete files of the following type: Backup files
   • File extension: bak
   • File age: Select as appropriate

Hourly Transaction Log Backup

1. Back Up Database Task
   • Backup type: Transaction Log
   • Databases: All user databases
2. Maintenance Cleanup Task
   • Delete files of the following type: Backup files
   • File extension: trn
   • File age: Select as appropriate

Commands

Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also MySQL

SELECT * FROM MASTER..SYSDATABASE					       /* Show databases */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'	       /* Show tables */
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */

Gain Sysadmin Rights

In order to gain sysadmin control over a SQL instance you need to have local administrator access over the server, and be able to shut the SQL service down. You should only need to do this if nobody has sysadmin rights, and trying to add gain sysadmin rights or perform other tasks fails with errors similar to...

• User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

If your running in a virtual machine, take a snapshot 1st for piece of mind...!

1. Login to the server as a local admin (any account with local admin rights)
2. Stop the SQL Server service and any other SQL services
   • If the database is running as a named instance the name will appear in brackets in the service name - note the instance name
3. Ensure that any Microsoft SQL Server Management Studio instances are closed
4. Start the SQL instance in single-user mode
   1. Open a command prompt in the Binn folder of the instance
      • EG C:\Program Files\Microsoft SQL Server\MSSQL10.DB\MSSQL\Binn
   2. If running as a named instance noted above use (replace <Instance>)...
      • sqlservr.exe -s<Instance> -m -c
   3. Otherwise use...
      • sqlservr.exe -s<Instance> -m -c
   4. In amongst other messages you should see the following to state that the DB is up and ready
      • SQL Server is now ready for client connections. This is an informational message; no user action is required.
5. Connect to the SQL instance (see below for probs)
   1. Open another command prompt in the Binn folder of the instance
   2. If running as a named instance noted above use (replace <ComputerName> and <Instance>)...
      • sqlcmd -S<ComputerName>\<Instance>
   3. Otherwise use (replace <ComputerName>)...
      • sqlcmd -S<ComputerName>
6. Add the required accounts into the sysadmin role (replace <Login> with Windows account
   1. sp_addsrvrolemember '<Login>', 'sysadmin'
      • EG sp_addsrvrolemember 'DOMAIN\user', 'sysadmin'
   2. GO
   3. Repeat the above two steps as required
7. Restart the server

Source - http://support.microsoft.com/kb/937682

Unable to Connect in Single User Mode

When trying to connect to a SQL server instance in single user mode you received an error similar to...

• Login failed for user 'DOMAIN\user'. Reason: Server is in single user mode. Only one administrator can connect at this time.

This is caused by another user (or piece of software) having already looged into the SQL instance. Ensure that...

• Any applications that use the SQL instance are not trying to connect (disconnect the SQL server from the network if required)
• No SQL services are running
• No Microsoft SQL Server Management Studio instances are running (including via RDP, check for Ssms.exe or Ssms.exe *32 in Task Manager