May 25, 2011 at 4:01 am
Hi,
Is there possiblity to remove the .NDF file (secoundary file ) during restore of the backup file as we can remove the transaction log file and what exactly the effect if we do so .
case 1 (model is simple recovery)
no log files are reuired as we are going for normal restore process
And When we remove the logfile during the restore process of the database what exactly the effect will be on the restored database is there any chance of missing the data
Case 2 what the same effect if it is full recovery model.
Thanks
May 25, 2011 at 4:07 am
No. A restore puts the DB back as it was at the time of backup. You can't remove log files during a restore either, they're required for restoring the DB to a consistent state.
It's possible to restore just the files in the primary filegroup, but that doesn't remove the secondary data file, SQL just marks it as offline and unavailable.
Restore the DB with all files, then take the necessary steps to remove the data file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 4:13 am
Regarding the restoration with no logfile i have try and my database is restored and is working fine.
and its creating from itself the log file even though i removed during restore process.
Might be it will lost some date in full recovery model case if we are removing the logfile from restoration.
What the steps to remove the ndf file after the restoration is done ...
May 25, 2011 at 4:17 am
How are you 'restoring'?
In a restore of a backup (RESTORE DATABASE) it is not possible to remove a log file, or any other file for that matter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 4:21 am
From enterprise manager if you go for restore process and when you will click the tab "option"
you can find the list of files and location of getting restore so delete the location of logfile and and file name ..
it will work
May 25, 2011 at 4:26 am
Enterprise manager? SQL 2000?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 4:29 am
I don't have SQL 2000 and I don't have Enterprise manager, but I suspect all that's doing is restoring the log with default location. You cannot delete a log file during restore, the files are needed. You cannot delete a file during restore.
Check the SQL that the dialog is actually running.
As for removing the NDF, is it part of another filegroup or is it in its own filegroup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 4:31 am
no is of seperate file group
May 25, 2011 at 4:36 am
Then you need to move all the tables/indexes from that filegroup somewhere else (rebuild the clustered index on the new filegroup), then run ALTER DATABASE DROP to get rid of the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 5:11 am
Can i have the steps and query to accomplish the above mention tasks
Thanks
May 25, 2011 at 5:59 am
To move indexes/table to another filegroup
CREATE [CLUSTERED] INDEX ... WITH DROP_EXISTING ON <other filegroup name>
To remove the file once empty
ALTER DATABASE ... REMOVE FILE ...
Read up in books Online for exact syntax, I don't have it memorised either.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 6:31 am
you can refer http://msdn.microsoft.com/en-us/library/ms175905.aspx for moving existing indexes to other filegroups..
May 25, 2011 at 7:01 am
Thanks to all 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply