Dear Readers,
You can check out IntelliSense Article on MSSQLTips.com.
Click Me to read…
Thanks,
Jugal Shah
Enabling IntelliSense and Refreshing IntelliSense Data in SSMS 2008
1 Reply
Category Archives: DB Articles
Central Management Server
Central Management Server
SQL Server Central Management Server is just a central repository that holds a list of managed servers. Microsoft has introduced CMS feature in SQL Server 2008 SSMS.
Use of Central Management Server
1. Multiple Server Query Execution, we can execute query against multiple servers and get the result at source.
2.Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort.
3.Evaluate policy against the multiple server from single source.
4.Control Services and bring up SQL Server Configuration Manager
5.Import and export the registered servers:
Pre-requisite:
You must have at least 1 SQL Server 2008 instance which can be used as CMS
Steps to Create CMS and Register Server
1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.
2. Right click on the Central Management Servers and select “Register Central Management Server” and Register the SQL Server 2008 instance as CMS.
3. Create the groups under Registered CMS servers to define the group for each server.
4. Right Click on groups and register the all the SQL Server instances as per their group. (SQL Server 2000,2005 and 2008)
How to Create Alias in SQL Server?
What is Alias?
A SQL Server alias is the user friendly name. For example if there are many application databases hosted on same SQL Server. You can give the different alias name for each application. Simply Alias is an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.
You can create Alias from Configuration Manager.
Go to SQL Server Configuration manager – Go to SQL Native Client -> Right Click and SELECT New Alias from the popup window.
Alias Name — Alernative name of SQL Server
Port No — Specify the Port No
Server – Mentioned the Server Name or IP address
Make sure On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.
Backup Start Date Time and Finish Date Time
As best practice it is recommended that you have to backup date time with the backup file name so anyone can get the idea of Backup creation.
Sometimes due some issue we took backup without specifying the datetime with the backup file name so during restore we are unsure that how much data backed up in the backup file, type of backup, Is it Copy only and more.
SQL Server stores the Backup Metadata into backup header. You can restore header only command to get the required information.
RESTORE headeronly FROM disk = ‘c:\jshah.bak’
| Column Name | Values | Description |
| BackupName | NULL | |
| BackupDescription | NULL | |
| BackupType | 1 | Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial |
| ExpirationDate | NULL | |
| Compressed | 0 | 0 = Un-Compressed Backup 1 = Compressed Backup |
| Position | 1 | |
| DeviceType | 2 | |
| UserName | JShah | |
| ServerName | SQLDBPool | |
| DatabaseName | jshah | |
| DatabaseVersion | 655 | |
| DatabaseCreationDate | 12/31/10 9:55 AM | |
| BackupSize | 1453056 | |
| FirstLSN | 28000000006000100 | |
| LastLSN | 28000000013000000 | |
| CheckpointLSN | 28000000006000100 | |
| DatabaseBackupLSN | 0 | |
| BackupStartDate | 12/31/10 10:06 AM | |
| BackupFinishDate | 12/31/10 10:06 AM | |
| SortOrder | 52 | |
| CodePage | 0 | |
| UnicodeLocaleId | 1033 | |
| UnicodeComparisonStyle | 196609 | |
| CompatibilityLevel | 100 | |
| SoftwareVendorId | 4608 | |
| SoftwareVersionMajor | 10 | |
| SoftwareVersionMinor | 0 | |
| SoftwareVersionBuild | 2757 | |
| MachineName | SQLDBPool | |
| Flags | 512 | 1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY. |
| BindingID | 85A5505D-ADB1-4B33-A181-549DC520A0F8 | |
| RecoveryForkID | 03DE5437-1E27-4885-9011-91CFED12338A | |
| Collation | SQL_Latin1_General_CP1_CI_AS | |
| FamilyGUID | 03DE5437-1E27-4885-9011-91CFED12338A | |
| HasBulkLoggedData | 0 | 1 = Yes 0 = No |
| IsSnapshot | 0 | 1 = Yes 0 = No |
| IsReadOnly | 0 | 1 = Yes 0 = No |
| IsSingleUser | 0 | 1 = Yes 0 = No |
| HasBackupChecksums | 0 | 1 = Yes 0 = No |
| IsDamaged | 0 | 1 = Yes 0 = No |
| BeginsLogChain | 0 | 1 = Yes 0 = No |
| HasIncompleteMetaData | 0 | 1 = Yes 0 = No |
| IsForceOffline | 0 | 1 = Yes 0 = No |
| IsCopyOnly | 0 | 1 = Yes 0 = No |
| FirstRecoveryForkID | 03DE5437-1E27-4885-9011-91CFED12338A | |
| ForkPointLSN | NULL | |
| RecoveryModel | FULL | |
| DifferentialBaseLSN | NULL | |
| DifferentialBaseGUID | NULL | |
| BackupTypeDescription | Database | |
| BackupSetGUID | 62EB4399-C119-42C2-91F1-BF0FF19CB896 | |
| CompressedBackupSize | 1453056 |
How to check database ISOLATION Level?
You can use below query to check the database isolation level.
DBCC UserOptions
aiDeeva.com 