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 Development (2000)
 Resetting a database

Author  Topic 

JBelthoff
Posting Yak Master

173 Posts

Posted - 2005-12-08 : 00:06:49
Hi all,

Is there a way to reset a database to its original state. Like just after the database has been created?


Short of Dropping it and recreating it of course....

JB --

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 00:10:40
use truncate table on all your tables

-----------------
[KH]
Dec-05
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-08 : 00:28:30
Generate Scripts;Drop old Db;Run the Scripts;

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

JBelthoff
Posting Yak Master

173 Posts

Posted - 2005-12-08 : 06:07:19
Sorry guys but neither of these would work for me.

Truncate Table leaves the tables there and Droping the DB is an option that is almost impossible.

Here is my reasons for doing so.

I have a very cheap development sql server database that I use only to devlop and test my scripts. When I am done I clean out all of the objects I created for the last project by hand. Sometimes that can be quite a lot of mundane work.

I would love to be able to do something like the following.
exec sp_ResetToOriginal

DRopping the db also is not a real thing becasue as I said this is a cheap sql server and my administrator does not want to have to create dbases and add users all day just for me. I use this only to testing scripts before I put the projects into production.

Any help would be appreciated.

I already know about the one of spending more money! Hahaha!


Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-08 : 06:50:42
Do you want all Tables, views, functions and sps to be dropped?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-08 : 08:35:43
Setup the database to it's original state, and then make a backup of the database and save that backup file. When you want to return the database to its original state, do a restore from the saved backup file. To make it even easier, set the restore up as a job, and jusr run the job when you need it.


quote:
Originally posted by JBelthoff

Hi all,

Is there a way to reset a database to its original state. Like just after the database has been created?


Short of Dropping it and recreating it of course....

JB --




CODO ERGO SUM
Go to Top of Page
   

- Advertisement -