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
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
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
after running RESTORE FILELISTONLY I GOT 
.AND MY RESTORE COMMAND IS :
USE [master]
RESTORE DATABASE [MYDB]
FROM DISK = 'K:\Backups\MYDB_backup_20
WITH FILE = 1,
MOVE 'MYdb_dat' TO 'K:\MYDB20180109\MYDB.mdf'
MOVE 'MYDB_F2' TO 'K:\MYDB20180109\MYDB_F2.n
MOVE 'MYdb_log' TO 'K:\MYDB20180109\MYDB.ldf'
NOUNLOAD, REPLACE, RECOVERY, STATS = 5
GO
.AND MY RESTORE COMMAND IS :USE [master]
RESTORE DATABASE [MYDB]
FROM DISK = 'K:\Backups\MYDB_backup_20
WITH FILE = 1,
MOVE 'MYdb_dat' TO 'K:\MYDB20180109\MYDB.mdf'
MOVE 'MYDB_F2' TO 'K:\MYDB20180109\MYDB_F2.n
MOVE 'MYdb_log' TO 'K:\MYDB20180109\MYDB.ldf'
NOUNLOAD, REPLACE, RECOVERY, STATS = 5
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help ,i am going to make some room (2.8 TB )and run my restore command again .
You may try to restore file-by-file. And run a DBCC SHRINKFILE after each file is created (untested).
ASKER
how do i restore file by file ?
You don't, was a dumb idea.
Shrink the transaction log from the original database and them perform the backup and use this last backup for the restore operation.
So the error message says this!
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.
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.
What is the size of K:\ drive?
ASKER
thanks for your help .