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
MD SHAMIMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
MD SHAMIMAuthor Commented:
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
0
ste5anSenior DeveloperCommented:
Yup, just take a closer look at the numbers...

Capture.PNG
seems like you need roughly 2,8 TB free space.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MD SHAMIMAuthor Commented:
thanks for your help ,i am going to make some room (2.8 TB )and run my restore command again .
0
ste5anSenior DeveloperCommented:
You may try to restore file-by-file. And run a DBCC SHRINKFILE after each file is created (untested).
0
MD SHAMIMAuthor Commented:
how do i restore file by file ?
0
ste5anSenior DeveloperCommented:
You don't, was a dumb idea.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shrink the transaction log from the original database and them perform the backup and use this last backup for the restore operation.
0
Gerald ConnollyCommented:
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.
0
Bharat BhushanSolution ManagerCommented:
What is the size of K:\ drive?
0
MD SHAMIMAuthor Commented:
thanks for your help .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.