Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
teepee
Starting Member
6 Posts |
 Posted - 2006-02-13 : 04:20:38
|
| I keep getting told that my parameters are wrong when I run more than one line in restore script.Given that I have a the following files:customers200602031000.bak customers200602031100.trncustomers200602031200.trncustomers200602031300.trn......How to make a T-SQL script that will restore it all for me? It has to be a script, because I have tried to do it from EM, but I have no history on the "clean" machine I'm trying to restore on, and therefore it seems I have to select one file at a time and that takes forever...Thx. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-02-13 : 04:27:24
|
| i don't have the script with me but follow the flow:1. pick up the files from the directory using xp_cmdshell dir, saving in temporary table2. extract only those files that you need 3. loop through the table for the restore, check restore script in BOL - each restore should be standby or norecovery option then issue the recovery option for the last trn filesince you have a naming convention, the files will get picked as per date stamp, correct?HTH--------------------keeping it simple... |
 |
|
|
teepee
Starting Member
6 Posts |
Posted - 2006-02-13 : 04:40:28
|
quote:
- each restore should be standby or norecovery option then issue the recovery option for the last trn file
It's exactly here that my parameter knowledge fails. I simply dont know what parameters to put where in the script. That's why I would like a sample script. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
|
|
teepee
Starting Member
6 Posts |
Posted - 2006-02-13 : 05:22:59
|
| This will do it if new filestructure is like on old machine, right?RESTORE DATABASE [Customers] FROM DISK = N'd:\BACKUP\customers200602031000.bak' WITH FILE = 1, NORECOVERY RESTORE LOG [Customers] FROM DISK = N'd:\BACKUP\customers200602031100.trn' WITH FILE = 1, NORECOVERYRESTORE LOG [Customers] FROM DISK = N'd:\BACKUP\customers200602031200.trn' WITH FILE = 1, NORECOVERYRESTORE LOG [Customers] FROM DISK = N'd:\BACKUP\customers200602031300.trn' WITH FILE = 1, RECOVERY |
|
|
|
Kristen
Test
22859 Posts |
|
|
Pidi
Starting Member
5 Posts |
Posted - 2006-03-07 : 11:19:00
|
| This thread brought me to the idea, to write a little VB script, to generate this TSQL script automatically from the directory, that holds all the TRN files.For a long time I have been thinking about what to do, if my SQL Server crashes, and I have to rebuild the database from a full backup plus all TRN files that have been save since the last full backup. I never had to try, but I was told, that for a new and "clean" instance of the SQL Server I would have to select each TRN file manually, which even for one day would be a pitty, since I do a transaction log backup every 10 minutes.The VB script I wrote, is a simple piece of code, that browses through the directory, which holds the TRN files, loads the file names to an array, does a simple bubble sort on date/time of those files, and then writes all files for the current day plus the TSQL commands I learned in this thread to an ASCII file, that I calles "RestoreTRN.sql"If someone is interested in that script, sen me an e-mail or tell me where to upload it within this forum.Thanks for the idea!PidiNew owner of SQl Server for Navision |
|
|
|
|
|
|
|
|
Help create restore script