none
Not enough space for Restore

    Question

  • Hi,

    I am using SQL Server 2008 R2, I am trying to restore a full backed up data with the diff transaction file using Enterprise Manager, I have pointed the restore process to my backup on my E drive and I am also creating all my mdf and ldf files to the same drive letter just a different folder but when I ask the restore to take place with the option of "Restore With Norecovery" I get told there is not enough room on the C drive to complete the operation.  My question is, I want my database on the E drive and all corresponding files on the E drive, why is it looking at the C drive and is there a way of getting around this?

     

    Thanks P

    Wednesday, February 16, 2011 11:22 AM

All replies

  • How much free space do you have in C drive Where the SQL server has been installed?
    Muthukkumaran SQlDBA
    Wednesday, February 16, 2011 11:36 AM
  • Interesting, could you post the restore statement you are using and also the results of the following query:
    SELECT
     database_id
    ,DB_NAME(database_id)
    ,file_id
    ,type_desc
    ,name
    ,physical_name
    ,state_desc
    ,size
    ,growth
    FROM sys.master_files

    It would be interesting to see what database files you have on the C drive as to whether this was causing the issue, maybe trying to insert restore history into MSDB or using TempDB for something?

    Is there anything more descriptive in the SQL Log?


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Wednesday, February 16, 2011 11:37 AM
  • haven't used SQL 2008, but in 2005 to restore to a different location you must click Options page and modify the Restore As location.  See 4.g.ii in the following article.

    http://networkadminkb.com/kb/Knowledge%20Base/SQL/How%20to%20Restore%20a%20Database%20in%20SQL%20Server%202005.aspx

    Wednesday, February 16, 2011 11:39 AM
  • Agreed. Script the RESTORE command from SSMS and post that/those commands here. That would be the first step, to see what you actually execute.
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, February 16, 2011 11:44 AM
  • Hi all,

    Thanks all for such a quick response, I will try and include answers to all questions posted, the C drive where SQL Server is installed has 38.8 GB free and where I am wanting to put all the files once resotre has been completed has 141 GB free.  Below is the results of the SQL statement (sys.master_files table)

     

    database_id    (No column name)    file_id    type_desc    name    physical_name    state_desc    size    growth
    1    master    1    ROWS    master    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf    ONLINE    512    10
    1    master    2    LOG    mastlog    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf    ONLINE    128    10
    2    tempdb    1    ROWS    tempdev    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf    ONLINE    1024    10
    2    tempdb    2    LOG    templog    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf    ONLINE    64    10
    3    model    1    ROWS    modeldev    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf    ONLINE    160    128
    3    model    2    LOG    modellog    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf    ONLINE    64    10
    4    msdb    1    ROWS    MSDBData    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf    ONLINE    1712    10
    4    msdb    2    LOG    MSDBLog    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf    ONLINE    192    10
    5    ReportServer    1    ROWS    ReportServer    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf    ONLINE    416    128
    5    ReportServer    2    LOG    ReportServer_log    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServer_log.LDF    ONLINE    784    10
    6    ReportServerTempDB    1    ROWS    ReportServerTempDB    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf    ONLINE    288    128
    6    ReportServerTempDB    2    LOG    ReportServerTempDB_log    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.LDF    ONLINE    96    10
    8    distribution    1    ROWS    distribution    E:\Data\distribution.MDF    ONLINE    896    128
    8    distribution    2    LOG    distribution_log    E:\Data\distribution.LDF    ONLINE    1544    10

     

    And I have pointed to E drive for everything except the initial location I dont know what the problem is?? Just a further note, this database is one that was on another server but has been sent to me to restore on a new server and I do believe they had it on the C drive but as I have said where I want it to be I dont know what is up?

    Sorry, missed a bit, I have the script now from the Enterprise Manager, should of looked at this first and it says everything is pointing to C:

     

    RESTORE DATABASE [CGD_PPIAdmin] FILE = N'CGD_PPIAdmin_Dat' FROM  DISK = N'E:\DBRestore\SERVER47_CGD_PPIAdmin_20110206-005110_20110205-230001_FULL.bak' WITH  FILE = 1,  MOVE N'CGD_PPIAdmin_Dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CGD_PPIAdmin.mdf',  MOVE N'CGD_PPIAdmin_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CGD_PPIAdmin_0.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 10

     

    But the Enterpirse Manager windows all show the E drive, the only problem I have now it how to restore the Differential after already getting this problem.

     

    Thanks P

    • Edited by harlequintp Wednesday, February 16, 2011 12:02 PM Update
    Wednesday, February 16, 2011 11:55 AM
  • Are you using ssms right. Can you post me the exact error from errorlog.
    Muthukkumaran SQlDBA
    Wednesday, February 16, 2011 12:02 PM

  • Hi muthukkumaran,

     

    Thanks again for the quick response, I restored from this database earlier on today on the same server with no problems but omitted the option to add the differential so had to do it again and that worked fine first time, so I just used the same process again and got the forementioned error, I have the restore running at the moment from the script so as soon as that is finished I will post and update and get the error from the log for you as well as its annoying me and I would like to know the answer even if I dont have the problem again.

     

    Thanks Phil

    Wednesday, February 16, 2011 12:20 PM
  • it looks like you are trying to restore onto the C drive, the code shows it:

     MOVE N'CGD_PPIAdmin_Dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CGD_PPIAdmin.mdf', 

    MOVE N'CGD_PPIAdmin_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CGD_PPIAdmin_0.ldf', 

    You need to change this to something like 'E:\SQL DATA.....'

    The other thing that I will say is that you SQL server is not set up for optimal IO performance, in an ideal worls your data files should be separated onto different spindles from you log files and I would definitely recomend putting TempDB on yet another spindle. At the very least it needs to be moved off the c drive where you have the os.

    If you're keen to follow this advice then check out my blog on how to mopve TempDB - http://sql.richarddouglas.co.uk/archive/2010/08/moving-tempdb.html

    Rich

     

     


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Wednesday, February 16, 2011 12:27 PM
  • Hi Richard,

     

    Thanks for the advice, you are correct the Script does point to the C drive but when you go through the Enterprise Manager and set everything to go to E and you can physically see that in the relevant windows it points to see, what I have done is amend the script to point to the right place and ignore the enterprise manager tool for now, just wondered what would cause this problem as I have no issue when I restored it earlier on today.  I also totaly agree with your advice on the seperation of the data files as well as the tempdb but at the moment I am dealing with a very poor machine and a none exsistent budget so trying to do the best I can.  When things start to get better hopefully April I have been told I will take on board your recommendations and have a look through your blog when doing the new build, having come from a development background and doing DBA duties over the years some of my choices are not always the best so always wanting to learn more.

     

    Thanks Phil

    Wednesday, February 16, 2011 12:32 PM
  • Phil, expected results.I did not see that command Rich good catch. Step by step backup/restore
    Wednesday, February 16, 2011 12:50 PM
  • Hi Phil,

    If you ever need any advice on setting up the infrastructure feel free to ping me an email/tweet.

    Rich


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Wednesday, February 16, 2011 12:53 PM
  • <<Thanks for the advice, you are correct the Script does point to the C drive but when you go through the Enterprise Manager and set everything to go to E and you can physically see that in the relevant windows it points to see, what I have done is amend the script to point to the right place and ignore the enterprise manager tool for now, just wondered what would cause this problem as I have no issue when I restored it earlier on today.>>

    Hmm, I have read and re-read above sentence a few times now, but I still can't understand it. Let me simplify. Which one of below two describes your problem:

    1. Management Studio (not Enterprise Manager, btw), generates a script creating the database files on C: even though you configured the files to go to E: in the GUI.

    2. Management Studio does indeed produce the script correctly, the MOVE option points the database files to E:, but SQL server still uses space on C:.

    If 1., then there's a but in the GUI (SSMS).
    If 2, there's a bug in the database engine.
    For us to determine which it is, we need to see the RESTORe commands generated by the restore dialog in SSMS.


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, February 16, 2011 12:57 PM
  • Thanks for the help Rich its much appreciated.

     

    Thanks Phil

    Wednesday, February 16, 2011 1:10 PM
  • Hi Tibork,

    My apoligies fo the use of Enterprise Manager and not Management Studio, been one of those days when nothing has gone right lol

    In answer to you question it is option "1", out of interest while I was waiting for my restore to complete, I did a seperate restore doing exactly the same steps

    on a different server and not a problem, everything worked as expected.

    When my initial resotre has finished I will post the Restore Commands for you to take a look at.

    Thanks Phil

    Wednesday, February 16, 2011 1:16 PM
  • Long restore :)
    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Wednesday, February 16, 2011 4:18 PM
  • Hi Richard,

     

    Sorry, had; had enough by the time it had finished and finally restored.   1 Hour 46 minutes for the main backup and 38 minutes for the differential, really dont think the people that are doing my job for the other company are really keeping the database as clean as possible ;) All is working fine at the moment, fingers crossed.  Tried running the same thing again this morning, "I dont sleep much" and had no issues at all and followed exactly the same steps as I had even writting them down from last time.  I just cant see what the difference was from last time, perhaps SQL had decided it was sick of this database and wanted to make it as complicated as possible to get it back on the server.  For now I will keep the post open if that is ok with everyone as I am going to have to do it for real so to speak either Sunday or Monday when I move the application over on to this server as at the moment I was just running tests and getting other things set up.  Thanks again for everyone's help and watch this space to see if it crash's and burns :-)

     

    Thanks Phil

    Thursday, February 17, 2011 8:15 AM
  • How annoying is it when you can't reproduce at will some behavior.

    Next time, make sure you script the TSQL and save that for future reference. And if you find that "the one that used space on C:" had C: in the MOVE optionsof the RESTORE command, then you would want to consider/remember exactly how you cklicked'n'typed in the GUI...


    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, February 17, 2011 8:50 AM
  • I'm going to echo Tibor's comments. When you are rolling something out, you really should have everything scripted and those scripts tested to rule out human error.

     

    Rich


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Thursday, February 17, 2011 1:00 PM