November 28, 2011 at 8:17 pm
Hey all,
I am running into an issue and I was hoping someone could explain why it was happening. I was trying to restore a database on a SQL instance where the user databases are on the D drive, but the system databases are on the C drive because it was initially configured as the default directory.
When I tried to restore a 3 gb user database with a large (22 gb) transaction log, I got an error about there not being enough space on the C drive. The C drive has about 5 gigs free, not enough to restore the database to that drive but not so low that it should cause functionality issues (I think). However, the D drive has plenty of space, and that drive is the location of the default data directory (I checked).
What is SQL doing to the system databases that would cause the C drive to need that much free space? Is it due to one of the logs of the system databases becoming very big as a result of restoring such a database? The thing is, after shrinking the transaction log of the user database I was able to restore the database without a problem. I would like to try to understand why this constraint on the C drive exists. Can anyone explain it? Thanks.
November 28, 2011 at 10:40 pm
cjohn5552 (11/28/2011)
Is it due to one of the logs of the system databases becoming very big as a result of restoring such a database?
No. This can not be the reason.
I think the user database you are restoring was residing on C: drive. So when you try to restore, it gets restored to C: drive only even though the default data directory is D:
Run RESTORE FILELISTONLY to know the file location. Use WITH MOVE option while RESTOREing to place the files on D: drive.
November 29, 2011 at 12:32 am
cjohn5552 (11/28/2011)
Hey all,I am running into an issue and I was hoping someone could explain why it was happening. I was trying to restore a database on a SQL instance where the user databases are on the D drive, but the system databases are on the C drive because it was initially configured as the default directory.
When I tried to restore a 3 gb user database with a large (22 gb) transaction log, I got an error about there not being enough space on the C drive. The C drive has about 5 gigs free, not enough to restore the database to that drive but not so low that it should cause functionality issues (I think). However, the D drive has plenty of space, and that drive is the location of the default data directory (I checked).
What is SQL doing to the system databases that would cause the C drive to need that much free space? Is it due to one of the logs of the system databases becoming very big as a result of restoring such a database? The thing is, after shrinking the transaction log of the user database I was able to restore the database without a problem. I would like to try to understand why this constraint on the C drive exists. Can anyone explain it? Thanks.
You must have tried to restore the database using the restore wizard. Also the database whose backup you were restoring should have been residing on C:\ drive only.
Use T-SQL to restore the database as much as possible so that you understand restoration process better.
1. As Suresh said above execute this command to get the logical data & log file names:
RESTORE FILELISTONLY FROM DISK = N'<BackupFilePath>'
2. Execute this command to restore the database:
RESTORE DATABASE <DatabaseName> FROM DISK = N'<BackupFilePath>'
WITH STATS = 1,
MOVE 'LogicalDataFileName' TO N'DriveName:\DataFilesDirectoryName\DatabaseName.MDF',
MOVE 'LogicalLogFileName' TO N'DriveName:\LogFilesDirectoryName\DatabaseName_Log.LDF'
P.S.: Default data directory path which you have set to D:\ is used only when you create the new database with default configuration.
November 29, 2011 at 1:12 pm
Thanks for the replies. Yeah, I'm running a restore query that a colleague wrote, so I may not understand it very well. The .bak db file was on the D drive.
The database ended up on the D drive, but you're saying that it is initially restored to the C drive and then moved? (WITH MOVE)
What controls this? One thing I noticed is that, although the default data directory is on the D drive, some registry values point to the C drive.
The key location is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\Parameters
SqlArg0:-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
SqlArg1:-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
SqlArg2:-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
I assume these registry values simply specify the location of the master database and error log. While clients that have SQL installed on the D drive have these paths pointing to D, this location has them on the C drive. This is fine, but does this affect where the database is initially restored?
November 29, 2011 at 8:09 pm
Hi,
Those registry keys have nothing to do with to where a user database gets restored. They are for [master] system database.
Backup (file *.bak) contains details about data and log files locations of the backed up database. When restored without "WITH MOVE" option SQL Server tries to place database files to the same file system directories as specified in the backup. If such directories do not exist RESTORE fails.
So make it a rule always to use WITH MOVE option to specify files location when restoring databases 🙂
November 29, 2011 at 9:11 pm
cjohn5552 (11/29/2011)
Thanks for the replies. Yeah, I'm running a restore query that a colleague wrote, so I may not understand it very well. The .bak db file was on the D drive.The database ended up on the D drive, but you're saying that it is initially restored to the C drive and then moved? (WITH MOVE)
If the database ended up on the D drive, you should not have got error about C drive.
WITH MOVE creates the files directly on specified path (D drive). It does NOT create the files intially on the old path.
Please post the restore query (complete script).
Please run RESTORE FILELISTONLY as mentioned earlier and post the result.
It may help to resolve this issue.
November 29, 2011 at 9:39 pm
Just a second on what Suresh says. That's likely the issue. If you post the query you ran, I'm sure Suresh can help you alter it to prevent issues on the C: drive.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 29, 2011 at 10:02 pm
Also run:
xp_fixeddrives
and post the result.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply