December 27, 2008 at 5:05 pm
I was moving my user databases to another file location using the "ALTER DATABASE" command under the "Planned Relocation and Scheduled Disk Maintenance Procedure" section described on the following webpage:
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90,printer).aspx
When I copied the files, I inadvertently copied the log files to the wrong location. After restarting the SQL service, all the databases were available. However, new log files were created in the directory specified in sys.master_files table and the "old" log files (which resided in the wrong directory) were ignored. Should I still consider the database move successful, or did I lose any information?
December 27, 2008 at 8:04 pm
I dont think u'd lose any data.
refer BOL on checkpoints: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406.htm
It says shutting down a database causes a checkpoint which means all the dirty pages would be written back to the disk. So I think, you're safe.
December 28, 2008 at 9:02 am
- Did you follow best practices by taking a backup before you moved the datafiles ??
- if you did perform a clean shutdown and there weren't any ongoing transactions, you should be fine.
- indeed a checkpoint causes all dirty pages being written to disk, but dirty pages means the pages have been modified since they were fetched from disk.
It (sqlserver) will use the log file(s) to check the pages data for data integrity. So... if an instance crashes or is forcebly shut down, at restart time, it will use the logs to redo the transactions and rollback any open transactions.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 28, 2008 at 9:36 am
You should be fine. The logs should be closed properly when you shut down SQL Server, so I wouldn't expect anything to be broken.
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
December 28, 2008 at 3:20 pm
ALZDBA (12/28/2008)
- Did you follow best practices by taking a backup before you moved the datafiles ??
Yes, I took database backups. However, I was trying to save time and not manually test if the restore was exactly the same as the "improperly moved" databases in question.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply