April 4, 2009 at 9:26 am
Is there any easy way to attach database files (more than 100) after dettaching it in the process of moving filed between the drives.
April 4, 2009 at 9:59 am
You could script the attaches,but you've have to know something about them. RESTORE HEADERLIST will give you some info.
Are these one database or multiple ones?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2009 at 10:54 am
Mike Levan (4/4/2009)
Is there any easy way to attach database files (more than 100) after dettaching it in the process of moving filed between the drives.
Basically do you want to move files from one drive to other and is for this purpose do you want to detach move file from one location to other and attach database.
If you are using version 2005 and above you don't have to detach, you can take database offline move file to new location alter database and then bring database online.
April 4, 2009 at 11:38 am
Mike Levan (4/4/2009)
Is there any easy way to attach database files (more than 100) after dettaching it in the process of moving filed between the drives.
have you had a look at undocumented stored procedure 'sp_MSforachdb' stored procedure? You could use that to detach and attach them.
April 4, 2009 at 1:13 pm
this script, found on this site, claims to reverse engineer the SQL to re-attach databases using create..for attach command
http://qa.sqlservercentral.com/scripts/Miscellaneous/30795/
haven't tried it myself.
---------------------------------------------------------------------
April 4, 2009 at 1:30 pm
Actually here is what i am trying to acheive..
I have PRoduction and Stage Server.
1. Restore production database "Employees" backup onto stage
2. Update stage with latest data
3. Backup stage and restore the backup file onto production with different name like Employeed_New.
4. Renaming Existing Employees database to Employess_Old
5. Again Rename Employeed_New back to original " Employees "
6 . After cheking integrity of production database delete Employess_Old.
What do you guys.. think. You have any automated scritps to do such jobs already. without doing them manually.
April 4, 2009 at 2:53 pm
Mike Levan (4/4/2009)
I have PRoduction and Stage Server.
1. Restore production database "Employees" backup onto stage
2. Update stage with latest data
3. Backup stage and restore the backup file onto production with different name like Employeed_New.
4. Renaming Existing Employees database to Employess_Old
5. Again Rename Employeed_New back to original " Employees "
6 . After cheking integrity of production database delete Employess_Old.
What do you guys.. think. You have any automated scritps to do such jobs already. without doing them manually.
I think this is not a common task.
If this activity is frequenty done in your environment it is better for you to come up with a script for this.
BTW, do you use any 3rd party software for backing up database or you use native?
April 4, 2009 at 3:01 pm
Here's the thing. What scale are you talking about? Because a script to check things automatically would require a bunch of testing from you, and in that time, you might as well do it manually. If you'd take a script from here and just run it, you might be asking for trouble.
If you are doing this regularly, then a script will help. It's not hard to script any of these actions. However you're want to standardize all the names for files, backups, etc. to keep it simple.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2009 at 8:00 pm
Mike Levan (4/4/2009)
Actually here is what i am trying to acheive..I have PRoduction and Stage Server.
1. Restore production database "Employees" backup onto stage
2. Update stage with latest data
3. Backup stage and restore the backup file onto production with different name like Employeed_New.
4. Renaming Existing Employees database to Employess_Old
5. Again Rename Employeed_New back to original " Employees "
6 . After cheking integrity of production database delete Employess_Old.
What do you guys.. think. You have any automated scritps to do such jobs already. without doing them manually.
or you can
1. Backup and Restore production database "Employees" onto stage
2. Update stage with latest data
3. Backup stage and restore the backup file onto production overwriting old database.
4. After checking integrity of production database if any issues restore prod backup taken in step1
Also, you may want to mark production database read only before starting these steps, this will avoid any data update on production during the process.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply