Sunday, June 29, 2014

Is Your SQL Server Database Need to Repair?


Run below command to check whether your SQL server database needs to be repaired or not

DBCC CHECKDB (Database-Name) with no_infomsgs

  • DBCC CHECKDB: It checks all the logical & physical integrity on the specified SQL server database.
  • Database-Name: Just replace the 'Database-Name' with your database name for which to run DBCC CHECKDB integrity. If you not specified the database name then current database will be used.
  • No-Infomsgs: It will remove all the information messages

If above command executed without any error message then there is no need to repair SQL server database but if there is error message(s) then you need to repair your SQL server database.

Note: If error message appears with DBCC updateusage then you need to run DBCC UPDATEUSAGE command on the database.

DBCC UPDATEUSAGE: It detects and corrects all the table related problems like rows, data page count, reserved page, used page etc. in specified database. Run below command to resolve table related problem in the database.

DBCC UPDATEUSAGE ({database_name} {table_name}) with no_infomsgs

If appears error message does not contain dbcc updateusage then you need to repair your SQL server database.

Why Need to Repair?
You can see the reasons from the Windows system & application event logs. SQL server logged possible cause for the problem in the windows system logs.

Suggestion: I will recommend you to check your database backup before repairing the corrupt SQL server database. If Backup is clean & updated then restore it.

How to Repair?
If backup is not available then you need to repair your SQL database. SQL server provides following repair options:
  • Repair_Rebuild: SQL server suggests it when there is no any possibility of data loss from the specified database.
  • Repair_Fast: SQL server suggests it when no repair is performed
  • Repair_Allow_Data_Loss: It will try to fix all the error messages with possibility of some data loss.
To perform any of the repair options, you need to put your database in single user_mode:

ALTER Database <database-name> SET Single_User

Here is the syntax of all repair options:

DBCC CheckDB ([YourDatabase], REPAIR_REBUILD)
DBCC CheckDB ([YourDatabase], REPAIR_FAST)
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

After repairing your database, don't forget to set your database in multiple user_mode. You can do this by running following command:

ALTER DATABASE [YourDatabase] SET MULTI_USER

What to Do after Repairing the Database?
After repairing your corrupt SQL server database, you need to do following things:

  • Re-Run DBCC CHECKDB: It is very important to re-run DBCC CHECKDB command on the database after repairing. If it comes out without any error message then it is conform that there is no any inconsistency in the database.
  • Make Full Backup of Database: If DBCC CHECKDB comes out cleanly then take a full backup of your database and keep it at safe place. Backup will help you in restoring of database.
  • Test Your Backup: Verify your backup by restoring it on another server. Successful restorations can only guarantee that your backup is consistence.
  • Make Disaster Recovery Plan for Future: Make a proper disaster recovery plan for future.
»»  Read More...

Wednesday, June 18, 2014

Save $549 on SQL Toolkit bundle from Stellar Phoenix


Stellar Phoenix, a pioneer in data recovery software & services now offering SQL toolkit bundle for all SQL server DBAs. SQL toolkit bundle has three essential tools that help you in recovery of corrupt SQL database, backup file and forgotten password.

https://secure.element5.com/esales/checkout.html?PRODUCT[300624711]=1&COUPON1=SQLTULK-EN&HADD[300624711][ADDITIONAL1]=Stellarinfo.com

Tools in SQL Toolkit Bundle:

Stellar Phoenix SQL database repair tool repairs corrupt or damaged SQL server database files (.mdf & .ndf). Software supports SQL server 2012, 2008 R2, 2008, 2005, & 2000.

Stellar Phoenix SQL Backup Recovery tool safely restores corrupt or damaged SQL database backup file into a new database.

Easily recovers lost or forgotten password of SQL server system administrator (SA) and all users.


You save 50% on the purchasing of all three essential tool individually.
So don't wait, buy & Save $549
»»  Read More...

Tuesday, June 17, 2014

Is Full Backup is Sufficient to Recover SQL Server Database?

Data is very important for everyone, ranging from individual to big organizations. For me, data is my life. I can’t think, what will be my life without data. So I regularly manage and update data. If you think that your data is also important for you as of mine then you should also manage and update your data.

What is Data?
Data can be anything like list of records, phonebook, measurements, survey records, employee records or even a description of any things. In the other words, you can say that data is a set of records.

How to Manage your Data?
Creating copy or copies of your data is the primary method for data management. Just create copy of your important data and store it on external hard drive, pen drive, or any other removable media so that you can recover any lost or deleted data from backup copy. Fortunately, Microsoft SQL server provides backup & restore facilities for data management.

SQL Server Backup
Backing up of your SQL server database is very important for protecting your database from data loss. SQL server facilitates for backing up transaction log, whole database, partial database or sets of files & filegroups. For these, SQL server offers following backup techniques:
  • Tranaction Log Backup: It records all the changes performed in the database that were not available in the previous log backup. To create a transaction log backup, you need to create at least one full backup of your database before. I recommend you to take a transaction log backup of your database frequently that will minimize data loss exposure and truncate the old transaction log.
  • Full Backup: It is also known as database backup because it backs up whole database including transaction log. It can be performed on the basis of your business need. Full backup contains the data at the time of full backup finished.
  • Differential Backup: It is faster in comparison of full backup because it contains the data since last full backup.
  • File Backup: It backs up one or more SQL server database files or filegroups. You can backup whole filegroup instead of individual files.

Data Loss, it can happen to anyone
You should always prepare of it. It can happen with anyone at anytime. So it is very important of everybody to prepare of it and make a suitable disaster recovery plan to avoid data loss and minimize the business downtime when data loss event will occur.

So now you may need to make a full backup of your SQL server database. You can create full backup of your SQL server database by using SQL server management studio, transact-SQL, or powershell.

Is Full Backup is Sufficient Enough?
If you have a read-only SQL server database or data in your SQL server database is very static then full backup is sufficient for you. Possibly you may recover your whole database with the help of full database backup.  But think differently, your database experiences frequent modification regularly then full backup is not sufficient for you because it does not contain data after full backup. To recover every bit of your data, you need to create other backups such as differential or transaction log backup. I have already discussed about these backups in the above paragraphs.

What is the role of recovery model in transaction log backup?
SQL server facilitates database administrators to manage their transaction logs through recovery model. There are three types of recovery model in SQL server: Simple, Full & Bulk-Logged. A database administrator can take backup of transaction log when his/her database in Full or Bulk Logged recovery models but this is not possible in simple recovery model.

Points to Ponder:
  • Make backup of database
  • Data is key to success
  • Regularly update database backup
  • Different types of backup required to recover database
  • Simple recovery model does not allow for transaction log backup
I hope that you have enjoyed to reading my article. As always, share your thoughts through comment.
»»  Read More...

Wednesday, June 11, 2014

7 Tips to Improve SharePoint Server Performance


Hi friends, today I am going to share one of my favorite topics in SharePoint that is performance improvement. Microsoft SharePoint offers good performance even during backup & restore operation is running but I will share some tips that will make your SharePoint server better than before. Backup & Restore operation of SharePoint use SharePoint server resources and slowdown the server performance while operations are running. Follow below tips to minimize the server resource usages and improve the server performance.



Tip 1: Perform Backup on Local Disk

To improve the SharePoint performance, it is very important to make a backup of SharePoint database on a local disk on the server instead on server network drive. After making the backup on local drive, you can easily copy the database backup on network drive. If you don't have local disk on the server then it is recommended to use a network drive with same latency as of server.

Tip 2: Maintain Small Databases

Small databases help in faster backup & restore. If you have a big database or planning for it then it is recommended to split it into small databases that will help you in faster recovery. For practice, use multiple small databases for a website application instead of having a big database.

Tip 3: Use Database Backup Compression Technique during Backup

Firstly, it was introduced with SQL server 2008. Compressed database backup increases the backup speed and significantly requires less to restore because it is smaller than uncompressed database backup. It helps in faster backup & restore of SharePoint database.

Tip 4: Prefer Incremental Backup for Large Database

Incremental backup back ups data since last backup only. Last backup can be either full backup or differential backup. There is a small difference between differential & incremental backup, differential backup back ups data since last full backup while incremental backup back ups data since last backup. Incremental backup is fast and small in comparison of differential backup because it back ups data since last backup. It helps in improving the database performance as well as minimizes the changes of data loss.

Tip 5: Choose Backup Type Wisely

SharePoint uses SQL server to store database and SQL server backup uses transaction log, differential & full to minimize the recovery time of database. If you are using simple recovery model of your database then there is no need to truncate the transaction log files because SQL server does not maintain transaction log if database is simple recovery model. But if you are using full recovery model for your database, then I will recommend you to truncate the transaction log file periodically to avoid the database maintenance problems.

Tip 6: Avoid Conflicting Issue

To avoid conflicting issue in the database, it is recommended to don't run backup operation during peak hour. Typically SharePoint server runs 27*7, so it is best practice to run incremental backup to safeguard data against server failure.

Tip 7: If Possible Try to Use RAID 10

RAID stands for redundant array of independent disks. It combines multiple disk drives component into a single unit for performance improvement. If you are using RAID on the SharePoint server then I will recommend you to use RAID 10 because it reads & writes data faster in comparison of other RAID levels.  
»»  Read More...

Thursday, May 22, 2014

Fix SQL Server Login Issue: Login failed for user 'Username'


In this article I will try to list all the steps which require to follow when you receive “SQL server error message 4064 - Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064”



Possible Cause: Above error message appears due to several reasons like database is dropped that is default database for some of the users, desired database may be corrupt, database is office, database may be renamed etc. When any cause is true then you will receive above error message.

Default Database: Before going to fix above error message, lets take a look at the default database. In SQL server management studio, whenever you connect to any SQL server database then by-default database is 'MASTER database'. It is selected by default and forces you to change from database drop-down list. It is very annoying situation. If you want to change your default database then follow below steps:
  • Open SQL server management studio (SSMS)
  • Point to the object explorer –> Security –> Logins
  • Now right on the Logins and select properties
  • A properties window will be appear
  • Change your default database from MASTER to desired database

Note: You can also change the default database by running below stored procedure:

 Exec sp_defaultdb@loginame='TestUser', @defdb='Test'     

Resolution/Fix: This error message does not appears frequently, but if you have received then here is a simple fix for it. You can connect your MASTER database and then change default database. To do this you need to follow below steps:

  • Open SQL server management studio
  • Now point to the 'File' button at top-left and click on it
  • Go-to connect object explorer



  • Now type the user-name & password and click on the 'options>>' button as shown in above image.
  • From connection properties tab, change the connect-to-database to master or any existing database name on your server. As I have discussed earlier, MASTER database is the default database by default.
  • After setting the database name to connect-to-database, click on the 'Connect' button.
  • You will successfully login in your system.
  • Now run this statement through your username.

ALTER LOGIN [username] with Default_Database = yourdatabasename

  • Error message resolved.

»»  Read More...

Thursday, May 15, 2014

Top 3 methods to Repair MySQL Tables

Corruption in MySQL database table can happen due to any uncertain reasons, and some of the top reasons are bugs in MySQL itself, virus attack, power failure, hardware problem, OS problem etc. If this happens with you then to repair MySQL table is the only solution you can try. Note that MySQL table repair may cause of valuable data loss because of limited functionalities of in-build MySQL repair utility or table has been badly corrupted. So make a proper backup of your MySQL database before trying any repair method.

When you have verified that your MySQL table gets corrupt, use one of the following methods to repair it.
  1. Repair MySQL Table using MySQLCheck
  2. Repair MySQL Table using phpMyAdmin
  3. Repair MySQL Table using 3rd party Software
Repair MySQL Table using MySQLCheck: It is a table maintenance program that checks, analyzes, repairs & optimizes MySQL tables. MySQLCheck is similar to myisamchk, but myisamchk must be run when MySQL server is running while MySQLCheck must be run when MySQL server is not running.

Check MySQL Table
# mysqlcheck -c database-name table-name -u root -p
Enter password:
database-name.table-name OK

Repair MySQL Table
# mysqlcheck -r database-name table-name -u root -p
Enter password:
database-name.table-name OK

Repair MySQL Table using phpMyAdmin: It is the easiest method to fix corrupt MySQL table. Follow below steps to repair corrupt MySQL table:

  • Open your phpMyAdmin client, enter the username & password then click on 'Go' button as shown in the image.



  • Select your MySQL database from left drop-down menu and click on the desired database as shown in the image.


  • Now you will see all of the tables of your selected MySQL database. In below image, there are 11 tables for the selected database.


  • Tick-mark on the check-boxes of tables that you want to repair. For example, I have selected 'wp_options' table as shown in the image.


  • Now click on the drop-down arrow of 'with selected' option and click on 'repair table'. Take a reference from below image.


  • After that you will get a conformation message saying that “your sql-query has been executed successfully”.
Repair MySQL Table using 3rd Party Software: There is platy of 3rd party MySQL repair utility available online that claims to repair & recover data from corrupt MySQL table. But I generally recommend Stellar Phoenix Database Repair for MySQL because it is trusted software to repair corrupt MySQL tables. It uses advanced algorithm to scan & repair corrupt tables. Software does not change anything of your system because it is read-only in nature. Software repairs myisam & innodb storage engine's table and supports MySQL 6.x, 5.x, 4.x, and 3.x versions. If you know & believe any other software that can do your job they go with that. Choose the right software that will make you happy.

Your Turn: I love to hear from you guys! Share your opinion through comment.
»»  Read More...

Thursday, February 27, 2014

Top Tips to Improve DB2 Performance

Along with various other issues, the performance issue is one of the major concerns for DB2 users. If you are a Database Administrator (DBA), then you have to look at this problem more carefully and take all the measures to rectify it. That means being DBA; you have to look for all the possible areas where there are chances of improvements. Therefore, in order to help you out, we are providing you some tips to get back the performance of your DB2:

  • Turn-on Monitor Switches: It is suggested to turn on the monitor switches. If they are not, then you will not have the information regarding performance that you require. Issue the following command to turn-on the monitor switches:

db2 “update monitor switches using   
lock ON sort ON bufferpool ON uow ON   
table ON statement ON”
  • Ensure You Have Sufficient DB2 Agents: Make sure that you have sufficient DB2 agents for handling your workload. Issue the following command to find the number of agents:

db2 “get snapshot for database manager”


After that look for the following lines:

High water mark for agents registered = 7  
High water mark for agents waiting for a token = 0  
Agents registered= 7  
Agents waiting for a token= 0  
Idle agents= 5  
Agents assigned from pool= 158  
Agents created from empty Pool = 7  
Agents stolen from another application= 0  
High water mark for coordinating agents= 7  
Max agents overflow= 0


In these commands, if you find that your agents are waiting or stolen from the application like Agents waiting for token or Agents stolen from another application, then increasing the number of agents available for database manager is recommended.
  • Watch out Maximum Open Files: The DB2 is designed in a way that it can manage multiple open files at a time. With MAXFILOP command, you can get to know about the upper limit of opened files that DB2 can handle concurrently. However, when this maximum limit is reached, DB2 starts closing and opening tablespace files. This slows its performance. Issue the following command to find out if DB2 is closing files or not: db2 “get snapshot for database on DBNAME”. After that, look for the line that reads: Database files closed = 0. If you find that your files are being closed, then increase the value of ‘MAXFILOP’ till this closing and opening stops. Issue the following command for the same:
db2 “update db cfg for DBNAME using MAXFILOP N”

By keeping these points in mind and checking them on a regular basis, you can improve the performance of DB2 and work more efficiently and quickly.
»»  Read More...

Thursday, January 23, 2014

Security Features of MS Access 2010

MS Access is one of the most useful applications of MS Office suite. MS Access is a database management system that is suited for home users and small businesses. MS Access 2010 is included with MS Office 2010 suite. Along with various other useful and efficient features, this application includes more reliable ‘Security Features’. The security features of MS Access 2010 ensure enhanced security of your database file. The enhancements to these features help you to apply them more easily than before. Mentioned below are different security features included in MS Access 2010:

  • The ‘Trust Center’: The ‘Trust Center’ is a common feature of all MS Office applications for managing and changing the security settings of Office application. The location of ‘Trust Center’ in MS Access 2010 is ‘File >> Trust Center >> Trust Center Settings’. Generally, it performs a set of security checks when you open your database file. The process it follows for checking your database is as follows:
When you initially open the .accdb or .accde file, then Access submits the location of the database to the ‘Trust Center’. If ‘Trust Center’ finds that the location is trusted, then this database file opens with all its functionalities. If you open a database that has an earlier file format, then too Access submits that location and digital signature to ‘Trust center’.

Note: The decision made by ‘Trust Center’ for opening an Access file entirely depends upon the settings that you or your system administrator has made in ‘Trust Center’.
 
  • Disabled Mode: When the ‘Trust Center’ finds a database unreliable, it opens this database in ‘Disabled Mode’. That means it turns off all its executable content irrespective of its file format. The contents that are disabled by MS Access in disabled mode are VBA code, action queries, Data Definition Language (DDL) queries, SQL pass through queries, ActiveX controls, and unsafe actions in all macros. However, when you disagree with the decision of ‘Trust Center’, you can use the ‘Message Bar’ to enable its contents. 
  • Encrypt the Database with Password: Access 2010 enables you to password-protect your database. The password ensures that any unauthorized computer user does not use the data. Moreover, the password-protected Access database file is unreadable by any other Access application as well until you provide the correct password to open it. You can also encrypt the content of your Access database in Access 2010 version.
With these features, MS Access 2010 provides more reliable and trusted working environment to its users.
 
»»  Read More...

Friday, August 23, 2013

All about RMAN backup


RMAN stands for Recovery Manager, is the backup and recovery solution of Oracle database. It is the preferred method for backup and recovery. The best part about RMAN is that it detects block corruption while performing backup. It can be accessible through command line or Oracle Enterprise Manager. Since RMAN is the feature of Oracle so no separate installation is required for it. It comes with some advanced features like parallelization of backup/restore; keep history of all backup and many more. You can backup following files through RMAN:



  1. Archive redo log file
  2. Data files
  3. Control files
  4. SPFILES.

Types of backup in RMAN:-
  1. Full backup: It is used to backup whole database block by block.
  2. Incremental backup: It can be of two types, either a 1 or 0 levels backup.
  • Level 1 backup: It backups only those blocks that have been changed since the last parent backup. Parent backup can be of 0 level or 1 level.
  • Level 0 backup: It is same as full backup. The only difference is that it does not backup empty blocks.
  1. Consistent backup: It is performed when database was shutdown using SHUTDOWN command.
  2. Inconsistent backup: It is performed when database is open or failed after crash.
  3. Open backup: It is performed when database is in online mode.
  4. Closed backup: It is performed when database is in offline mode.

How to perform backup with RMAN?
To backup whole database: Use following command to backup the whole database:
RMAN> BACKUP DATABASE;

