| Author |
Topic |
|
pithhelmet
Posting Yak Master
183 Posts |
 Posted - 2007-12-31 : 12:00:18
|
| Hi everyone -Is there a way to take a database offline from within a job,and bring it back online within the same job??A new directive has come down, and a copy of the .mdf and .ldf are required to be taken to secure location once a week.I was thinking a simple take offline, make a DOS copy of the .ldf and .mdf onto an external drive, and bring back online once the copy is completed would do the deed.If there are users connected, simply boot them off.thankstony |
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2007-12-31 : 12:13:35
|
| alter database mydb set offlineDonn Policarpio |
 |
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2007-12-31 : 12:45:33
|
| thanks for the post!!does anyone have any comments on this "solution"I understand the users will be lost if the database is attached toanother machine, but that is "ok with me" signed by the mgmt |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-31 : 13:54:06
|
| The users will not be lost if you follow a proper procedure to copy them over. Who put this new directive in place? Aren't you performing hot backups? Cold backups are almost never done in SQL Server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2007-12-31 : 14:34:47
|
| Hi Tara,tried the offline, copy, attach routine, everything went ok...mgmt is happy about thatnow they can have a copy of the databases at their house in case anything happens.the trouble now is one of the actual databases is 21gb, and it takes more than two minutes to do a physical copy...so the sql job times out... i'm looking into this issue.take caretony |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-31 : 15:19:55
|
| Management should be given backups "in case anything happens" not the database files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-12-31 : 17:16:25
|
quote:
Originally posted by pithhelmet
...now they can have a copy of the databases at their house in case anything happens.
Until thier pc gets whacked with a really nice trojan/zombie and all that database information is now belonging to someone else. This is a data security nightmare waiting to happen....Ask management to get a proper secure off-site storage mechanisim setup. It's not all that expensive and is easily done these days. |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2008-01-02 : 08:57:51
|
| Haywood,Thanks for posting....but in all reality - that is NOT my problem.... i honestly don't think it will ever be used, they simply want a "warm and fuzzy" knowing that the data is in a semi-usable format sitting in a fireproof safe somewhere.thanks again for the reply |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-02 : 11:45:04
|
| And that's exactly why you should be providing backups to them and not the database files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2008-01-02 : 12:02:35
|
| Hi Tara and everyone -They are receiving normal database backups, this is not the problem,mgmt simply wants a copy of the .mdf and .ldf files on an external hard drive.like i said, a little "warm and fuzzy"... people rise to the level of incompetance (sp)and the best mgmt can do is attach and detach a database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-02 : 12:47:02
|
| The DBA needs to explain things to management though and in this scenario whoever the DBA is needs to tell management how ridiculous this is. Why cause unnecessary downtime? Management shouldn't even know about detach/attach. All they should care about is having the files required to recover a database. And those files would be the backups.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2008-01-03 : 09:45:18
|
| Hi Tara -I agree completely - the DBA has explained this to the mgmt, and mgmt still wants the .mdf and .ldf files on an external hard drive to keep at his house.he signs the checks - i do what he asks.There are certain problems when a programmer moves into the mgmt chair.people complain that mgmt often has no idea on technology, but there are trulya world of problems when a programmer moves into the mgmt chair as well.so for now - i will copy the .mdf and .ldf files to an external hard driveand move on to the next challenge.thanks again for everyone help!! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-03 : 12:13:33
|
| so what happens if you detach your db, and can't attach it any longer?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2008-01-03 : 15:29:34
|
| if you cannot re-attach, then your hosed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-03 : 15:39:04
|
| And that's precisely why you should not be doing this on a scheduled basis. Not only are you adding extra downtime each time this process runs, you are also greatly increasing your risk of a major production nightmare. Backup/Restore is the only way to go here. And my management would listen to me as I would be adamant about it. But it's your environment and you're the one who has to cleanup the mess, so good luck!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-03 : 15:46:46
|
quote:
Originally posted by pithhelmet
if you cannot re-attach, then your hosed
that's exactly Tara's, mine and everyone elses point in this thread._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2008-01-04 : 09:34:24
|
| This is not a topic of discussion any more - mgmt wants a physical copy of the databases - they get a copy of the databases.I am glad your mgmt listens to you tara, but some of us do not live in a world that mgmt listens or even cares about anything anyone says - so good for you!I honestly don't give flying F about the could happen and might happen cases - mgmt wants to have a copy of the database at his house, he gets a copy. Will mgmt be able to put the data back into production once it leaves - hell no, will the device even be allowed into the NAP - hell no. FUD responses wasted.The daily job to unattach and copy was ended - i have installed replistor, and i am able to make a copy of the replistor copy and move it to the external drive.The transaction log, differential and weekely full backups are proceding as usual.The backuped data is being written to DVD and rolled to iron mountain as usual.thanks for your replies!take caretony |
|
|
|
|
Taking a database offline