You can attach a database file to an instance of SQL Server by using the SQL Server Management Studio tool. For example, if you have existing database files from an old installation of SQL Server, you can attach these files to a new installation or another instance of SQL Server. Likewise, if you want to restore a corrupt database from a backed-up copy, you can attach the backed-up database file.This topic describes how to attach a database in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. You can use this feature to copy, move, or upgrade a SQL Server database.
1. Right-click Databases and click Attach.
3. Sometimes attaching a database file may throw exceptions like "Access is Denied". To overcome this issue open the windows explorer and go to the file location. Right click on the file and select Properties option. Then go to the Security tab and enable Full Control option for the User Account.
4. Now the database file will be attached with the SQL Server. If the database is not shown on the databases list, refresh the database list and it will be shown on the list.
Prerequisites
- When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.
Steps
In SQL Server Management Studio Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
1. Right-click Databases and click Attach.
2. In the Attach Databases dialog box, to specify the database to be attached, click Add; and in the Locate Database Files dialog box, select the disk drive where the database resides and expand the directory tree to find and select the .mdf file of the database.
No comments:
Post a Comment