SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Taking a database offline
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pithhelmet
Posting Yak Master

183 Posts

Posted - 12/31/2007 :  12:00:18  Show Profile  Reply with Quote
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.

thanks
tony

donpolix
Yak Posting Veteran

97 Posts

Posted - 12/31/2007 :  12:13:35  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message  Reply with Quote
alter database mydb set offline

Donn Policarpio
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 12/31/2007 :  12:45:33  Show Profile  Reply with Quote
thanks for the post!!

does anyone have any comments on this "solution"

I understand the users will be lost if the database is attached to
another machine, but that is "ok with me" signed by the mgmt

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 12/31/2007 :  13:54:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 12/31/2007 :  14:34:47  Show Profile  Reply with Quote
Hi Tara,

tried the offline, copy, attach routine, everything went ok...

mgmt is happy about that
now 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 care
tony


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 12/31/2007 :  15:19:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Management should be given backups "in case anything happens" not the database files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Haywood
Posting Yak Master

USA
221 Posts

Posted - 12/31/2007 :  17:16:25  Show Profile  Reply with Quote
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.
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 01/02/2008 :  08:57:51  Show Profile  Reply with Quote
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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 01/02/2008 :  11:45:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
And that's exactly why you should be providing backups to them and not the database files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 01/02/2008 :  12:02:35  Show Profile  Reply with Quote
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.



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 01/02/2008 :  12:47:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 01/03/2008 :  09:45:18  Show Profile  Reply with Quote
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 truly
a 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 drive
and move on to the next challenge.

thanks again for everyone help!!


Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/03/2008 :  12:13:33  Show Profile  Visit spirit1's Homepage  Reply with Quote
so what happens if you detach your db, and can't attach it any longer?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 01/03/2008 :  15:29:34  Show Profile  Reply with Quote

if you cannot re-attach, then your hosed

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 01/03/2008 :  15:39:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/03/2008 :  15:46:46  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 01/04/2008 :  09:34:24  Show Profile  Reply with Quote
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 care
tony


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000