Backup and Restore of SQL Server Databases and Maintenance plans
The SQL Server backup and restore component provides an essential safeguard for protecting your business critical data in the databases. The below Videos will help to understand the backing up SQL Server databases, backup and restore strategies.

An Index in a database lets you quickly find specific information in a table or indexed view and you can significantly improve the performance of applications and database queries by well-designed indexes to support your queries.

Visit below links to learn about different types of Indexes in SQLServer.

Monday, February 25, 2013

You should always have proper backup plan in place to protect your database from failures. SQLServer have four types of backups

1. Full Database backup – Complete database backup at the time of backup.
2. Transaction Log backup – Backups up the transaction log file
3. Differential backup– Backs up the parts of the database that is changed since the last Full Database backup
4. File and file group backup – Backs up the database files that you specify in the FILE or FILEGROUP option.
In SQLServer either you can mention the backup path or you can create a backup device use the device name during the backup. You can either use T-SQL or SQLServer Management Studio to backup SQLServer Database.

Full Database backup: You can backup whole database, this includes part of transaction log which is needed to recover the database using full backup.

Transaction Log backup: You must backup the transaction log, if SQLServer database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full. Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.

BACKUP LOG DB_Name
TO Backup_Device
GO

or

BACKUP LOG DB_Name
TO DISK = ‘D:\SQLBackup\LogBackup\DB_Name.trn’
GO

If transaction log is full users will receive error “Log files are running out of space”

Differential backup:- The database must have full back up in order to take a differential backup, it only backups the changes since last full backup.

File and file group backup:- By default each database has PRIMARY file group which is tied to one data file. You can create additional filegroups and add data files to filegroup. You can perform both FILE and FILEGROUP backups.

Wednesday, February 20, 2013

When trying to create user with login or edit user mapping…etc, you may receive below error message.Error 15023: User, Group or Role already exists in the current database
You may see below error when you are dealing with group

Error 15024: The group already exists in the current database
Users will receive this message when user existed without a SQLServer login in the database.

If the user does NOT exist, then you can run below command using “auto_fix” attribute to create the user in the database.

You will see the output with following resultThe row for user will be fixed by updating its login link to a login already in existence.The number of orphaned users fixed by updating users was 1.The number of orphaned users fixed by adding new logins and then updating users was 0.

If the user is ALREADY exists and if you want to map login with the user run below command using “updated_one” attribute.

You can also use below Stored Procedure to fix all the Orphan users in database by mapping them to username which is already exist on server. Make sure you test the script in a testing environment first.

About Me

I have been working with Database technologies for over 16 years, specialized in High Availability Solutions such as Oracle RAC, Data Guard, Grid Control, SQLServer Cluster, SAPHANA. I have experience on wide range of products such as MySQL, Oracle Essbase, Agile, SAP Basis, SharePoint, Linux and Business Apps admin. I have implemented many business critical systems for fortune 500, 1000 companies.