Create SQL History Database
-
Background
Server 2012 running SQL Server 2012 Management Studio 11.0.5343.0
The vendor software uses SQL for the DB. Each year they create a sub DB as a history file. The 'new' procedure is to create a backup of the DB, then restore it into the history DB.
The backup goes off without much of an issue. However restore does not. The process should create a new DB leaving the original DB in a active state. However, the restore puts the original into a 'Restoring' state. I connected to support and the resolution was to then go to the original DB and restore it from the backup just made.
-
So this is resolved now?
-
Not as I would like no. I don't see why you need to restore the original DB if you made a back up copy of it to create a history DB.
-
What script are you running to take the backup? A backup doesn't require a restore. Did you modify the backup so it's not touching the original when restored? Sounds like it might be still referencing the original DB
Also if this is just histrocial backups why does it actively need to be in SQL as a DB? That's just using more memory even if the DB isn't used.
-
Yeah, I get this as well and haven't resolved yet. Hopefully someone on here will have the answer.
You don't need to restore from backup to get the original on-line, you just need to execute "RESTORE DATABASE DB1 WITH RECOVERY". This is what I used and it seemed to work fine - this wasn't on a production server though, so I can't guarantee anything.
-
@Jason said:
What script are you running to take the backup? A backup doesn't require a restore. Did you modify the backup so it's not touching the original when restored? Sounds like it might be still referencing the original DB
Also if this is just histrocial backups why does it actively need to be in SQL as a DB? That's just using more memory even if the DB isn't used.
No script running. No modification of the backup,
-
@gjacobse said:
@Jason said:
What script are you running to take the backup? A backup doesn't require a restore. Did you modify the backup so it's not touching the original when restored? Sounds like it might be still referencing the original DB
Also if this is just histrocial backups why does it actively need to be in SQL as a DB? That's just using more memory even if the DB isn't used.
No script running. No modification of the backup,
You are doing it wrong in management studio.
-
^^ helpful...
-
@Carnival-Boy said:
^^ helpful...
Sorry, emergency bowel issues.... /wtb cyborg body.
Anyway, in management studio, you are likely not setting the right options. Are you restoring directly to the new DB or are you making a backup first and then restoring the backup? It sounds like the former.
-
In my case, when doing the restore I renamed the database and changed the location, and er, that's about it. There may be another setting that I missed, but I don't know what.
Instructions here look like pretty much what I did https://technet.microsoft.com/en-us/library/ms186390(v=sql.110).aspx -
Turns out the directions they vendor sent us were incomplete / wrong.
it's done and over with.
-
Care to explain? It may help me out.
-
In this case, it was a matter of the settings during the restore.
Under GENERAL you had to select your source and destination as normal. Destination was changed so that it was the new History file. Under FILES you updated the DB and LOG files to reflect the new DB, otherwise you would over write the originals.
This is where they borked it. They didn't mention FILES only going to OPTIONS, and there is where they mentioned updating the file names. The main discovery was that you need to uncheck LEAVE Source database in the restoring state.
When I emailed them about removing the 'borked' databases I had created the called me back. I mentioned it to the fellow and we had a short discussion on the matter where he took notes and agreed that the directions were incorrect. When I got to the part about unchecking LEAVE Source database in the restoring state he mentioned that he uncheck Take tail-log backup before restore
Hope this helps.
