| Author |
Topic |
|
lauriejanesmith
Starting Member
5 Posts |
 Posted - 2008-01-25 : 08:02:48
|
| HiWonder if anyone can help please?We receive transaction logs from a company that we need to restore to our database and want to automate it rather than restoring eachone individually through the guiI have written a script but it falls over because Ive copied the contents off the filelist into it to make it quicker than typing eachone in, which is fine when all the files are their but often they dont send all teh files that are in the filelist, therefore script fails saying "Msg 3013, Level 16, State 1, Line 30 RESTORE LOG is terminating abnormally" any ideas?Other idea is Ive put the contents of the directory where the logs get copied to, into a temporary table but now not sure how to script this in SQL to restore contents of the table to my database, any ideas?ThanksLaurie |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-25 : 23:56:49
|
| select 'restore log db_name from disk = ''path\''' + name + ' with ...' from table ...That'll generate restore statements for you. |
 |
|
|
lauriejanesmith
Starting Member
5 Posts |
Posted - 2008-02-04 : 06:20:45
|
| HiThankyou, works greatHow do I need execute these statements? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-04 : 23:07:16
|
| Run them in query window in ssms. |
|
|
|
lauriejanesmith
Starting Member
5 Posts |
Posted - 2008-02-05 : 10:29:55
|
| Hmmm ok thankyou but how do I do that? Do you mean script the table to a query and run? I did want to try and automate this somehow :) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-05 : 10:44:58
|
quote:
Originally posted by lauriejanesmith
Hmmm ok thankyou but how do I do that? Do you mean script the table to a query and run? I did want to try and automate this somehow :)
You can use a cursor to execute the restore commands in a loop, once you have a result set with the transaction log file names in the correct order.CODO ERGO SUM |
|
|
|
lauriejanesmith
Starting Member
5 Posts |
Posted - 2008-02-05 : 10:51:51
|
| Tried scripting it no good as your command just give results in a query window it doesnt execute them, I need to get these statements into a form that I can execute? :( |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-05 : 23:06:40
|
| You generate restore statements with select 'restore log db_name from disk = ''path\''' + name + ' with ...' from table ...Then copy results to query window and execute them. |
|
|
|
lauriejanesmith
Starting Member
5 Posts |
Posted - 2008-02-06 : 09:57:14
|
| ok thanks, havent tried the cursor option yetIt would be no good copying results to a query window because i need the script to do this for me everyday, think Im back to where I started :(Another couple of questions1)How do you send what is in the results tab to a log file?2)When restoring a back one day and transactions the next day, all goes ok, but when the third days comes sql doesnt like the next set of transaction logs, it complains the database was left in a recover state and expects to see it in a non-recovered state, you have to put with recover on the last transaction log else the database is left in a locked state, the next set of transaction logs need to be with norecovery, so what we are having to do is restore the backup everyday and then the transactions as well, so by friday there is 4 days, very quick to do so doesnt matter, its just I would expect there being a better whay of doing this :) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-06 : 23:56:34
|
| 1. Run query with sqlcmd in dos, you can put result in output file with -o option.2. Have to restore full backup then log backups again. |
|
|
|
|
RESTORE LOG - transaction files