December 10, 2015 at 3:01 pm
I favor restoring Databases using T-SQL.
Unfortunately in this situation there are Transaction Log Backups that occur every 5 minutes so T-SQL is not an option.
There is a way that you Select the full Backup and SQL Server backs up the latest differential backup and all of the transaction log backups. Specify a point in time and SQL Server restore all of the Backup files up to the last transaction log backup.
Does anyone know where I can find instructions with screen shots?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2015 at 6:49 am
December 11, 2015 at 7:10 am
heymiky (12/11/2015)
This may help you out:
Thank you.
That is helpful.
I was looking to generate a restore script via SSMS.
I need to change the name of the Database in some cases so that I can restore to a different Database and extract the data for a table.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2015 at 8:03 am
Assuming you want to create the script in SSMS on the same server where the backups are taken from, take a look at the backup tables in MSDB to dynamically build the script.
December 11, 2015 at 8:40 am
Search automatic restore, or restore all files here. Lots of scripts that you can use or might help you with a base to modify.
December 12, 2015 at 7:33 pm
Welsh Corgi (12/11/2015)
heymiky (12/11/2015)
This may help you out:Thank you.
That is helpful.
[font="Arial Black"]I was looking to generate a restore script via SSMS.
[/font]
I need to change the name of the Database in some cases so that I can restore to a different Database and extract the data for a table.
Thanks again.
It's simple. Go through all of the steps to do a point in time restore using the following steps (https://technet.microsoft.com/en-us/library/ms190982(v=sql.105).aspx) BUT DO NOT CLICK "OK". Instead, look for the little document ICON near the top of the restore window that has the word "Script" next to it. Click that and a script will be generated in a new SSMS code window. Then, remember to hit "CANCEL" instead of "OK" to get out of the restore window.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2015 at 2:19 pm
Jeff Moden (12/12/2015)
Welsh Corgi (12/11/2015)
heymiky (12/11/2015)
This may help you out:Thank you.
That is helpful.
[font="Arial Black"]I was looking to generate a restore script via SSMS.
[/font]
I need to change the name of the Database in some cases so that I can restore to a different Database and extract the data for a table.
Thanks again.
It's simple. Go through all of the steps to do a point in time restore using the following steps (https://technet.microsoft.com/en-us/library/ms190982(v=sql.105).aspx) BUT DO NO CLICK "OK". Instead, look for the little document ICON near the top of the restore window that has the word "Script" next to it. Click that and a script will be generated in a new SSMS code window. Then, remember to hit "CANCEL" instead of "OK" to get out of the restore window.
Once you use this approach to generate the script, you should understand the steps necessary to do so. After that, use the backup tables in MSDB to generate a script from scratch using the most recent full and log backups after that.
December 13, 2015 at 7:17 pm
Ed Wagner (12/13/2015)
Jeff Moden (12/12/2015)
Welsh Corgi (12/11/2015)
heymiky (12/11/2015)
This may help you out:Thank you.
That is helpful.
[font="Arial Black"]I was looking to generate a restore script via SSMS.
[/font]
I need to change the name of the Database in some cases so that I can restore to a different Database and extract the data for a table.
Thanks again.
It's simple. Go through all of the steps to do a point in time restore using the following steps (https://technet.microsoft.com/en-us/library/ms190982(v=sql.105).aspx) BUT DO NO CLICK "OK". Instead, look for the little document ICON near the top of the restore window that has the word "Script" next to it. Click that and a script will be generated in a new SSMS code window. Then, remember to hit "CANCEL" instead of "OK" to get out of the restore window.
Once you use this approach to generate the script, you should understand the steps necessary to do so. After that, use the backup tables in MSDB to generate a script from scratch using the most recent full and log backups after that.
Here's a good place to start learning how to do that. Understand that the script does not detect or use DIF files. As the author states, there are a couple of other nuances you might want to research and add to the script but it's a great place to start learning.
https://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/
I'm currently adding enhancements to the script. The code will poke a bot that will get into a car, drive to the offsite tape storage facility, grab the correct tapes and the tape set just before that incase something goes wrong, stop at the local packy to pick up a six, stop at Jimmy Johns for a pre-ordered sandwich, return to the office, drop the six and the sandwich off at my desk, then let itself into the server room to mount the correct tape and change it if need be.
Once complete, the bot will come back out to my desk and give me a backrub for having such a difficult day. On really bad days, I've programmed it to dress up like me, put on a face mask that looks like me, sit at my desk letting out the occasional "WTF!?" and occasional old man smell and no one will know I'm gone. They'll think I'm doing code reviews and won't bother "me" to find out.
The only problem I'm having so far is that the bot has developed a taste for cigarettes... MY cigarettes.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2015 at 9:11 am
Jeff Moden (12/13/2015)
I'm currently adding enhancements to the script. The code will poke a bot that will get into a car, drive to the offsite tape storage facility, grab the correct tapes and the tape set just before that incase something goes wrong, stop at the local packy to pick up a six, stop at Jimmy Johns for a pre-ordered sandwich, return to the office, drop the six and the sandwich off at my desk, then let itself into the server room to mount the correct tape and change it if need be.Once complete, the bot will come back out to my desk and give me a backrub for having such a difficult day. On really bad days, I've programmed it to dress up like me, put on a face mask that looks like me, sit at my desk letting out the occasional "WTF!?" and occasional old man smell and no one will know I'm gone. They'll think I'm doing code reviews and won't bother "me" to find out.
The only problem I'm having so far is that the bot has developed a taste for cigarettes... MY cigarettes.
Man, those T-SQL enhancements in 2016 are really something!
December 14, 2015 at 10:15 am
Gazareth (12/14/2015)
Jeff Moden (12/13/2015)
I'm currently adding enhancements to the script. The code will poke a bot that will get into a car, drive to the offsite tape storage facility, grab the correct tapes and the tape set just before that incase something goes wrong, stop at the local packy to pick up a six, stop at Jimmy Johns for a pre-ordered sandwich, return to the office, drop the six and the sandwich off at my desk, then let itself into the server room to mount the correct tape and change it if need be.Once complete, the bot will come back out to my desk and give me a backrub for having such a difficult day. On really bad days, I've programmed it to dress up like me, put on a face mask that looks like me, sit at my desk letting out the occasional "WTF!?" and occasional old man smell and no one will know I'm gone. They'll think I'm doing code reviews and won't bother "me" to find out.
The only problem I'm having so far is that the bot has developed a taste for cigarettes... MY cigarettes.
Man, those T-SQL enhancements in 2016 are really something!
If the procedure is developing a taste for cigarettes, it means that the procedure is learning. Jeff, I hope you aren't going to cause the rise of the machines. 😉
December 16, 2015 at 5:33 pm
heymiky (12/11/2015)
This may help you out:
Thanks for the script.
Does it only script out the transaction Logs since the last Diff?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply