0

We're trying to restore a database (created programatically by running a hand-crafted SQL script we have). Our backup routine is to create a full backup of every database on the SQL Server 2008 instance on a Saturday then automatic transaction logs (I assume these are created automatically anyway - we appear to have lots of log files, possibly one per transaction after the full backup was taken?).

On Tuesday this week the database in question was dropped and another one with the exact same name and schema was created. SQL Server has continued to create transaction log files but it hasn't had chance to create a new full backup (that won't happen until next Saturday).

Now as it turns out we need to restore the database to how it was on Thursday. This is after the "drop and re-create". My question is, is this possible? If it isn't, what exactly does SQL Server think that it's writing to those transaction logs created since the drop and re-create? (I understood they were kind of files containing a binary delta, which makes me think maybe we can restore from them?)

I'm no DBA but then neither is our IT department, so I'm doing the best I can to resolve this. Any advice much appreciated!

Ben
  • 115
  • 5
  • Could you provide a snippet of the backup scripts that you are running - it will help identify exactly what backups you should have. – Chris W Jan 07 '11 at 08:46

2 Answers2

3

No you won't be able to do a restore of the "new" database as SQL Server needs a full backup of the database on to which it can then apply the subsequent log backups.

Whilst you may have used the same DB/schema names the DB itself is a different object/entity as far as SQL Server is concerned so you won't be able to use your original full backup and then replay all logs to it. You should have taken a full backup of your DB immediately after creating it.

Hopefully this is just a test system you're referring to... sounds like you could do with someone doing some basic DB admin training. Doing basic SQL Server backup/restore isn't hard to learn in an hour or two if you do some practice on a test system and it'll quickly help you understand what all the files are that are getting produced.

EDIT:

Further to this... I've just thought about your comment that SQL is still creating logs. You've got something else going on here that what you think is happening. If you dropped the DB but haven't taken a full backup of the new database SQL Server would throw errors when you try to take the log backups. It won't let you backup the log without having first taken an original full backup. You need to verify your backups scripts as either the log backups were throwing errors or you're not even taking log backups and are looking at something else entirely.

Chris W
  • 2,670
  • 1
  • 23
  • 32
0

Restore your lastest full backup to a new database of the same name and apply your transaction logs to get you back to point-in-time.

Gerald Zimmerman
  • 16