May 12, 2008 at 1:09 pm
Hi all
unable to restore database
I have an test db, which needs to be restored with the latest backup, the problem is hard disk space, it was 43 gb free, but the latest bak file is 54gb, so in order to restore it i though of deleting the db and copying the latest bak file on it and restoring it.
Now the backup is there, and 58 Gb is free in the hard disk, But the prob is when im restoring is says
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'server1'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'D:\' to create the database. The database requires 76416024576 additional free bytes, while only 63008391168 bytes are available. (Microsoft.SqlServer.Smo)
i dont know whats the problem, i have 58 gb free space and 54 bak file then why its not restoring.
Pls let me know asap.
thanks
May 12, 2008 at 1:14 pm
The backup file size is relevant only to the amount of data stored, not the amount of storage allocated to the databse.
You can have a 10MB backup file that restores to a 100MB database if the database has 100MB of space allocated when the backup was taken. The error is telling you that you need 76(ish)GB of free storage for the restore operation.
Your friendly High-Tech Janitor... 🙂
May 12, 2008 at 1:22 pm
so what do i need to do, i dont have that much free space available.
May 12, 2008 at 1:47 pm
Unfortunatly, there isn't anything to do other than find/make the freespace.
If the original database used multiple files, you have the option of restoring them to different physical drives.
Your friendly High-Tech Janitor... 🙂
May 12, 2008 at 1:54 pm
can i delete the backup file on the local system and then restore it from the network drive, i know we can do this, but i dont know how to restore database from network,
May 12, 2008 at 2:00 pm
Yes, you can restore it from the network. You'll need to ensure a couple things first though:
1: SQLAgent on the dbserver is using a domain account.
2: The domain account that SQLAgent is using has access to the network share/location.
Your friendly High-Tech Janitor... 🙂
May 12, 2008 at 6:59 pm
The below command might work and will overwrite the existing database, ensure that Sql service account has permissions to that network drive. It might take some time and resource intensive.
Restore database dbname from disk='\\Network path name\filename.bak' With Replace
[font="Verdana"]- Deepak[/font]
May 12, 2008 at 8:05 pm
Thanks a lot guys, I am now able to do it through network. I was giving the path from the address bar from my computer, But now when i went into that path from run, It was different for example,
This is what i was giving in the path name:
//server1/w:/backups/MSSQL/Database.bak
When i enter the below path from run(Command Prompt), I found out just a simple mistake which i was ignoring.
//server1/backups/MSSQL/Database.bak
just see the difference in the backup paths on both ways, I think this will help some one else who can safe their time and frustration.
though the process is a bit slow in restoring, but its ok.
Thanks all again.
May 12, 2008 at 9:24 pm
Nicole (5/12/2008)
Thanks a lot guys, I am now able to do it through network. I was giving the path from the address bar from my computer, But now when i went into that path from run, It was different for example,This is what i was giving in the path name:
//server1/w:/backups/MSSQL/Database.bak
If the domain account for SQLAgent is a local admin on server1, you could have done: \\server1\w$\backups\MSSQL\Database.bak The $ denotes the default "Administrative" share for a drive.
When i enter the below path from run(Command Prompt), I found out just a simple mistake which i was ignoring.
//server1/backups/MSSQL/Database.bak
That's the proper way to use a UNC path (not as above using the w$, which requires local admin rights).
though the process is a bit slow in restoring, but its ok.
Thanks all again.
It can be a bit slow sometimes over the network. But at least you'll be able to get the database restored, and then run some shrink operations on it to get it back down to a manageable size. Sounds like there's some space to be reclaimed (if you're not going to be needing it).
You're quite welcome. 🙂
Your friendly High-Tech Janitor... 🙂
May 12, 2008 at 9:36 pm
Gordon , I really appreciate the way you have helped Nicole and also us.
"More Green More Oxygen !! Plant a tree today"
August 2, 2010 at 7:41 pm
Hi
I got the similar problem yesterday and checking the solution here and there. Below is the steps I followed to restore the database
1. Map the Drive Letter to this location from where you are going to restore the file
2. Stop MSSQL service from Services on relevant server
3. Run command prompt under your account by right clicking on the Command Prompt and select user name and password to this domain
4. Run MSSQL in command prompt mode as follow - this line is from SQL Server(MSSQLSERVER) PROPERTIES --> path to executable
"C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER
5. Once above service is running - Restart Ent. Manager and do the restore - you can give the Mapped Drive letter for path and give the name for file.
6. Verify the .mdf and .ldf file location for this database from Options and click OK - data base will be restored.
Many Thanks
Deepak
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply