Steps to copy .mdf and .ldf files of database to another location

If you try to copy .mdf  and .lfd file to new location, sql server not allow you to do so, it will through the error. For that you need to follow certain steps. Below are the steps which will tell you how to achieve this task.

STEP 1:  Alter Database to Single User.
To copy a log of database you need to Detach the log file from database so for that you need to make database Single User First.

Syntax:

ALTER DATABASE Database_Name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

STEP 2:  kill other session of database
If you switch database mode multiple to single you need to kill other session to use single mode database and do some operations.

Below is the query which is returns current session of the database

Syntax:

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'Database_Name'

Above query will return session id which are using same database. Below is the query to kill other session

Syntax:

kill SessionID;


Ex: kill 61


STEP 3: Detach Database .ldf and .mdf
Syntax:


EXEC MASTER.dbo.sp_detach_db @dbname = N'Database_Name'
GO

Now you can copy mdf and ldf file of database 

To create new database using moved mdf file, use below query:
Syntax:

CREATE DATABASE [DatabaseName] ON
( FILENAME = N'F:\loc2\DatabaseName.mdf' ),
( FILENAME = N'F:\loc2\DatabaseName_log.ldf' )
FOR ATTACH
GO

If you want to attached to the Existing database then follow following steps:

1.Right click to Databases(Root to the all databases)
2.Select Attach
3.Select mdf and ldf file location and click on to attached mdf file to existing database.


Comments

Post a Comment

Popular posts from this blog

Connecting Remote server with Local Server

Image
Introduction: Linked server is powerful functionality provided by SQL Server. We can create object of remote server on local server and use that object to perform operations on remote server. To explain this topic let's consider a scenario Scenario:   Local server having number of databases. We need to take backup of local databases and restored this database into the remote server. For this scenario we will follow certain steps: Step 1 : Create object of Remote Server Add remote server object in local server Syntax:     sp_addlinkedserver @server = N'LinkServer' ,     @srvproduct = N' ' ,     @provider = N'SQLNCLI' ,     @datasrc = N'Server Name ,     @catalog = N'Database Name' Here we have created object of remote server i.e. LinkServer . By using this object you can perform Select, Update functionality on the same. Step 2 : Grant Permission Next Step...
Read more

MDX Error handling tricks

Error List: Duplication of column value(column is not primary key): This issue occur due to NULL records. You have to handle NULL records for particular column. Below are the some properties which you need to set for the attribute: On dim property set: UnknownMember = Visible On attribute property:Key column Expand -> Null Processing-> handle in your way.
Read more

JSON_Automated_stored_procedure

Image
What is JSON: JSON stands for  J ava S cript  O bject  N otation, and is a way to store information in an organized, easy-to-access manner, "self-describing" and easy to understand. It gives us a human-readable collection of data that we can access in a really logical manner. Why we need JSON: JSON is a lightweight data-interchange format and it is language independent. Many sites are sharing data using JSON in addition to RSS feeds nowadays, and with good reason: JSON feeds can be loaded asynchronously much more easily than XML/RSS. Format of JSON string: JSON Example { "employees" :[     { "firstName" : "John" ,   "lastName" : "Doe" },     { "firstName" : "Anna" ,   "lastName" : "Smith" },     { "firstName" : "Peter" ,   "lastName" : "Jones" }             ] } You can compa...
Read more