How can I freeze and restore database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How can I freeze and restore database

Author

Topic

eashoori
Starting Member

22 Posts

Posted - 2007-09-18 : 14:35:52

We are going to do some data mapping and conversion, what and how is the safest process to freeze the database and later restore it,
Thanks,
E

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-18 : 14:43:10

what do you mean by freeze? restrict access?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-18 : 14:44:42

What do you mean by Freeze? If you want it to be read only you can do:

USE MASTER
EXEC sp_dboption 'databasename', 'read only', 'True'

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 14:56:03

ALTER DATABASE <dbname> SET READ_ONLY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

eashoori
Starting Member

22 Posts

Posted - 2007-09-18 : 15:08:20

By Freezing I mean no action would be done on the users side so we can make sure everything has been accomplished. Then Restore the databse. Does SET READ-ONly enable that?
Thanx for any inforamtion

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-18 : 15:09:54

quote:
Originally posted by eashoori

By Freezing I mean no action would be done on the users side so we can make sure everything has been accomplished. Then Restore the databse. Does SET READ-ONly enable that?
Thanx for any inforamtion

Setting to read-only means no changes can be made to the database, and Brett's way does the same thing as mine but it is better because sp_dboption will not be supported in the future.

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 15:12:09

quote:
Originally posted by eashoori

By Freezing I mean no action would be done on the users side so we can make sure everything has been accomplished. Then Restore the databse. Does SET READ-ONly enable that?
Thanx for any inforamtion

Well you won't have to restore anything if you use my ALTER

Then app will just receive errors when an update is about to pccur

But the app should work just fine if no one is trying an insert update or delete

Thanks zoolander

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-18 : 23:25:07

If you don't want other users to access the db at all, can set it to dba use only.

eashoori
Starting Member

22 Posts

Posted - 2007-09-19 : 07:29:27

Brett and Zolaster, If I use Brett's Alter, how can I recover the database after all the data mapping?
Thanx for all the info
Ellie

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 07:44:15

"how can I recover the database after all the data mapping? "

If the database is read only nothing will have changed.

If you DO want to make some, trial, changes then:

Take a FULL Backup of the database

Make experiments

Restore from the Full Backup file.

Kristen

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-20 : 12:54:34

I think you can start database in single user mode and start to restore it, so no one can access

SQL IN Minds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources