July 1, 2011 at 6:52 am
I want to create a proc that restores a database every week. Lets say i want to restore a database "Employee" every sunday, here are the steps i want but would like to know how i can do in a proc.
1. Find the drive letter for existing "Employee" databse and restore the new database as "Employee_new" on a different drive.
2.Drop database "Employee_old" if existing.
3.Rename Existing "Employee" database to "Employee_old"
4.Rename "Employee_new" to "Employee"
Is it possible to do this in a single transaction?
July 1, 2011 at 11:01 am
Tara-1044200 (7/1/2011)
I want to create a proc that restores a database every week. Lets say i want to restore a database "Employee" every sunday, here are the steps i want but would like to know how i can do in a proc.1. Find the drive letter for existing "Employee" databse and restore the new database as "Employee_new" on a different drive.
2.Drop database "Employee_old" if existing.
3.Rename Existing "Employee" database to "Employee_old"
4.Rename "Employee_new" to "Employee"
Is it possible to do this in a single transaction?
All the stuff I bolded in your post can be handled using the WITH REPLACE in combination with the WITH MOVE options of a single RESTORE DATABASE command.
I couldn't find anything regarding what happens if the restore fails when using the REPLACE option, i.e. whether the database being replaced is left intact or whether the database being replaced is now unusable from the failed restore.
Depending on your motivation for wanting "transactions" this approach may not work for you.
http://technet.microsoft.com/en-us/library/ms186858.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 12:08 pm
Tara-1044200 (7/1/2011)
I want to create a proc that restores a database every week. Lets say i want to restore a database "Employee" every sunday, here are the steps i want but would like to know how i can do in a proc.1. Find the drive letter for existing "Employee" databse and restore the new database as "Employee_new" on a different drive.
2.Drop database "Employee_old" if existing.
3.Rename Existing "Employee" database to "Employee_old"
4.Rename "Employee_new" to "Employee"
Is it possible to do this in a single transaction?
If there is a newly added LogicalName (Physical file), your process will fail for sure. You'd better use RESTORE FILELISTONLY to get the file list combined with current file list to do the job. 🙂
July 1, 2011 at 12:13 pm
Wildcat (7/1/2011)
Tara-1044200 (7/1/2011)
I want to create a proc that restores a database every week. Lets say i want to restore a database "Employee" every sunday, here are the steps i want but would like to know how i can do in a proc.1. Find the drive letter for existing "Employee" databse and restore the new database as "Employee_new" on a different drive.
2.Drop database "Employee_old" if existing.
3.Rename Existing "Employee" database to "Employee_old"
4.Rename "Employee_new" to "Employee"
Is it possible to do this in a single transaction?
If there is a newly added LogicalName (Physical file), your process will fail for sure. You'd better use RESTORE FILELISTONLY to get the file list combined with current file list to do the job. 🙂
True, it depends on just how dynamic you need it to be. Typically a DBA controls both sides of the equation (the file list and the restore job) so would know to change one when the other needs changing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply