April 22, 2009 at 9:26 am
Hello,
I am trying to restore a database to the directory where the user database data files are kept (on the D drive). The D drive has a decent amount of space, whereas the C drive is running low on space. The only databases still located on C are the system databases. The restore command itself specifies that the data files for the restored database are to be stored on the D drive (and that's where the default directory is for the data and log files). Looking at the restore script I'm using, I think the issue is with this line:
insert into #filedetails
exec ('restore filelistonly from Disk = ''' + @ArchiveDir + @ClientAbbreviation + @ArchiveName + @RestoreDate + '.bak''')
I'm not sure what this command does. Does this alter any of system databases? Because it's saying not enough space is available on disk C to restore the database, but I don't see where disk C is alluded to in the query. Any advice or suggestions would be greatly appreciated.
Thanks.
April 22, 2009 at 9:36 am
All that command does is reads the file details of the selected backup file and load that information into a temporary table. I would assume that the information is used later on in your script to build the actual restore command.
The default location for restoring the database is the same directory and path where the database existed when it was backed up. So, if the backup is from a database that exists on the C: drive of another system - that is going to be the default restore location.
To change where the database is restored, you have to use the WITH MOVE option of the restore command. Lookup the RESTORE DATABASE command in books online for further details - but here is a basic example:
RESTORE DATABASE MyDatabase
FROM DISK = '{path to the backup file}'
WITH RECOVERY
,MOVE 'Logical DB Name' TO 'D:\...\MyDatabase_data.mdf'
,MOVE 'Logical Log Name' TO 'D:\...\MyDatabase_log.ldf'
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 9:38 am
April 22, 2009 at 10:05 am
Yeah, well the bit about restoring with move is after that:
SELECT @stmt = 'restore database PracticeRepository' + @ClientAbbreviation + SUBSTRING(@RestoreDate, 5, 4) + 'from Disk = ''' + @ArchiveDir + @ClientAbbreviation + @ArchiveName + @RestoreDate + '.bak''
with move ''' + @DataFile + ''' to ''' + @DbDir + 'PracticeRepository' + @ClientAbbreviation + SUBSTRING(@RestoreDate, 5, 4) + '_Data.mdf'', move ''' + @LogFile + ''' to ''' + @DbDir + 'PracticeRepository' + @ClientAbbreviation + SUBSTRING(@RestoreDate, 5, 4) + '_Log.ldf'''
@ArchiveDir and @DbDir are both set to directories on the D drive.
The query doesn't seem to explicitly refer to the C drive anywhere. Perhaps a temporary table is being created in one of the system databases? In any case, is there anyway to detach and re-attach the system databases using the GUI in SQL Server 2008? Any advice would be appreciated.
Thanks.
April 22, 2009 at 10:27 am
Okay, when you run this script - add a print statement to print the @stmt variable before trying to execute it. Let's see exactly what is being built and executed.
Also, please include the actual error you are getting so I can see exactly what the error is.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply