tag:blogger.com,1999:blog-57339026934467448642024-03-26T23:38:03.022-07:00Database SimplifiedKuldeephttp://www.blogger.com/profile/02883629969174190080noreply@blogger.comBlogger74125tag:blogger.com,1999:blog-5733902693446744864.post-10560624869510208322015-08-20T23:55:00.000-07:002015-08-30T23:39:34.062-07:00Rebuild System Databases in SQL Server Failover Cluster Environment This blog describes process of rebuilding system databases in clustered environment. Sometimes an unexpected shutdown of SQL Server database server has resulted in crashing of the SQL Server or system databases. In this case when you will try to start SQL Server service, it will not start and if you will check the SQL Server error logs to check error message, you could see the following Neeraj Singh Bhandarihttp://www.blogger.com/profile/12728209774423569620noreply@blogger.com4tag:blogger.com,1999:blog-5733902693446744864.post-34729857257204889182015-08-06T00:08:00.001-07:002015-08-06T01:02:19.944-07:00Database Backup Encryption in SQL Server 2014 This blog describes process of database backup with encryption. Database Backup Encryption is new feature that is available in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key). This blog will show you how we can performed encrypted database backup by creating master key and certificateNeeraj Singh Bhandarihttp://www.blogger.com/profile/12728209774423569620noreply@blogger.com5tag:blogger.com,1999:blog-5733902693446744864.post-91541964040208003492015-08-05T23:49:00.000-07:002015-08-06T00:06:48.377-07:00Backup Database using PowerShell : Microsoft SQL Server
Often there’s a need to perform backup of all multiple databases on an
instance or maybe multiple databases on multiple instance across environment.
Now, this may well become a laborious task if you don’t have any fancy tool to
do the job for you and that’s exactly where PowerShell comes to rescue.
PowerShell can be used to perform such task easily.
Let’s Get Started!!!
How to Use the Anonymoushttp://www.blogger.com/profile/13330352261105567086noreply@blogger.com3tag:blogger.com,1999:blog-5733902693446744864.post-27416588233651141702012-06-10T20:44:00.002-07:002012-06-10T20:44:54.084-07:00Bad Performance Of View In SQL ServerIn this post I am going to describe the problem I encountered in a view, which may affect your view performance badly. Create tables from following script
If(OBJECT_ID('PerfTest1') is Not null)Drop Table PerfTest1
Create Table PerfTest1(Id int Identity(1,1),Code Int,CodeName Char(30))
Insert Into PerfTest1 Values(1,'Archo'),(2,'Halie'),(3,'Raiye'),(4,'Amity')Go 300
If(OBJECT_ID('PerfTest2') Anonymousnoreply@blogger.com2tag:blogger.com,1999:blog-5733902693446744864.post-52152342052786133892012-06-03T20:48:00.000-07:002012-06-03T20:48:14.463-07:00Restore Database From SnapshotThis is another way of restoring database to a time when snapshot was taken but it has certain restrictions lets see what are those ? Database should have only one snapshot. Cannot restore files removed after taking snapshot. Transaction log backup chain is broken So make sure you take full backup before restore. Restore From snapshot will drop all full text catalogs. You must understand that Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-2006322222665862002012-05-29T21:21:00.000-07:002012-05-29T21:21:00.529-07:00Import Text File To SQL ServerThis blogs describes how flat files or text files can be imported to SQL Server with defining field information in a format file (xml file). Let’s see how ? Download Sample Text File So You can download the text file from the given link or if you have your own flat/text file with you then copy file and put it into the folder. For me its “D:\dbsimplified” Now You must be knowing fields(FieldName) Anonymousnoreply@blogger.com2tag:blogger.com,1999:blog-5733902693446744864.post-29570747960603005832011-12-25T20:28:00.002-08:002012-04-17T03:21:39.382-07:00Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.Collation conflicts are encountered when u try cross database query or cross server query or joining tables, because joining tables might have different collation settings. You must handle these query with specifying appropriate collation while joining to tables having different collation data. Let Replicate the issue with following example. Create following tables with script given below. CREATEAnonymousnoreply@blogger.com5tag:blogger.com,1999:blog-5733902693446744864.post-47437185925672785852011-12-25T19:05:00.001-08:002011-12-27T08:31:19.809-08:00An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.Inserting explicit value for identity column in a table can not be done directly as identity column is bound to rule of incrementing them with specified number. If you try to insert value explicitly you encounter following error message. Msg 8101, Level 16, State 1, Line 1An explicit value for the identity column in table 'TableName' can only be specified when a column list is used and Anonymousnoreply@blogger.com2tag:blogger.com,1999:blog-5733902693446744864.post-3853822354383056602011-12-05T07:53:00.001-08:002011-12-05T20:29:39.838-08:00Nested Transaction In SQL ServerSQL Server facilitate us to define nested explicit transaction. We may need to define nested transaction in many situation, so before you define or use nested transaction you must be aware of behavior of nested transaction. Create tables from following script to perform following tests.Create Table Parent_Table(Id int Identity(1,1),DataValue Varchar(20))Go Create Table Child_Table(Id Int IdentityAnonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-33831811565631299112011-12-02T23:34:00.001-08:002011-12-04T21:07:11.053-08:00How To Move TEMPDB In SQL ServerIn this blog post we will see how can we move location of tempdb database files in sql server. Moving of database may be required for many reasons like for security purpose or space constraints or etc. As tempdb is system database it cannot be detached or attached and cannot be restored like user databases. You need to be more alert when you are planning to move tempdb database files. Let’s see Anonymousnoreply@blogger.com2tag:blogger.com,1999:blog-5733902693446744864.post-49282504999567552282011-11-26T08:37:00.001-08:002011-11-28T20:50:56.652-08:00Memory Utilization In SQL Server (Database Level) Its common question for DBAs to find memory consumption by each database. With SQL Server 2000 its was quite difficult to find but with SQL Server 2005 + we have DMV (Dynamic Management Views) “sys.dm_os_buffer_descriptors” which provides memory utilization by each database or table in database. So let’s use the DMV to find current memory utilization by each database. Before we start this Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-72648463818242747092011-11-24T07:46:00.001-08:002011-11-25T00:27:08.986-08:00Move Database In SQL Server With Minimum DownTimeWhen working with very large database and there is need to move database from one server to another, then downtime is always an issue in such cases. There are several ways to move database from one location to another. Here I am describing an efficient way to move database from one location to another location. Following option only works if database recovery modal is Full or Bulk Logged. Let’s Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-80707125581110888622011-11-10T08:46:00.001-08:002011-11-10T20:07:43.114-08:00Database Backup At Multiple Location Using Mirror In SQL ServerSQL Server 2005+ At times we come across issue where we need multiple copies of database at different places. Many people end up taking single backup of database and making required copy of backup and moving them to required location using some cmd or script. But now this can be achieved using MIRROR with backup command. Mirror option allows you to create maximum 4 copies of database backup at a Anonymousnoreply@blogger.com3tag:blogger.com,1999:blog-5733902693446744864.post-18288171212586784912011-11-09T08:40:00.001-08:002011-11-09T19:39:13.904-08:00How To Get Comma Separated Value In SQL Server.In the earlier post we have seen use of xml path to get comma separated result. This post describes how to get comma separated value from a result set or a table data. Create Table With Following Script. Create Table CSData(Id Int Identity(1,1),DepartmentName Varchar(40))Go Insert Into CSData(DepartmentName) Values('IT'),('FINANCE'),('MEDICAL'),('TRANSPORT'),('TALENT TRANSFORMATION'),('IMG')Go Anonymousnoreply@blogger.com5tag:blogger.com,1999:blog-5733902693446744864.post-63160821816327501332011-11-09T08:08:00.001-08:002011-11-09T19:44:22.949-08:00The media family on device is incorrectly formed. SQL Server cannot process this media familyThis blog post deals with the problem of restoring SQL Server Database from higher version to lower version. We generally encounter error following error. Error while restoring database from SQL server 2008 to SQL server 2005 Msg 3241, Level 16, State 7, Line 1 The media family on device ‘<backup path>’ is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, Anonymousnoreply@blogger.com4tag:blogger.com,1999:blog-5733902693446744864.post-32986521328113769122011-11-07T07:17:00.001-08:002011-11-08T08:39:37.113-08:00How to find list of stored procedure with content info in SQL ServerThis post describes the how can you find list of stored procedure with specific text content or how to answer following question. List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement. List All Stored Procedure, Function, Views, Triggers which uses temporary table. List All Stored Procedure, Function, Views, Triggers which uses a particular table.Anonymousnoreply@blogger.com2tag:blogger.com,1999:blog-5733902693446744864.post-57008198404248262712011-11-07T06:36:00.001-08:002011-11-08T08:37:08.410-08:00Find Tables With Column Name in SQL Server DatabasesThis post describes how one can find list of all the tables where a column with a particular name exists in a databases. Many people come across this issue and searches for query. So here is the query with example. I am using following query to find out all the table having name “Status” in master database.Use masterGoSelect B.name as TableName, A.name As ColumnName,C.name as DataTypefrom Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-54472199119461124102011-11-02T20:23:00.001-07:002011-11-02T20:26:58.643-07:00Is Data Always Sorted On Primary Key (Clustered Index) In SQL Server ?Whenever we create a primary key on a table and we generally get data sorted on primary key defined. but that’s not always the case. Let’s examine it. Create Table and Populate Data With Following ScriptIf Exists(Select * From sys.Objects where object_id=object_id('Records'))Drop Table Recordsgo CREATE TABLE [dbo].[Records]( [Id] [int] IDENTITY(1,1) Primary Key NOT NULL,&Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-33281756581370423382011-10-29T10:04:00.001-07:002011-10-31T01:57:13.609-07:00Policy In SQL ServerSQL Server 2008 + Policy based management introduced in sql server 2008 is really a nice feature. Where DBA can manage certain activity by enforcing policy based on specified conditions. You can enforce policy at database level. How To Create Policy ? The following step by step shows creation of policy for a stored procedure name standard. Create a condition for policy Create A Condition Anonymousnoreply@blogger.com3tag:blogger.com,1999:blog-5733902693446744864.post-26563255858533842442011-10-28T23:25:00.001-07:002011-10-31T01:51:23.302-07:00Create SNAPSHOT In SQL ServerSQL SERVER 2005 + (Only Enterprise Edition) SNAPSHOTS are read-only views of actual database of a particular time and are really useful for sharing load for reporting purpose. You can have multiple snapshot against a single database. Working of SNAPHOT ? Suppose you have created SNAPHOT of database at 9:00 AM. You will see that (.ss) snapshot file size is very small initially and as Anonymousnoreply@blogger.com2tag:blogger.com,1999:blog-5733902693446744864.post-63943679080522510472011-10-28T22:19:00.001-07:002011-10-28T22:25:38.219-07:00Password Protected Backup In SQL ServerPassword protected database backup : Setting backup media password are important and useful for securing misuse of data. If database backup is password protected then one will not be able to restore and see details from backup file using RESTOREFILELIST or HEADERONLY command. How to take password protected database backup? Create A DatabaseIf DB_ID('DBS_Test') Is null Create Database DBS_TestGo Anonymousnoreply@blogger.com13tag:blogger.com,1999:blog-5733902693446744864.post-32870674048196411252011-10-28T22:13:00.001-07:002011-10-28T22:17:02.836-07:00Get Information From Backup File In SQL ServerThis post describes how can we get information from backup file without restoring it. information about file groups, data and log files and Database information like server name and user name, internal database version, backup size and etc. Backup A Database Backup Database CDB To Disk='D:\CDB_Full.Bak' With MediaName='CDB_FullBackup'Go Now we have a backup file located at D:\CDB_full.Bak, Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-81027858114503641002011-10-26T22:45:00.001-07:002011-10-27T10:29:25.702-07:00Configuring Database Mail In SQL Server Step By StepTo Configure Database Mail you must create database mail account and profile.Following are the steps to configure Database Mail Account And Profile. Once Account and Profile has been created you must configure database mail using sp_configure stored procedure exec sp_configure 'show advanced options',1Go Reconfigurego sp_configure 'Database Mail XPs',1go Reconfigurego Now the database Anonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-36311831452216279692011-10-26T21:33:00.001-07:002011-10-27T10:27:11.270-07:00Create User In All The SQL ServersI have found many people questioning on MSDN forum “How to run a query or create a user in all sql server instances” and the answer is using Central Management Server (Registered Servers). see following example of creating a user in all the sql server instances. I have following three instances of SQL where we need to create a user using registered servers. Goto Menu : View—>RegisteredAnonymousnoreply@blogger.com0tag:blogger.com,1999:blog-5733902693446744864.post-17163038300556038942011-10-19T22:28:00.001-07:002011-10-20T00:27:11.627-07:00ISNULL VS COALESCE, Nested ISNULLThere is a myth bound to COALESCE function that it is just a replacement of ISNULL. Many candidate in interview also agree to this statement that COALESCE is just replacement of ISNULL but most of them were not aware of advantage of using COALESCE in place of ISNULL. So Lets See ISNULL VS COALESCE Test 1 : Similar Functionality Of ISNULL and COALESCE Use of ISNULL Declare @Id1 IntDeclare @Id2 Anonymousnoreply@blogger.com0