Avatar of MD SHAMIM
MD SHAMIM asked on

SQL Database restore error

hello experts ,

currently i am trying to migrate my database(SQL server 2008R2 standard edition ) from test environment to dev(SQL server 2014 standard edition) .my BD size is 535 GB and my disk size is 2.49 TB ,every time i am trying to restore my DB getting error :

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

why i am getting this error since i have enough space on my drive .i am not trying to restore in C drive . how could i solve this ? thanks
DatabasesSQL

Avatar of undefined
Last Comment
MD SHAMIM

8/22/2022 - Mon
ste5an

A backup contains only the data, no fill space as for example required by the fill factor settings. It also maybe compressed. Use RESTORE FILELISTONLY to see how much space you really need.
ASKER
MD SHAMIM

after running RESTORE FILELISTONLY I GOT restore.png .AND MY RESTORE COMMAND IS :

USE [master]
 RESTORE DATABASE [MYDB]
 FROM DISK = 'K:\Backups\MYDB_backup_20180103.bak'
 WITH FILE = 1,

MOVE 'MYdb_dat' TO 'K:\MYDB20180109\MYDB.mdf',
 MOVE 'MYDB_F2' TO 'K:\MYDB20180109\MYDB_F2.ndf',
 MOVE 'MYdb_log' TO 'K:\MYDB20180109\MYDB.ldf',
 NOUNLOAD, REPLACE, RECOVERY, STATS = 5

GO
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
MD SHAMIM

thanks for your help ,i am going to make some room (2.8 TB )and run my restore command again .
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ste5an

You may try to restore file-by-file. And run a DBCC SHRINKFILE after each file is created (untested).
ASKER
MD SHAMIM

how do i restore file by file ?
ste5an

You don't, was a dumb idea.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

Shrink the transaction log from the original database and them perform the backup and use this last backup for the restore operation.
Gerald Connolly

So the error message says this!
The database requires 3106808856576 additional free bytes, while only 2670337888256 bytes are available

so the restore wants 3.1TB (2.83TiB), but only 2.67TB (2.42TiB) available

How ever you look at it, you dont have enough free space available!
I would suggest you need at least 5TB available so you have some headroom.
Bharat Bhushan

What is the size of K:\ drive?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
MD SHAMIM

thanks for your help .