.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Restoring DATA only from a backup set

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi, We have a backup and are trying to restore this. The original database is no longer available. When we try to restore this, we get this error: System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 84120371200 additional free bytes, while only 51382657024 bytes are available. (Microsoft.SqlServer.Express.Smo) Gave RESTORE   FILELISTONLY FROM DISK and checked the contents. The problem is with the Trans log. It is too huge and guess it was allowed to grow without any restrictions. Is there any way we can restore the backup without restoring the log? Please advice. Thanks in advance.

View Complete Post

More Related Resource Links

Backing Up & Restoring Data.

Hello, I'n new to the SQL Server Management Studio 2008, What i have done so far: I have opened my file Datebase Selected a subfolder inside the Database called "World00" I have backed up my World00 into my Hard Drive file called World00.bak But when i open my SQL Server Management Studio 2008, my World00 is "(Restoring) i seen some videos something about a Log that i also need to restore but where can i find that log? how do i make my file come back so i can go in it and use it? and also: http://img827.imageshack.us/img827/7172/problem2.jpg Can somebody please explain to me what do these options mean? Restore Options like (WITH REPLACE), (WITH KEEP_REPLICATION), Prompt and (WITH RESTRICTED_USER) Recovery State (RESTORE WITH RECOVERY), (RESTORE WITH NORECOVERY), (RESTORE WITH STANDBY) When i clicked on my World00 I selected (WITH REPLACE), and now i think i messed it up..is there a way to recover myfile back? so i can use it? because its still on "Restoring" please help! Also i did it all the way through like Manualy i didn't put any codes SQL's Query thing because i dont know how to use them if your going to give me a Query please tell me what to do, sorry i'm new to this coding ;o All i would like to know is how to backup ~ well i know this just right click on file select full backup and job done, but restoring is kinda confusing me because i

How to Recover accedently Deleted data without restoreing the backup ??

Hi I want to restore data which has been deleted accedently by user without restoring backup of T-Log.  

MSSQL 2008 R2 - database in restoring mode after backup - forever..

Hi folks,   i have this problem that occourred today with one instance of MSSQL 2008 R2 running on a WIN x64 R2 enterprise server.   i have run a backup maintainance job and now i have two databases in restoring mode for over 4hrs.   my questions are: why are these databases restoring when i was backing up? and why are they not being logged anywhere - i can not find anything on logs, activty monitor, or even Windows Resource Monitor.   i can not see any work been done by MSSQL to restore these databases, and yet they are set as 'RESTORING'.   any suggestion from the community here will be much appreciated as i am banging my head against the wall already.   thanks,   Nicolas    data warehouse | data migration | ETL/ELT | UK | http://www.brainpowered.net

Restoring a record from backup

I need to restore a record from backup but the record in the main DB has been deleted along with its primary key. Can I restore that same record (with its primary key kept in place) to the main DB from the backup DB ? I don't want to generate a new primary key, just add it to where it was before. Theoretically, filling in the hole. Is this possible and so how? Thanks.- Tackling one issue after another..

Error Restoring sql 2000 backup on SQL 2008 Error: 928


I'm restoring several sql 2000 user databases on a sql 2008 r2 box, but sometimes I receive the following error

Error: 928, Severity: 20, State: 1

During upgrade, database raised exception 156, severity 25, state 1, address 0000000001C05A9F. Use the exception number to determine the cause.

So the error occured after the restore and while upgrading the database schema from version: 539 to 661.

However I notice that if I try to restore it again with the same statement, sometimes it goes sometimes it doesn't.

I'm not finding any 823,824 or 825 errors on SQL log.

The database compatibility level is 80.

While running Upgrade Adivsor on the database I saw 27 objects that use the older outer join sintax *=. However being that just a warning I don't think that's the cause, moreover because if I retry sometimes the restore completes succesfully.

Does anybody knows what is this?

Is this the I/O subsystem failing?

Why is this intermitent?

How do I fix it?


Thanks in advance.


Restoring a Content Database to an earlier MSSQL backup?

We'd like to roll back a content database to an earlier point in time. Is this doable by just shutting down the frontend, and then restoring the MSSQL WSS_Content database, or are there some quirks that we need to take into account?

SQL Server Compatibility problem while backup and restoring ????


Hi ...

SQL Server Compatibility problem.

1. I have taken backup from sql server 2008 R2 , it is not restoring in sql server 2005 ?

