Is there an easy way to get Northwind and Pubs back to their default state,
assuming that you were too stupid to back them up before you started mucking
around with them? Thanks.You can recreate the two databases from the two scripts: instpubs.sql, and
instnwnd.sql from the \install folder of your SQL Server installation or
the SQL Server CD.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Alternatively, you can try attach Nothwind and pubs databases from the SQL
2K CD:
x86\DATA\northwnd.mdf
x86\DATA\northwnd.ldf
x86\DATA\pubs.mdf
x86\DATA\pubs_log.ldf
Richard
"Garrett Fitzgerald" <gfitzger@.nyx.net> wrote in message
news:uO$K4EbQDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get Northwind and Pubs back to their default
state,
> assuming that you were too stupid to back them up before you started
mucking
> around with them? Thanks.
>
>|||Alan,
>> You also have to run a batch utility...
Thanks Alan. I wasnt aware of that.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:uwBiD8kQDHA.1564@.TK2MSFTNGP12.phx.gbl...
> You also have to run a batch utility to fully populate the data in pubs.
> Northwind can be resinstalled by just using the instnwnd.sql script.
> The processes to create pubs and Northwind are covered in these
Transact-SQL
> Reference topics:
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_pubs_2v8l.asp?frame=true
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
> --
> Alan Brewer [MSFT]
> Lead Technical Writer
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Monday, March 26, 2012
Restoring state of database after running a Unit test
Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
Restoring state of database after running a Unit test
Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.
Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.
Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
Subscribe to:
Posts (Atom)