RMAN new features in Oracle 11g
  1. Data Recovery Advisor  Data Recovery Advisor automatically detects corruption and data failures. It repairs block corruption, data dictionary corruptions and many more.
  2. Cloning of database: You can easily clone your database without requiring existing backups.
  3. Improvement in backup compression: In Oracle 11g, RMAN backup compression is done using ZLIB algorithm which is much faster than BZIP2 algorithm that is used in Oracle 10g.
  4. Check physical corruption: A new command is introduced in Oracle 11g to check database for physical corruption. The command is VALIDATE DATABASE.

Why use RMAN?
RMAN is the preferred method of backup and recovery. Since RMAN is supported by Oracle so while using it if you find any issue you can always rely on Oracle for support. Here are the advantages of using RMAN:
  1. RMAN automatically detects block corruption while performing backup. In RAMN you can recover only few corrupted block unlike traditional backups where you have to backup entire data file.
  2. RMAN also performs incremental backup. In incremental backup only those blocks are backed up that are changed since last backup. So it reduces recovery time because now you don't have to backup the entire database.
  3. It provides automatic backup of controlfile and spfile.
  4. You can also perform encrypted backup using RMAN which is not possible in traditional backups.
  5. Using RMAN you can easily check any backup that whether it is recoverable or not.
  6. In RMAN backup compression there is a feature by which you can skip unused blocks.
  7. RMAN can work fairly well with third party tools.
  8. RMAN backup is much faster than other traditional backups.
  9. RMAN has a highly advanced feature named Data Recovery Advisor that automatically detects and repair block corruption.
  10. RMAN automatically deletes expired or unnecessary backup of data file from disk.
  11. You can perform backup in online mode without putting the tablespace in hot backup mode.

RMAN backup best practices
If you are using RMAN as your backup strategy then follow best practices for performing backup. However RMAN is intelligent enough to automatically detect which files need to be restored and backup. Consider following factors while performing backup

  1. Choose the backup option depending upon your business requirement: RMAN provide many type of backups. Choose the one that can provide fastest recovery for your business.
  2. Check validity of backup: Perform a media check on your backup storage. A disk to disk backup is considered as most reliable and fastest backup.
  3. Check the speed of backup: Check how long does it take to perform backup.

RMAN backup optimization
There are two commands in RMAN backup optimization. Here they are:
RMAN> configure backup optimization ON;
RMAN> configure backup optimization OFF;

When backup optimization is ON then BACKUP command will skip the backup of that file which has already been backed up to a specific device. RMAN check for identical files; for example in case of datafile, it will check for checkpoint SCN, DBID, creation SCN to see if they are already backed up. By default backup optimization is OFF. Consider some of the key features of configure backup optimization ON command:
  1. If you want to backup flash recovery area using RMAN then you need to set the Configure backup optimization command to ON.
  2. If you set backup optimization to ON then RMAN will skip the backup of identical files.

Some of the useful commands of RMAN are:

RMAN> show all; (It shows all the RMAN parameters of the database.
RMAN> backup database; (To perform database backup)
RMAN> delete expire backup; (To remove archive log)
RMAN> report obsolete; (To delete obsolete backup)
RMAN> alter database; (To mount a database)
RMAN> delete; (To delete backup copy of database)
RMAN> exit; (To exit RMAN executable)
RMAN>restore; (To restore files from backup to an new location)
RMAN> duplicate; (To create a duplicate database of target backup for testing purpose)
RMAN>show; (To show the current configuration settings.) and list can go on....

»»  Read More...

Friday, July 26, 2013

How to move SQL server database from one location to another?


Do you want to change the location of your SQL server database? If yes then it is not a tough task! SQL server database files (data or transaction log) can be detached and reattached to the different instances of SQL server at the same machine or move to the another machine. Detaching & Reattaching is the best way to move the SQL server database from one location to another.

Steps to change the location: You will have to perform 3 tasks to change the location of your SQL server database from source computer to designation computer and the task are...
  1. Detach the SQL server database from source computer.
  2. Copy the database on the designation computer.
  3. Attach the SQL server database on the designation computer.
Detach a SQL server database: Detaching is a process to remove database from the SQL server instance without altering the data within data and transaction log file. These files are used to reattach the database to another SQL server instance at the same machine or instance at the different machine.

How to Detach a Database: SQL server database administrator can detach a database by two methods:
  1. Detach a database using stored procedure.
  2. Detach a database using SQL server management studio (SSMS).
Detach a database using Stored Procedure: Run the below syntax to detach a database using stored procedure (sp_detach_db).

sp_detach_db [ @dbname= ] 'database_name'
[ , [ @skipchecks= ] 'skipchecks' ]
[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

Output: If output is '0' then you have successfully detach the database from instance, if '1' then you will get an error message on your screen. In case of failure, try to run the syntax again.

Detach a database using SQL server management studio: Follow below steps to detach a database using SSMS:
  • Connect to the instance and then expand it.
  • Now expand the databases, and select your desired user database.
  • If database is in use currently then restrict it to single access.
  • Right click on the database->Tasks->Detach.
  • Detach dialog box will be open on your screen.
  • Select the database from the database name column to detach.
  • Verify that you are detaching the right database.
  • Click on the 'OK' button to start the detach process.
Note: Always perform detach operation on the database when database is not in use.

Limitations: A database administrator can't detach a database when any of the following point is true:
  1. Database is being mirrored in mirroring session.
  2. Detach database is a system database.
  3. Database is in suspect mode.
  4. Database is replicated.
How to attach a database: Database administrator can attach a previously detached database from the same machine or database files are location at another machine. There are 3 methods available to attach a SQL server database files.
  1. Attach a database using stored procedure
  2. Attach a database using 'Create Database' statement
  3. Attach a database using SQL server management studio
Attach a database using stored procedure: It is the easiest method to attach a database to the SQL server instance. Microsoft suggest that don't use this method to attach a production database using it because it will be remove in the future version of SQL server. To attach a database, run below stored procedure:

sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]

Where [ @dbname= ] 'dbname' is the name of database & [ @filename1= ] 'filename_n' is the physical file name.

Output: If output is '0' then you have successfully attach the database to an instance, if '1' then you will get an error message on your screen.

Attach a database using 'Create Database' statement: It is the recommended method for attaching a new or detached database to the SQL server instance. To attach a database using this method, run below T-SQL syntax:

To attach a database
CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG }
[;]

Attach a database using SSMS: Follow below steps to attach a database using SSMS.
  • Connect to the instance and then expand it.
  • Now right click on the databases, and select attach.

  • A attach dialog box will be open on your screen.
  • Click on the 'Add' button to specify the database.

  • Select the database from the disk drive for attach.
  • Verify that you are attaching the right database.
  • Click on the 'OK' button to start the attach process.
Note: To attach a database, all the data files (.mdf & .ndf) are necessary. If any of the data file is unavailable then you can't attach the database to SQL server instance.

Need to attach a database without Transaction log file: As I have mentioned above, for attaching a database all SQL server data file (.mdf & .ndf) are necessary but you can attach a database without transaction log file (.ldf). Follow below steps to test this.
  • Download a sample database (AdventureWork) from the codeplex website.
  • After downloading the sample database, you will have only sample data file not transaction log files.
  • Now attach this sample database using SSMS.
  • Connect to the instance and then expand it.
  • Now right click on the databases, and select attach.
  • A attach dialog box will be open on your screen.
  • Click on the 'Add' button to specify the database.
  • Select the database from the disk drive for attach.
  • Verify that you are attaching the right database.
  • To attach database without transaction log file (.ldf); select & remove transaction log file. Now click on 'OK' to attach.
  • SQL server automatically creates a transaction log file (.ldf) for you while attaching SQL server database.
Summary: Now you are able to detach & reattach the SQL server database. Additionally you can attach database without the transaction log file.
»»  Read More...