Wats the problem and how to solve this issue ??

2. I have taken sql server 2008 R2 database and log file (mdf and ldf). it is also not attaching in SQL server 2005.

Plz help me to solve this .....

Restoring Data for a specific filegroup



I am trying to figure out what the best way is to seperate data physically for example different companies. One simple way ofcourse is using different database for each company. Downside is maintenance.

Another solution is different filegroups for each company by creating a partition function with ranges and assigning the companyid column from different tables to the partition scheme. This way i've only one database to maintain. The problem with this solution which i ran into is creating backups and restoring. Each filegroup can be accessed seperatly within the backup when using the partial key statement which is great. But lets say all companies want to return to their data from this morning except company 3. If i take the backup from this morning and then restore the current log backup (made no logs in between) for filegroup company3 then all other companies(filegroups) have their current data also. I wonder if there is an solution for this, am i doing something wrong or is it just not possible what i want?.

with regards

Restore sequence i use:

FILEGROUP = 'Primary'
FROM DISK='C:\Databases\Data2\BackupFromThisMorning\MultipleCompaniesDB.bak'


Problem in restoring backup file


at System.DBNull.System.IConvertible.ToBoolean(IFormatProvider provider)
   at System.Convert.ToBoolean(Object value, IFormatProvider provider)
   at Microsoft.SqlServer.Management.SqlManagerUI.BakDevMediaContents.InitBackupSets(Restore sqlRestore)
   at Microsoft.SqlServer.Management.SqlManagerUI.BakDevMediaContents.InitProp()
   at Microsoft.SqlServer.Management.SqlManagerUI.BakDevMediaContents.InitializeOffline(CDataContainer dataContainer)
   at Microsoft.SqlServer.Management.SqlManagerUI.BackupDeviceContents..ctor(CDataContainer dataContainer, IServiceProvider sp)
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreSelectBackupSource.buttonContents_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)

Setup a login account for system backup, but now allow accessing data


Dear All,

     My problem seem quite contradiction. Not sure it's possible to do. My question is we used a symmatic software to backup our SQL databases. It require an admin right SQL user account to perform it. However some of database is quite sensitive e.g HR data. I don't want to let the IT staff to make use of this privilege to read the data. Is there any solution on this?

Best Rdgs


Restoring only a few tables from the backup


Hello All,

I've a backup available to me (MS SQL 2005)- which is quite large. Is it possible to restore only a few tables instead of the entire database? These table may or may not exist on a single file group.



Restoring a SQL 2k backup to a new SQL 2008 db


I got a customer backup (from 2k, the file itself is named 115TCSSQL) that i'm trying to restore to my SQL 08 instance (the db i'm trying to restore with replace to is named 115th) to troubleshoot some goofy records he has.

I run restore filelistonly from disk  and i get the logical name and physical name



So i try to restore:



database [115th]



Problem restoring backup in SQL 2008 enterprise


I created a backup from an express version and now that im restoring it to an enterprise edition i keep getting an error. Can anyone help me?

I keep getting this error, that the version is incompatible with this server running on version 10.00.1442 and the backed up version is running on 10.00.2531.


Restoring Moss 2007 backup


Dear all

We have a backup taken from Moss 2007.  But, we are looking to restore it in a environment which has WSS 3.0.  Is it possible to perform such restoration ?? Pl help



database backup without data


is there a way to back up a database without backing up the data (table, view, procs)

also back up the jobs on a server/linked servers.


is the only option to script them all? I dont need the data just the items like I mentioned about

Tables data Backup


Hi All,

I have a requirement where i want to take SQL tables data (18 in number) and to take backup of data for every 3 months, there are millions of records are there and i want that 3 months old data should be archived and delete/truncate the data.

And how should i restore back this backup in case data is needed..

Thanks in advance...  Please advise me i am not a DBA.

Thanks & Regards,


Backup from SQL srv 2008 stdr R2 now restoring on SQL srv 2008 Dev ... Error



I used to have a SQL Server 2008 Standrad R2 installed, had 4 databases only. Now it seems that a bit of our project doesn't work that good with R2 so we decided to uninstall R2 ver and install the previos version: SQL Server 2008 Developer.

I backed up my databases, scripts and SPs, uninstalled the R2 ver and installed the the "other" version ... Dev ... now I want to restore my databases and get the following error:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&LinkId=20476


I can't find any information on how to restore backwards.

Please help?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend