Moving the SAP DATA FILES from existed to newly added drive

If your SAP database or log files are running out of disk space, you have to add new disks to the file system.


Security Permissions :

Your Windows user account must be a member of the db_owner fixed server role in the SQL Server instance to detach DQS databases.

Your Windows user account must have CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission to attach a database.

You must have the dqs_administrator role on the DQS_MAIN database to terminate any running activities or stop any running processes in DQS.

If an existing device cannot be extended, you have to add an entire new device to your system.

In this case, you first have to configure the new device as an NTFS partition. Then you have to move existing SAP database files have to the new device by detaching from and re-attaching the database to the SQL Server.

Case study – I have taken an instance SM1.

Procedure –

1. Shut down the instance.

2. Detach the Database.

3. Copy the Data files from Existed drive.

4. Paste the copied Data files to newly added drive.

5. Attach the Database.

6. Start the instance.

1.JPG                                      

  • Instance has shut down.
  1. In the SQL Server Management Studio right-click the SAPSM1 database and choose Tasks –>Detach, The Detach Database dialog box appears.

2.JPG

Select the “Drop connections” check box, Choose OK to detach the database.

3.JPG

The SAP database is detached from the SQL Server and is no longer visible in the SQL Server Management Studio.

 
Copy the required SM1 Data files from Existing device,

4.JPG

Paste the SM1 Data files to newly added drive

5.JPG

In the SQL Server Management Studio, expand the server, right-click Databases and choose Attach. The Attach Database dialog box appears.

6.JPG

Specify the mdf file of the SAPSM1 database by choosing the Add button.

All the files belonging to the SAPSM1 database are listed under Original File Name.

7.JPG

We have to change the path of which one need to change

Now where we have located, it’s assigned that path only. Choose ok to attach the Data base.

8.JPG

Now able to view the SAP Instance in SQL server Management studio, and is again visible and accessible for the SQL Server. The SQL Server can now use the newly added drive.

9.JPG

 

Started the SAP instance, and Instance is up on running.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Matt Fraser

    Hi Srini,

    Nice blog. SAP has a Note (363018) which describes essentially the same procedure, except using Transact-SQL query commands instead of the Management Studio, and on a much older version of SQL Server, but I have always preferred using the Management Studio myself for this kind of operation, unless it needs to be scripted for some reason. Still, it’s easy enough to do either way.

    I would add to it that if the hard drives containing the database are on a SAN, then it’s often much easier to have the SAN administrator simply expand the volumes. Modern SANs usually have a mechanism for doing this while the volumes remain online, so there is no downtime. Then you can simply increase the size of the data files in place, again with no downtime (though there is a minor performance hit, so best to do this off-hours if possible).

    Cheers,

    Matt

    (0) 

Leave a Reply