May 17, 2018 at 5:47 pm
Hi Guys,
Had a very strange is happen to me, I was required to restore our production db to a specific time(DB A) for some testing so I did so on our test server(thank god for that).
The test server also had a copy of our production training db(DB B).
Once I restored the DB A under a unique catalog name and with unique file names DB B then then goes into Restoring mode along side DB A!!!?
DB A completes the restoration but DB B is stuck in restoring... Had I restored the Test db on the production server I can't imagine the drama that would of caused.
Has anyone ever come across this before? I'm still experiencing the problem each time I refresh the Test db it takes out the Training db! Is there a fix or work around??
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Cheers,
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 18, 2018 at 10:21 am
Could you post your restore script?
I have personally not experienced this, but I am wondering if maybe something in your restore script it touching DB B.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 18, 2018 at 10:46 am
I've never heard of that either. What are you using to do the backups and restores? Are these native backups using t-sql scripts or something else? And is it the same for the refreshes that you have the problems with as well as this last one?
Sue
May 18, 2018 at 11:56 am
Post the restore code that you're using.
And, yes... I've seen something similar in the past when two databases are backed up to the same file and other cautions aren't take.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2018 at 7:41 pm
Hi Guys,
Its a very standard boring script
USE [master]
BACKUP LOG [DBNAME] TO DISK = N'<<Drive withheld>>\DBNAME_LogBackup_2018-05-21_11-20-50.bak' WITH NOFORMAT, NOINIT, NAME = N'DBNAME_LogBackup_2018-05-21_11-20-50', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [DBNAME_Newdb] FROM DISK = N'<<Drive withheld>>\Toberestored\DBNAME_backup_2018_05_15_010000_5514393.bak' WITH FILE = 1, MOVE N'<<Withheld>>' TO N'<<Drive withheld>>\DATA\DBNAME_Newdb.mdf', MOVE N'<<Withheld>>' TO N'<<Drive withheld>>\DATA\DBNAME_Newdb_1.ndf', MOVE N'<<Withheld>>' TO N'<<Drive withheld>>\DATA\DBNAME_Newdb_2.ndf', MOVE N'<<Withheld>>' TO N'<<Drive withheld>>\LOG\DBNAME_Newdb.ldf', NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [DBNAME_Newdb] FROM DISK = N'<<Drive withheld>>\Toberestored\DBNAME_backup_2018_05_15_030000_6243966.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [DBNAME_Newdb] FROM DISK = N'<<Drive withheld>>\Toberestored\DBNAME_backup_2018_05_15_031501_4462518.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [DBNAME_Newdb] FROM DISK = N'<<Drive withheld>>\Toberestored\DBNAME_backup_2018_05_15_033001_1181289.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [DBNAME_Newdb] FROM DISK = N'<<Drive withheld>>\Toberestored\DBNAME_backup_2018_05_15_034500_9796941.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [DBNAME_Newdb] FROM DISK = N'<<Drive withheld>>\Toberestored\DBNAME_backup_2018_05_15_040000_7314860.TRN' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
What happening is when I create a NEW db for testing the EXISTING train db (which is restored from the same backup from prod) goes into restore mode... but as nothing is being restored to it.. it just hangs until you do a new specific restore over the training db. The test db will complete the restoration... but somehow SQL is sending restore command to both dbs.
I have to be honest it has me totally flummoxed
To reiterate.. the test db is a newly created one, with unique catalog name, has unique physical file name and over with restore is not enabled.
Cheers!
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 20, 2018 at 8:24 pm
I'm not seeing anything obvious in that, either. Sorry... not much help here.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2018 at 9:07 pm
Yep its totally odd...
The only thing I have noticed is the Test DB(NEW Db) Database_Id preseeds the Training Db(EXISTING Db)... but.. that shouldn't make a difference...
And it keeps doing it... PLUS... I've looked into the SQL Server log just before the restore of the NEW Db and it logs...
Starting up database 'NEWDb'.
The database 'NEWDb' is marked RESTORING and is in a state that does not allow recovery to be run.
Database was restored: Database: 'NEWDb' , creation date(time): 2014/11/28... blah blah blah
RESTORE DATABASE successfully processed... blah blah blah
Nothing within the SQL Server log files that mentions the EXISTING Db being restored or even being accessed. :blink: :crazy:
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 21, 2018 at 2:48 am
BACKUP LOG [DBNAME] TO DISK = N'<<Drive withheld>>\DBNAME_LogBackup_2018-05-21_11-20-50.bak' WITH NOFORMAT, NOINIT, NAME =N'DBNAME_LogBackup_2018-05-21_11-20-50', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
Option NORECOVERY put database in restoring state, it is used when you want to get tail log backup, and is ON by default in SSMS when you restore on the same server.
May 21, 2018 at 3:04 am
I think e4d4 is right. Only use NORECOVERY in a BACKUP LOG statement if you intend to restore over the database you're backing up. You should be able to bring the original database back like this: RESTORE DATABASE [DBNAME] WITH RECOVERY.
John
May 21, 2018 at 7:35 pm
Awesome... next time I have to refresh the test db I will take note of that flag and will report back with the results.
Cheers!
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 22, 2018 at 7:50 pm
Success!!
Thanks E4D4 and John... when I restored the new db without the tail log backup the training db didn't go into restoring mode... but why on earth it will put a totally different db into restoration is beyond me.
Maybe its a bug.. I dunno.
Thanks for the suggestion!
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 22, 2018 at 9:44 pm
Mr J - Tuesday, May 22, 2018 7:50 PMSuccess!!Thanks E4D4 and John... when I restored the new db without the tail log backup the training db didn't go into restoring mode... but why on earth it will put a totally different db into restoration is beyond me.
Maybe its a bug.. I dunno.
Thanks for the suggestion!
It's a total SWAG but I wonder if it could be that the logical names (or something similar) on the training DB aren't what you think . Someone could have renamed the DB in the past.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2018 at 7:02 am
This is not a bug, but expected behavior. You said you ran the following command:
BACKUP LOG [DBNAME] TO DISK = N'<<Drive withheld>>\DBNAME_LogBackup_2018-05-21_11-20-50.bak' WITH NOFORMAT, NOINIT, NAME = N'DBNAME_LogBackup_2018-05-21_11-20-50', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
Then you restored the "[DBNAME_Newdb]" database. The tail-log backup command above with the NORECOVERY option will put the "[DBNAME]" database into a "restoring" state to insure that no additional changes can be made to the database.
You can read more about the NORECOVERY option here: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-server-2017
May 24, 2018 at 11:16 pm
Greetings!!
What it seems to do is by default is to create the tail log backup when you use the GUI to restore it, and automatically adds the database name into the database source field(In this case the name of the training db)... a field which you can not edit... and I believe it is taking that name when building the tail log backup command and not the destination restore name.
Cheers!!
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 25, 2018 at 5:57 am
Mr J - Thursday, May 24, 2018 11:16 PMGreetings!!What it seems to do is by default is to create the tail log backup when you use the GUI to restore it, and automatically adds the database name into the database source field(In this case the name of the training db)... a field which you can not edit... and I believe it is taking that name when building the tail log backup command and not the destination restore name.
Cheers!!
That is weird. Yup it does this with the downloaded versions of SSMS. It's inconsistent on which databases it does this with. I didn't play with it long enough to figure out why it does that for some databases. I checked a couple versions that come with SQL Server installation and they don't have this behavior.
But when you are in that Restore Database window, go to the options page. You can remove the option for tail of the log backup in the middle of the page.
Sue
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply