Search

Friday, May 18, 2012

Schedule automatic backup in SQL Server

To schedule a database backup operation by using SQL Server Management Studio in SQL Server, follow below steps:
  • Start SQL Server Management Studio.
  • In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.
  • Click Connect.
  • In Object Explorer, expand Databases.
  • Right-click the database that you want to back up, click Tasks, and then click Back Up.
  • In the Back Up Database – DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.
  • In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.
  • In the Script list, click Script Action to Job.
  • In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.
  • Note In the Job Step Properties – 1 dialog box, you can see the backup command.
  • Under Select a page, click Schedules, and then click New.
  • In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.
  • Note: If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.
  • Click OK two times.
You receive the following message:
The backup of database ‘DatabaseName’ completed successfully.
Note: To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this, the SQL Server Agent service must be running.

Thursday, May 17, 2012

Rename SQL Server Instance Name

Run below queries to rename SQLserver Instance Name


sp_helpserver  
select @@servername  
go  
   
sp_dropserver 'OLDSNAME'  
go  
sp_addserver 'NEWNAME','local'  
go  
   
sp_helpserver  
select @@servername  
go  


Remember you have to restart the SQL Server service after executing this.

Wednesday, May 16, 2012

SQL Server Browser not working properly

Today my SQL Server cluster stopped answering on a named instance, I restarted SQL Server Browser but it didn't started.


So I started the process from console using -C argument in order to get more information.


C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c


I noticed that query against instance 1 generated CLNT_UCAST_INST and CLNT_BCAST_EX packets but query against instance 2 generated only CLNT_UCAST_INST packets.


I googled for this and then I found a post saying to delete this registry key:


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.2], 
I did it and then I restarted service, and now my SQL server is running perfectly.



Tuesday, May 15, 2012

Foreign Keys without Index

Run below script to generate a create-script for inserting indexes – over all tables in the database.


SELECT  
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['  
+ IndexTables.[name]  
+ ']'') AND name = N''NCI_'  
+ IndexTables.[name] + '_' + IndexColumns.[name]  
+ ''') '  
+ 'CREATE NONCLUSTERED INDEX [NCI_'  
+ IndexTables.[name] + '_' + IndexColumns.[name]  
+ '] ON [dbo].['  
+ IndexTables.[name]  
+ ']( ['  
+ IndexColumns.[name]  
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'  
FROM sys.foreign_keys ForeignKeys  
INNER JOIN sys.foreign_key_columns ForeignKeyColumns  
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id  
INNER JOIN sys.columns IndexColumns  
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id  
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id  
INNER JOIN sys.tables IndexTables  
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id  
ORDER BY IndexTables.[name], IndexColumns.[name]  

Monday, May 14, 2012

Clustered SQL Server fail to start

Today I got the following error:


The SQL Server (INST1) service terminated with service-specific error 10048 (0x2740).


and also


Server is listening on [ 192.168.51.27 1433].
Server TCP provider failed to listen on [ 192.168.51.27 1433]. Tcp port is already in use.
TDSSNIClient initialization failed with error 0x2740, status code 0xa.


The root cause of this issue is because of cluster checkpoint service behavior. If a setting is changed while the resource is online, that change will get check pointed to the CPT have file in the cluster quorum disk.
To fix it follow the instructions specified on: http://support.microsoft.com/default.aspx?scid=kb;EN-US;912397


cluster res "SQL Server" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"


Correct the protocol parameter for the clustered instance of SQL Server on all nodes


cluster res "SQL Server" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"