October 3, 2018 at 2:35 pm
I'm not a SQL person by any means, however I know how to do a few very basic tasks and what I don't know I look up. For example, the hard drive where the databases reside was getting low on space. I've moved them before but because it had been a while I looked up how to do it again, just as I did the first time I needed to move them. The first time moving them I remember detaching the database, moving the files, then attaching it again. However this time I read where that is not the recommended way to do it. The ALTER command was the correct way to relocate databases to another drive. So, I copied the sytaxes and began the process. 1st one went great as did the second one, but on the 3rd one I flubbed up. The database names are similarly named with only a number being the difference, so I was changing that number for each command then running it. Well I missed a number in one of the commands that modifies the paths after moving the physical files, and now my mdf has an ldf of the wrong database log file. I was working on the Bank01_2017_03 database but neglected to change the ldf's number from 02 to 03 so when I brought it back online it created a new ldf called Bank01_2017_02_log.ldf. I caught it before it was too late and was able to correct the 03 database files before bringing it back online, but I am unable to figure out how to tie the original 02 ldf back to the 02 mdf. If I run the command to change the path to the original ldf, when I try to bring it online it fails with the 5173 error "Log file 'E:\SQL DATA\Bank01_2017_02.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously."
Database: Bank01_2017_02
Original paths: Bank01_Data - D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Bank01_2017_02.mdf
Bank01_Log - D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Bank01_2017_02.ldf
Should be paths: Bank01_Data - E:\SQL DATA\Bank01_2017_02.mdf
Bank01_Log - E:\SQL DATA\Bank01_2017_02.ldf
Actual paths: Bank01_Data E:\SQL DATA\Bank01_2017_02.mdf
Bank01_Log E:\SQL DATA\Bank01_2017_02_log.ldf
How do I fix this?
Thanks,
Jenny
October 3, 2018 at 3:11 pm
I'm assuming, and hoping, that you copied the files to the new locations rather than moved them (never do that, always copy and retain the originals until the ONLINE works correctly).
If so, do this:
1) Modify the file names on the db to whatever you want them to ultimately be.
2) Set the db offline.
3) Delete all the new files -- again, ONLY if you originally COPIED the files rather than moved them.
4) Copy the files again.
5) Bring the db ONLINE again.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2018 at 3:16 pm
Yeah, that'd be a negative. However...I do have backups of each months database.
Jenny
October 3, 2018 at 3:31 pm
ScottPletcher - Wednesday, October 3, 2018 3:11 PMI'm assuming, and hoping, that you copied the files to the new locations rather than moved them (never do that, always copy and retain the originals until the ONLINE works correctly).If so, do this:
1) Modify the file names on the db to whatever you want them to ultimately be.
2) Set the db offline.
3) Delete all the new files -- again, ONLY if you originally COPIED the files rather than moved them.
4) Copy the files again.
5) Bring the db ONLINE again.
Scott - the problem is that she brought the database online and it created a new transaction log file in the new location with a new name. If I am reading this correctly - she then fixed that problem and renamed the new transaction log file to the appropriate name.
At this point - the original transaction log file is no longer valid, but that shouldn't be a problem as the database was brought offline cleanly. The database was able to recover and build a new transaction log file because there were no open transactions in transit when the database was taken offline - so there shouldn't have been anything that needed to go through recovery (either undo or redo) to bring the database online.
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
October 3, 2018 at 3:47 pm
Jeffrey Williams 3188 - Wednesday, October 3, 2018 3:31 PMScottPletcher - Wednesday, October 3, 2018 3:11 PMI'm assuming, and hoping, that you copied the files to the new locations rather than moved them (never do that, always copy and retain the originals until the ONLINE works correctly).If so, do this:
1) Modify the file names on the db to whatever you want them to ultimately be.
2) Set the db offline.
3) Delete all the new files -- again, ONLY if you originally COPIED the files rather than moved them.
4) Copy the files again.
5) Bring the db ONLINE again.Scott - the problem is that she brought the database online and it created a new transaction log file in the new location with a new name. If I am reading this correctly - she then fixed that problem and renamed the new transaction log file to the appropriate name.
At this point - the original transaction log file is no longer valid, but that shouldn't be a problem as the database was brought offline cleanly. The database was able to recover and build a new transaction log file because there were no open transactions in transit when the database was taken offline - so there shouldn't have been anything that needed to go through recovery (either undo or redo) to bring the database online.
If she has ALL the original files, she can simply discard all the new files, copy the files back over, and do the online again.
She can assume the new log won't cause issues -- and in this case she might be forced to, if she didn't keep an original copy of the files -- but I always prefer to go back to the original files and re-ONLINE them.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2018 at 3:52 pm
ScottPletcher - Wednesday, October 3, 2018 3:47 PMIf she has ALL the original files, she can simply discard all the new files, copy the files back over, and do the online again.She can assume the new log won't cause issues -- and in this case she might be forced to -- but I always prefer to go back to the original files and re-ONLINE them.
Yes - if she has the original files and no one has made any changes to the database since it was brought back online, then she can copy those original files again and bring the database online. If any changes have been made since the database has been online (the second time) - those changes would be lost.
It looks like she doesn't have the original files though - but it sounds like this database may be from a previous month and is not updated...and there is a good backup that has all available data (maybe).
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
October 4, 2018 at 8:04 am
I have confirmed that I do in deed have the backup for that database. Scott and Jeffery, thank you both for responding on this. I think to be on the safe side I would rather restore it from backup. I have no way myself of verifying that the database doesn't have any problems or is missing data based on what you said Jeffery in your first comment. I don't use the application for which this SQL database is for and I'd prefer not to alarm the end-users who do. So, if one of you could tell me the steps I need to take to restore this database based on its current state, I would greatly appreciate it.
Thanks so much,
Jenny
October 4, 2018 at 8:44 am
itgirl17 - Thursday, October 4, 2018 8:04 AMI have confirmed that I do in deed have the backup for that database. Scott and Jeffery, thank you both for responding on this. I think to be on the safe side I would rather restore it from backup. I have no way myself of verifying that the database doesn't have any problems or is missing data based on what you said Jeffery in your first comment. I don't use the application for which this SQL database is for and I'd prefer not to alarm the end-users who do. So, if one of you could tell me the steps I need to take to restore this database based on its current state, I would greatly appreciate it.Thanks so much,
Jenny
If you are absolutely sure that no data has been added/modified since that backup was taken - you can simply restore the database. If you utilize the GUI - then specify the new file locations and file names, if you use a script utilize the WITH REPLACE and MOVE options.
With that said - I don't think you have an issue. When you took the database offline - it was shut down cleanly, meaning all outstanding transactions were completed before the database was taken offline. Can you confirm the command that you issued to take the database offline?
To be safe - perform a full integrity check on the database: DBCC CheckDB(thisdb) With no_infomsgs, all_errormsgs, data_purity;
And - make sure you keep that backup available for a while just in case. You can always restore that database as a different name and compare data between that version and the current database if needed.
The biggest concern I would have about restoring the backup is when it was taken - what recovery model - and whether or not you have transaction log backups through current point in time. If the database is simple recovery you can only restore the data to the time when the backup completed. If the database is in full recovery - and you have an unbroken chain of transaction log backups through the time you took the database offline - you can restore that to get to that point in time...but can you be sure that no changes have been made since that point in time?
Once the database was brought back online - the users would have access to the database and could have changed anything. Are you sure the users have not accessed the database since it was brought back online?
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
October 4, 2018 at 9:08 am
I see your point now Jeffery! No, I cannot confirm that data hasn't been changed since that backup was done. So...I think I will leave it alone.
To answer your question about the command issued to take it offline, I used ALTER DATABASE Bank01_2017_02 SET OFFLINE;
For the integrity check on the database, do I just copy exactly what you have above and execute it, or do I substitute my database name for what you have in parenthesis? Remember, I don't know SQL that well. 😉
Thank you,
Jenny
October 4, 2018 at 9:56 am
Substitute your database
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
October 4, 2018 at 1:39 pm
Ok, that script did not come back with any errors, so I believe we are good.
Thank you again for your help with this, I truly appreciate it! I will definitely be more cognizant of what I'm doing from now on!!
Jenny
October 4, 2018 at 3:11 pm
itgirl17 - Thursday, October 4, 2018 1:39 PMOk, that script did not come back with any errors, so I believe we are good.
Thank you again for your help with this, I truly appreciate it! I will definitely be more cognizant of what I'm doing from now on!!Jenny
That is good to know - thank you for the update.
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 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply