Relational Database Systems

How to move files when database is in restoring/loading state in SQL Server 2008?

Scenario:

Database is in restoring state and we have more backup files to restore before database can be brought online but there is not enough space available for further restore. We can not start restore process all over because Full backup restore takes more than 12 hours and we are in middle of upgrade (SQL Server 2000 to SQL Server 2008). Server is scheduled to go live in next 4 hours. Reported Error Message are:

Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'K:\ ' to create the database. The database requires 419430400 additional free bytes, while only 0 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

Resolution

Let’s take an example, we are restoring database named SQLVillage that has three files Data, Index and Log. We ran out of space on disk where we have placed Index file.

1st option, you may think of moving file to different location using move command in restore command but unfortunately that will not work because File cannot be moved when database is restoring/loading state. Let’s look at error message that you will receive if you attempt:

restore database SQLVillage from disk='I:\SQLVillage_4.dif'
with
move 'SQLVillage_Data' to 'K:\SQLVillage.mdf'
,move 'SQLVillage_Index' to 'I:\ SQLVillage2.ndf'
,move 'SQLVillage_Log' to 'K:\ SQLVillage_log.ldf'
,norecovery,stats=5

Output of above command will be:

Msg 3174, Level 16, State 1, Line 1

The file 'SQLVillage_Index' cannot be moved by this RESTORE operation.

Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

 

2nd option, you may ask Storage team to expand drive but that is also not feasible because Storage team needs to concatenate LUN that will wipe-out all data.

3rd option, restore full backup but looking at time constraint full backup restore option is not feasible.

So let’s see step by step how to move file to different location and use new location in restore operation in this situation.

Step 1

As you know, SQL Server 2008 provides a special diagnostic connection for administrators known as DAC (Dedicated Administrator Connection). We can resolve issue using DAC. So first start SQL Server in single user mode using following command thru DOS:

sqlservr – m

Step 2

Now Open another DOS window and Connect SQL Server using DAC as shown below:

Now you need to perform all operation stated below in DAC window

Step 3

Update system catalog to allow you update:

Step 4

Now run following command to ensure you are updating property of database that needs to be modified in order to perform restore successfully:

Step 5

Update sysaltfiles so that you can move file to location where you have enough space to continue restore:

Above command should return you following message:

Warning: System table ID 22 has been updated directly in database ID 1 and cache coherence may not have been maintained. SQL Server should be restarted.

Step 6

Now Stop SQL Server Services:

Step 7

Move files manually to location that you specified in above steps. In my case, new location is I: drive.

Step 8:

Once file move is successfully completed then restart SQL Server services in normal mode.

Step 9:

You should be now good to restore file using new file location.

 Summary

Data or Log files can be moved to different location even when database is in restoring state but that is not ideally recommended. Hence, plan your disk drives extra enough before you move ahead with restoring any database.

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top