Hello:
I am having a problem with a production database that has
run out of space. The database was configured by the
sofware manufacturer with 269mb of allocated space. The
current size is 450mb with 0% free space available. The
database has the Autogrow option turned on and is set for
Unrestricted file growth. There are 48GB of free space
available on the physical disk on which the database
resides. None of my users have reported any problems so
far.
I have a monitoring tool that is throwing up an alarm
because of this problem. I have shrank the database
several times, indicating that I want to have 10% free
space available after shrinking. Whenever I check the
database properties however, the sizes remain the same.
How can I correct this issue before it becomes a real
problem.
Thanks for any assistance
BrennanThis is a multi-part message in MIME format.
--=_NextPart_000_0427_01C3CE18.E52CA0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Well, 48GB is just about 10% of 450GB. I would start looking for more disk
space, unless you can actually purge data out of your database.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:00e101c3ce41$563d7450$a501280a@.phx.gbl...
Hello:
I am having a problem with a production database that has
run out of space. The database was configured by the
sofware manufacturer with 269mb of allocated space. The
current size is 450mb with 0% free space available. The
database has the Autogrow option turned on and is set for
Unrestricted file growth. There are 48GB of free space
available on the physical disk on which the database
resides. None of my users have reported any problems so
far.
I have a monitoring tool that is throwing up an alarm
because of this problem. I have shrank the database
several times, indicating that I want to have 10% free
space available after shrinking. Whenever I check the
database properties however, the sizes remain the same.
How can I correct this issue before it becomes a real
problem.
Thanks for any assistance
Brennan
--=_NextPart_000_0427_01C3CE18.E52CA0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Well, 48GB is just about 10% of =450GB. I would start looking for more disk space, unless you can actually =purge data out of your database.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:00e101c3ce41$56=3d7450$a501280a@.phx.gbl...Hello:I am having a problem with a production database that has run out of space. The database was configured by the sofware manufacturer =with 269mb of allocated space. The current size is 450mb with 0% =free space available. The database has the Autogrow option turned on and =is set for Unrestricted file growth. There are 48GB of free space =available on the physical disk on which the database resides. None of my =users have reported any problems so far.I have a monitoring tool that =is throwing up an alarm because of this problem. I have shrank =the database several times, indicating that I want to have 10% free =space available after shrinking. Whenever I check the database =properties however, the sizes remain the same.How can I correct this issue =before it becomes a real problem.Thanks for any assistanceBrennan
--=_NextPart_000_0427_01C3CE18.E52CA0B0--|||Hi Tom:
The DB is 450MB not 450GB. I have plenty of space
available on the volume. Total size is 90+GB and 48GB
are free.
Am I missing something?
Brennan
>--Original Message--
>Well, 48GB is just about 10% of 450GB. I would start
looking for more disk
>space, unless you can actually purge data out of your
database.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00e101c3ce41$563d7450$a501280a@.phx.gbl...
>Hello:
>I am having a problem with a production database that has
>run out of space. The database was configured by the
>sofware manufacturer with 269mb of allocated space. The
>current size is 450mb with 0% free space available. The
>database has the Autogrow option turned on and is set for
>Unrestricted file growth. There are 48GB of free space
>available on the physical disk on which the database
>resides. None of my users have reported any problems so
>far.
>I have a monitoring tool that is throwing up an alarm
>because of this problem. I have shrank the database
>several times, indicating that I want to have 10% free
>space available after shrinking. Whenever I check the
>database properties however, the sizes remain the same.
>How can I correct this issue before it becomes a real
>problem.
>Thanks for any assistance
>Brennan
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_04D6_01C3CE1E.D1605D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Oops, sorry about that. It looks then that you will soon reach a point
where if someone goes to add data, it will have to autogrow. All pending
inserts - and maybe some updates - will have to wait until that happens.
I'd just expand the database by, say, 100 MB, so that the users wouldn't
have to wait for the autogrow to complete.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<anonymous@.discussions.microsoft.com> wrote in message
news:010101c3ce47$61893e10$a601280a@.phx.gbl...
Hi Tom:
The DB is 450MB not 450GB. I have plenty of space
available on the volume. Total size is 90+GB and 48GB
are free.
Am I missing something?
Brennan
>--Original Message--
>Well, 48GB is just about 10% of 450GB. I would start
looking for more disk
>space, unless you can actually purge data out of your
database.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00e101c3ce41$563d7450$a501280a@.phx.gbl...
>Hello:
>I am having a problem with a production database that has
>run out of space. The database was configured by the
>sofware manufacturer with 269mb of allocated space. The
>current size is 450mb with 0% free space available. The
>database has the Autogrow option turned on and is set for
>Unrestricted file growth. There are 48GB of free space
>available on the physical disk on which the database
>resides. None of my users have reported any problems so
>far.
>I have a monitoring tool that is throwing up an alarm
>because of this problem. I have shrank the database
>several times, indicating that I want to have 10% free
>space available after shrinking. Whenever I check the
>database properties however, the sizes remain the same.
>How can I correct this issue before it becomes a real
>problem.
>Thanks for any assistance
>Brennan
>
>
--=_NextPart_000_04D6_01C3CE1E.D1605D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#d4d0c8>
<DIV><FONT face=3DTahoma size=3D2>Oops, sorry about that. It looks =then that you will soon reach a point where if someone goes to add data, it will =have to autogrow. All pending inserts - and maybe some updates - will have =to wait until that happens. I'd just expand the database by, say, 100 MB, =so that the users wouldn't have to wait for the autogrow to =complete.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT> </DIV>
<DIV> </DIV>
<DIV><<A href=3D"mailto:anonymous@.discussions.microsoft.com">anonymous@.discussions=.microsoft.com</A>> wrote in message <A href=3D"news:010101c3ce47$61893e10$a601280a@.phx.gbl">news:010101c3ce47$61=893e10$a601280a@.phx.gbl</A>...</DIV>Hi Tom:<BR><BR>The DB is 450MB not 450GB. I have plenty of =space <BR>available on the volume. Total size is 90+GB and 48GB <BR>are free.<BR><BR>Am I missing =something?<BR><BR>Brennan<BR><BR><BR>>--Original Message--<BR>>Well, 48GB is just about 10% of 450GB. I would =
start <BR>looking for more disk<BR>>space, unless you can =actually purge data out of your <BR>database.<BR>><BR>>--<BR>>Tom<BR>><BR>>--=---<BR>--<BR>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>>SQL Server MVP<BR>>Columnist, =SQL Server Professional<BR>>Toronto, ON Canada<BR>>www.pinnaclepublishing.com/sql<BR>><BR>><BR>>"Bren=nan" <<A href=3D"mailto:anonymous@.discussions.microsoft.com">anonymous@.discussions=.microsoft.com</A>> wrote in <BR>message<BR>>news:00e101c3ce41$563d7450$a501280a@.phx.gbl...<BR>>=Hello:<BR>><BR>>I am having a problem with a production database that has<BR>>run out =of space. The database was configured by the<BR>>sofware =manufacturer with 269mb of allocated space. The<BR>>current size is 450mb with 0% =free space available. The<BR>>database has the Autogrow option =turned on and is set for<BR>>Unrestricted file growth. There are 48GB of free space<BR>>available on the physical disk on which the database<BR>>resides. None of my users have reported any =problems so<BR>>far.<BR>><BR>>I have a monitoring tool that is throwing =up an alarm<BR>>because of this problem. I have shrank the database<BR>>several times, indicating that I want to have 10% free<BR>>space available after shrinking. Whenever I check the<BR>>database properties however, the sizes remain the same.<BR>><BR>>How can I correct this issue before it becomes a real<BR>>problem.<BR>><BR>>Thanks for any assistance<BR>><BR>>Brennan<BR>><BR>><BR>><BR>></BODY><=/HTML>
--=_NextPart_000_04D6_01C3CE1E.D1605D00--
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Sunday, March 25, 2012
Wednesday, March 21, 2012
Database setting
. The BPA recommend that the model database setting for the items below be set to on. I can accomplish this task through the query analyzer and run the set command. (Set ANSI_NULLS on). The response is positive but when I re-run the report the setting are back off.
Why?
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULLDon't the setting only last for the scope of the session?
That's why they have to be coded inside the sporcs?
I'll have to look a more defenitive answer...but I'll just be fgetting from BOL|||Garry,
Those settings are only taking effect for that single session i.e. within Query Analyzer.
To make any permanent changes to the model database, you need to right click it in Query Analyzer and check the options under properties. You should save a copy of the database first in case you should find that you need to return to the default settings.
Please note carefully this article in case you need to reattach your model database:
http://support.microsoft.com/?id=224071
We would advise leaving the model database at default settings.
Use the following from Query Analyzer to check the settings:
Sp_helpdb
And also check for databaseproperty in Books Online
Syntax
DATABASEPROPERTY( database , property )
USE master
SELECT DATABASEPROPERTY('model', 'IsANSINullDEFAULT')|||Well its up to you, but most people would leave the model database alone. It depends on your particular needs more than anything else. Remember the model database is only a template used to create new databases, so if you are bringing databases to this machine from another server, then the model database settings will have no effect.
Any conflict here is due to ANSI compatibility levels. SQL Server does not always default to the ANSI compatible levels.
Please review this article for a note on the various database settings and their effects:-
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp
These two commands give you information on your current connection details, and on the database settings that may be configured respectively:-
sp_dboption
dbcc useroptions|||why did you post the question if you already had the answer?|||Perhaps it was a rhetorical question?
Read the top of his posts. He was just copying information from MicroCoughed.sql
Why?
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULLDon't the setting only last for the scope of the session?
That's why they have to be coded inside the sporcs?
I'll have to look a more defenitive answer...but I'll just be fgetting from BOL|||Garry,
Those settings are only taking effect for that single session i.e. within Query Analyzer.
To make any permanent changes to the model database, you need to right click it in Query Analyzer and check the options under properties. You should save a copy of the database first in case you should find that you need to return to the default settings.
Please note carefully this article in case you need to reattach your model database:
http://support.microsoft.com/?id=224071
We would advise leaving the model database at default settings.
Use the following from Query Analyzer to check the settings:
Sp_helpdb
And also check for databaseproperty in Books Online
Syntax
DATABASEPROPERTY( database , property )
USE master
SELECT DATABASEPROPERTY('model', 'IsANSINullDEFAULT')|||Well its up to you, but most people would leave the model database alone. It depends on your particular needs more than anything else. Remember the model database is only a template used to create new databases, so if you are bringing databases to this machine from another server, then the model database settings will have no effect.
Any conflict here is due to ANSI compatibility levels. SQL Server does not always default to the ANSI compatible levels.
Please review this article for a note on the various database settings and their effects:-
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp
These two commands give you information on your current connection details, and on the database settings that may be configured respectively:-
sp_dboption
dbcc useroptions|||why did you post the question if you already had the answer?|||Perhaps it was a rhetorical question?
Read the top of his posts. He was just copying information from MicroCoughed.sql
Monday, March 19, 2012
Database Script Does Not Account for Dependencies
I don't know if I have missed something here, but I blindly thought that if
I used SSMS to script my database, I could run the script on a new instance
and get a database out of it.
The script fails, and I get an error saying that a stored procedure requires
an object that doesn't exist. That is because the object hasn't been created
yet. It appears later on in the script.
How can I get the database scripting wizard to script objects in the correct
order, that is in an order that takes account of dependencies?
Either that, or does anyone know of a tool/product that does do this?
TIA
CharlesUsually, I put my scripts together manually, because this issue troubled me
and I never got around to trying to find a solution for it. I did try
checking off "Generate scripts for all dependent objects" and that did not
help at all, although it did change the order of the script, just in useless
ways.
If you do find an answer, please let us all know.
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||Hi Jim
Will do :-)
Charles
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:eEWo$v3BIHA.5868@.TK2MSFTNGP05.phx.gbl...
> Usually, I put my scripts together manually, because this issue troubled
> me and I never got around to trying to find a solution for it. I did try
> checking off "Generate scripts for all dependent objects" and that did not
> help at all, although it did change the order of the script, just in
> useless ways.
> If you do find an answer, please let us all know.
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
I never used it myself. If you google there are more (don't miss the
sponsored links).
hth
Quentin
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
most of times it causes more problems. Besides nothing like doing it
yourself ;-) more fun, and you get to make sure everything is named how you
like it. Instead of using SQL naming convention for keys and such.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Charles Law" wrote:
> I don't know if I have missed something here, but I blindly thought that if
> I used SSMS to script my database, I could run the script on a new instance
> and get a database out of it.
> The script fails, and I get an error saying that a stored procedure requires
> an object that doesn't exist. That is because the object hasn't been created
> yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the correct
> order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>
>|||On Oct 5, 12:55 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> I don't know if I have missed something here, but I blindly thought that if
> I used SSMS to script my database, I could run the script on a new instance
> and get a database out of it.
> The script fails, and I get an error saying that a stored procedure requires
> an object that doesn't exist. That is because the object hasn't been created
> yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the correct
> order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
I regularly use Red Gate's SQL Compare for such tasks.|||Charles Law wrote:
> [...]
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
You can use the Database Publishing Wizard from
http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and
it takes care of the dependencies quite good (at least on SQL 2005).
--
Razvan Socol
SQL Server MVP|||Hi Quentin
Thanks for the suggestion. I will take a look.
Charles
"Quentin Ran" <remove_qran2@.yahoo.com> wrote in message
news:uYe4x$5BIHA.4836@.TK2MSFTNGP06.phx.gbl...
> http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
> I never used it myself. If you google there are more (don't miss the
> sponsored links).
> hth
> Quentin
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||Hi Mohit
Maintaining a script manually is really not an option. There are too many
objects to control in that way. Perhaps if SQL Server objects could be
properly source controlled then it might be an option, but not at the
moment.
Thanks.
Charles
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:AFF81E72-8292-462D-89FA-667E4CBDDB2B@.microsoft.com...
>I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
> most of times it causes more problems. Besides nothing like doing it
> yourself ;-) more fun, and you get to make sure everything is named how
> you
> like it. Instead of using SQL naming convention for keys and such.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Charles Law" wrote:
>> I don't know if I have missed something here, but I blindly thought that
>> if
>> I used SSMS to script my database, I could run the script on a new
>> instance
>> and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires
>> an object that doesn't exist. That is because the object hasn't been
>> created
>> yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct
>> order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>|||Hi Alex
I've just downloaded the toolkit trial which contains several utilities, so
I'll have a look at it now.
Cheers.
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1191640297.834009.175120@.d55g2000hsg.googlegroups.com...
> On Oct 5, 12:55 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> I don't know if I have missed something here, but I blindly thought that
>> if
>> I used SSMS to script my database, I could run the script on a new
>> instance
>> and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires
>> an object that doesn't exist. That is because the object hasn't been
>> created
>> yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct
>> order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
> I regularly use Red Gate's SQL Compare for such tasks.
>|||Hi Razvan
Thanks for the reply.
I have downloaded the wizard and run it. It looks identical to the scripting
wizard built into SS Management Studio. Do you know if it behaves
differently from the built-in version?
Charles
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:uI$0Lq9BIHA.4584@.TK2MSFTNGP06.phx.gbl...
> Charles Law wrote:
>> [...]
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
> You can use the Database Publishing Wizard from
> http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and
> it takes care of the dependencies quite good (at least on SQL 2005).
> --
> Razvan Socol
> SQL Server MVP|||Charles Law (blank@.nowhere.com) writes:
> Maintaining a script manually is really not an option. There are too many
> objects to control in that way. Perhaps if SQL Server objects could be
> properly source controlled then it might be an option, but not at the
> moment.
It isn't what? We keep all our SQL Server objects under version control
and have done so for many years. There's nothing special with SQL objects.
Source code is source code. Just do it.
The database on the other hand, is just a place were you put your
binaries. (Nevermind that the binaries in this case have a very strong
resemblence to the source code. It's nevertheless to be regarded as
binaries.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland
What do you use as your version control repository? The problem we get is
when someone makes a change to a database object, in the database, this is
not tracked and controlled. Is there a way of 'locking' objects until they
are checked out to the database, where developers can use SSMS to manage
their tables, views and stored procedures?
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C1EF8DD5814Yazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> Maintaining a script manually is really not an option. There are too many
>> objects to control in that way. Perhaps if SQL Server objects could be
>> properly source controlled then it might be an option, but not at the
>> moment.
> It isn't what? We keep all our SQL Server objects under version control
> and have done so for many years. There's nothing special with SQL objects.
> Source code is source code. Just do it.
> The database on the other hand, is just a place were you put your
> binaries. (Nevermind that the binaries in this case have a very strong
> resemblence to the source code. It's nevertheless to be regarded as
> binaries.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> What do you use as your version control repository? The problem we get is
> when someone makes a change to a database object, in the database, this is
> not tracked and controlled. Is there a way of 'locking' objects until they
> are checked out to the database, where developers can use SSMS to manage
> their tables, views and stored procedures?
We use Visual SourceSafe, although we really grew out of it long ago.
Database objects is not any different from any other type of objects.
You check it out when you work with it, you check it in when you are done.
If it is not checked in, it does not exist, it's as simlpe as that.
Someone might be changing things in a development database without checking
things out, but that is completely irrelevant. If it is not checked in,
it does not exist.
And, yes, someone could change a stored procedure in the production
environment without going the SourceSafe route. Just as someone could
change a C++ file, compile a DLL and install it in production without
going through SourceSafe.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland
I suppose the problem I have is that with database objects there is an
intermediate step/object, i.e. the script.
If I were in VS, I could select my objects, add them to VSS, and check them
in and out. In SQL Server, I have to create an intermediate object - the
script file - and put that into VSS. There is no direct control over the
object in the database. So, for example, there is no way for one person to
tell if someone else is working on a SP just by looking in the database.
Perhaps that level of tight integration and control will come in a future
release, but for now, it is not really adequate for our needs.
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to manage
>> their tables, views and stored procedures?
> We use Visual SourceSafe, although we really grew out of it long ago.
> Database objects is not any different from any other type of objects.
> You check it out when you work with it, you check it in when you are done.
> If it is not checked in, it does not exist, it's as simlpe as that.
> Someone might be changing things in a development database without
> checking
> things out, but that is completely irrelevant. If it is not checked in,
> it does not exist.
> And, yes, someone could change a stored procedure in the production
> environment without going the SourceSafe route. Just as someone could
> change a C++ file, compile a DLL and install it in production without
> going through SourceSafe.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> If I were in VS, I could select my objects, add them to VSS, and check
> them in and out. In SQL Server, I have to create an intermediate object -
> the script file - and put that into VSS. There is no direct control over
> the object in the database. So, for example, there is no way for one
> person to tell if someone else is working on a SP just by looking in the
> database.
I suggest you change your mindset to view the VSS SQL Server object script
file as the master copy rather than an "intermediate" file. This way, you
need only look in source control to see who is working on an object and can
follow the same sort of practices as you do for application code.
We use VSS as our source control repository (although we've outgrown it like
Erland) and are looking at TFS source control. I've also used ClearCase and
MKS in past positions. I don't think the source control tool is nearly as
important as the processes surrounding it. Sound development and release
management practices are mandatory, especially for larger teams.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles Law" <blank@.nowhere.com> wrote in message
news:OWsJUDaCIHA.972@.TK2MSFTNGP05.phx.gbl...
> Hi Erland
> I suppose the problem I have is that with database objects there is an
> intermediate step/object, i.e. the script.
> If I were in VS, I could select my objects, add them to VSS, and check
> them in and out. In SQL Server, I have to create an intermediate object -
> the script file - and put that into VSS. There is no direct control over
> the object in the database. So, for example, there is no way for one
> person to tell if someone else is working on a SP just by looking in the
> database.
> Perhaps that level of tight integration and control will come in a future
> release, but for now, it is not really adequate for our needs.
> Charles
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get
>> is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to manage
>> their tables, views and stored procedures?
>> We use Visual SourceSafe, although we really grew out of it long ago.
>> Database objects is not any different from any other type of objects.
>> You check it out when you work with it, you check it in when you are
>> done.
>> If it is not checked in, it does not exist, it's as simlpe as that.
>> Someone might be changing things in a development database without
>> checking
>> things out, but that is completely irrelevant. If it is not checked in,
>> it does not exist.
>> And, yes, someone could change a stored procedure in the production
>> environment without going the SourceSafe route. Just as someone could
>> change a C++ file, compile a DLL and install it in production without
>> going through SourceSafe.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||> Sound development and release management practices are mandatory,
> especially for larger teams.
I agree entirely. However, the script _is_ an intermediate file because
developers do not work on it directly. They work in SSMS on its version of
the object. By the same token, developers do not _need_ to check out an
object to work on it, only when they want to check a change into VSS.
If this were C# source, then the developer would have to check out a file
before working on it in VS, because it is the same file.
Procedures are fine, and necessary, but the more they can be enforced the
better. It just seems like an omission, or a gap, in what should be a joined
up process, that SQL Server and VSS do not integrate to the same extent as
VS and VSS.
Charles
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:EA5D139B-B8F6-40AA-9765-44FC573C522B@.microsoft.com...
>> If I were in VS, I could select my objects, add them to VSS, and check
>> them in and out. In SQL Server, I have to create an intermediate object -
>> the script file - and put that into VSS. There is no direct control over
>> the object in the database. So, for example, there is no way for one
>> person to tell if someone else is working on a SP just by looking in the
>> database.
> I suggest you change your mindset to view the VSS SQL Server object script
> file as the master copy rather than an "intermediate" file. This way, you
> need only look in source control to see who is working on an object and
> can follow the same sort of practices as you do for application code.
> We use VSS as our source control repository (although we've outgrown it
> like Erland) and are looking at TFS source control. I've also used
> ClearCase and MKS in past positions. I don't think the source control
> tool is nearly as important as the processes surrounding it. Sound
> development and release management practices are mandatory, especially for
> larger teams.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:OWsJUDaCIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Hi Erland
>> I suppose the problem I have is that with database objects there is an
>> intermediate step/object, i.e. the script.
>> If I were in VS, I could select my objects, add them to VSS, and check
>> them in and out. In SQL Server, I have to create an intermediate object -
>> the script file - and put that into VSS. There is no direct control over
>> the object in the database. So, for example, there is no way for one
>> person to tell if someone else is working on a SP just by looking in the
>> database.
>> Perhaps that level of tight integration and control will come in a future
>> release, but for now, it is not really adequate for our needs.
>> Charles
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get
>> is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to
>> manage
>> their tables, views and stored procedures?
>> We use Visual SourceSafe, although we really grew out of it long ago.
>> Database objects is not any different from any other type of objects.
>> You check it out when you work with it, you check it in when you are
>> done.
>> If it is not checked in, it does not exist, it's as simlpe as that.
>> Someone might be changing things in a development database without
>> checking
>> things out, but that is completely irrelevant. If it is not checked in,
>> it does not exist.
>> And, yes, someone could change a stored procedure in the production
>> environment without going the SourceSafe route. Just as someone could
>> change a C++ file, compile a DLL and install it in production without
>> going through SourceSafe.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>|||Charles Law (blank@.nowhere.com) writes:
> I agree entirely. However, the script _is_ an intermediate file because
> developers do not work on it directly. They work in SSMS on its version of
> the object. By the same token, developers do not _need_ to check out an
> object to work on it, only when they want to check a change into VSS.
> If this were C# source, then the developer would have to check out a file
> before working on it in VS, because it is the same file.
As Dan said, this is a question of mindset. There is no developer in my
shop that would understand what your talking about. For them, a stored
procedure is a file just like C# program is. Of course, it may help
that we don't use QA or SSMS to edit SQL code, but a third-party editor,
so there is still a clear notion of a compile step. But there is not
really any difference even if you work in SSMS. You have a file and
you edit it.
Yes, you can modify an object in the database without checking it out.
Just as you can modify a C# file without checking in out.
> Procedures are fine, and necessary, but the more they can be enforced
> the better. It just seems like an omission, or a gap, in what should be
> a joined up process, that SQL Server and VSS do not integrate to the
> same extent as VS and VSS.
Using the Source Control APIs it's possible to lock objects in SQL
Server with database triggers, so that any attempt to alter it is
met with "Check out first". But how do you know that the developer is
not modifying the object in another database?
By the way, it is possible to connect Mgmt Studio with at least SourceSafe.
(I'm uncertain about TFS). You still work with files, though. Which is
the way it should be. That's all what source code is about. Files.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Again, I'm not really disagreeing, but I see the database as the repository
for all these objects, where we are given convenient tools for editing and
maintaining the objects. We seem to be advancing in one way, having a GUI
that allows us to view and change these objects, but we still have to go
back to a script file if we want to make a permanent change. To me, it's a
bit like having Windows Explorer to look at the contents of a directory, but
opening a command prompt if we want to move a file.
Anyway, what I was really after was some means to simplify the task of
managing database objects, whether that be a database wizard or a scripting
tool, or something else, and something to make it easier to control changes.
Perhaps we will end up creating script files and manually manage a batch
file to run them in the correct order.
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C459CDD66EYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> I agree entirely. However, the script _is_ an intermediate file because
>> developers do not work on it directly. They work in SSMS on its version
>> of
>> the object. By the same token, developers do not _need_ to check out an
>> object to work on it, only when they want to check a change into VSS.
>> If this were C# source, then the developer would have to check out a file
>> before working on it in VS, because it is the same file.
> As Dan said, this is a question of mindset. There is no developer in my
> shop that would understand what your talking about. For them, a stored
> procedure is a file just like C# program is. Of course, it may help
> that we don't use QA or SSMS to edit SQL code, but a third-party editor,
> so there is still a clear notion of a compile step. But there is not
> really any difference even if you work in SSMS. You have a file and
> you edit it.
> Yes, you can modify an object in the database without checking it out.
> Just as you can modify a C# file without checking in out.
>> Procedures are fine, and necessary, but the more they can be enforced
>> the better. It just seems like an omission, or a gap, in what should be
>> a joined up process, that SQL Server and VSS do not integrate to the
>> same extent as VS and VSS.
> Using the Source Control APIs it's possible to lock objects in SQL
> Server with database triggers, so that any attempt to alter it is
> met with "Check out first". But how do you know that the developer is
> not modifying the object in another database?
> By the way, it is possible to connect Mgmt Studio with at least
> SourceSafe.
> (I'm uncertain about TFS). You still work with files, though. Which is
> the way it should be. That's all what source code is about. Files.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> Again, I'm not really disagreeing, but I see the database as the
> repository for all these objects,
And that is flat wrong in my opinion. In my shop there is no such things
as "the database". There is a unknown number of development and test
databases, all stemming from the same schema. In our case this is amplified
by the fact that our customers have so different configuration, that
you may need a particular configuration when you develop and test a certain
feature.
But even without that, developers may need their own sandboxes where they
can work without disturbing other developers. This does not at least
apply if needs to change tables.
I don't know if you have looked at Visual Studio Team System for
Database Developers, also known as DataDude. As I understand DataDude, you
work solely against a database on your local machine, you cannot work
against a central database.
> Anyway, what I was really after was some means to simplify the task of
> managing database objects, whether that be a database wizard or a
> scripting tool, or something else, and something to make it easier to
> control changes. Perhaps we will end up creating script files and
> manually manage a batch file to run them in the correct order.
Keep in mind that if you work from scripting the database, you probably
get a bunch of junk objects that should not be in the source control
system.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I'm starting to be persuaded. I've been looking at a silhouette of two
candlesticks, and suddenly I see two faces. I think you mentioned that you
use another tool to manage your scripts. What is that tool?
There is a problem that developers - well, the ones here - like the GUI,
draggy, droppy, clicky tools like SSMS, and given the choice between that
and a text editor to edit boring old scripts, you know which they will
choose.
Perhaps what I want, then, is something that manages the scripts (files) in
a visual way (like VS does with source code), and enables changes to be
deployed to the database of choice. Does that sound better?
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C4EFFCF5F73Yazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> Again, I'm not really disagreeing, but I see the database as the
>> repository for all these objects,
> And that is flat wrong in my opinion. In my shop there is no such things
> as "the database". There is a unknown number of development and test
> databases, all stemming from the same schema. In our case this is
> amplified
> by the fact that our customers have so different configuration, that
> you may need a particular configuration when you develop and test a
> certain
> feature.
> But even without that, developers may need their own sandboxes where they
> can work without disturbing other developers. This does not at least
> apply if needs to change tables.
> I don't know if you have looked at Visual Studio Team System for
> Database Developers, also known as DataDude. As I understand DataDude, you
> work solely against a database on your local machine, you cannot work
> against a central database.
>> Anyway, what I was really after was some means to simplify the task of
>> managing database objects, whether that be a database wizard or a
>> scripting tool, or something else, and something to make it easier to
>> control changes. Perhaps we will end up creating script files and
>> manually manage a batch file to run them in the correct order.
> Keep in mind that if you work from scripting the database, you probably
> get a bunch of junk objects that should not be in the source control
> system.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> I'm starting to be persuaded. I've been looking at a silhouette of two
> candlesticks, and suddenly I see two faces. I think you mentioned that you
> use another tool to manage your scripts. What is that tool?
We have a toolset that I have developed over the years, and by now
they have achieved quite a level of complexity. Although they are fairly
streamlined to our needs, so about everyone else would probably find
them quirky. And if you are looking for drag and drop, just forget it -
it's all command-line. (And all Perl.)
Unfortunately this toolset is not available to the public. Maybe I will
be able to make it avilable in some form in the future.
But I can give you some of the essence of the toolset, as it may give
you inspiration to devise your own; of course you would start off with
something very simple.
The core is a file-loading module that reads an SQL file and performs a
lot of processing of it, for instance changing CREATE to ALTER if required.
There is a preprocessor and lots of bells and whistles. But the really
important one is CREATE vs. ALTER, which you can handle in this way:
IF object_id('some_proc') IS NULL
EXEC ('CREATE PROCEDURE some_proc AS PRINT 1')
go
ALTER PROCEDURE some_proc -- real procedure follows here.
You would have this prologue in every file. Or, if you write a simple
file-loader in your favourite language, you can generate that code from
the filename. Assume that procedure name and file name agrees and
whip up all programmers that violate the rule. Of course, you can parse
the code, but parsing T-SQL is messy, so you will avoid it if you can.
Then there is one tool that builds an empty database from SourceSafe
or from a structure on disk. We have a certain organisation with different
types of files in different projects. The DB build tool just loads
files type by type in alphabetic order. To handle dependency between
stored procedure, we load those twice. Since you have views that
refer to each other you will need to find a way to control the order.
One way is to have a file that holds the load order of the views. For a
long time this was never a problem for us, simply because we hardly
used views at all. They have started to pop up, it we handle them with
help of our preprocessor.
Finally, there is a tool that reads SourceSafe between to two labels
and generate a script to load all changed objects. The tricky part
here is of course changed tables. Our tool generates a template to
handle the change which builds on the principle create new table,
move data and referencing FKs over, drop old table. In step one, you
will probably prefer to just insert whatever migration you want manually.
(Although it's quite boring.)
> There is a problem that developers - well, the ones here - like the GUI,
> draggy, droppy, clicky tools like SSMS, and given the choice between that
> and a text editor to edit boring old scripts, you know which they will
> choose.
Then again, there is not much you can point and click with in SSMS for
development. Oh, yeah, there is the Table Designer, but if they use
that one, really make sure they stay away. There are several serious
bugs in the Table Designer when it comes to change an existing table.
> Perhaps what I want, then, is something that manages the scripts (files)
> in a visual way (like VS does with source code), and enables changes to
> be deployed to the database of choice. Does that sound better?
There are certainly some options on the market, although I have not
investigated the options very closely. DataDude that I mention is
definitely worth looking at if you are a fan of Visual Studio.
Red Gate's SQL Compare has no integration with version-control system,
but their most recent version permits you to compare a database with
files on disk. That permits you compare the current development with
what you have in the version-control system, although the checkin-
checkout process would be a two-step process. You can also generate
update scripts from it. SQL Compare does not really align with the
way I like to work, but I'm impressed by the tool as such. Not the
least in comparison with SSMS which have quite a few shortcomings
with scripting. Not the least in performance...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland, thanks for bearing with me through this process. I will inwardly
digest and assimilate (makes me sound like the Borg).
I will continue to look into the possibility of a tool that will help here
or, who knows, even create one myself, as you have done.
Many thanks
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C647B2101DYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> I'm starting to be persuaded. I've been looking at a silhouette of two
>> candlesticks, and suddenly I see two faces. I think you mentioned that
>> you
>> use another tool to manage your scripts. What is that tool?
> We have a toolset that I have developed over the years, and by now
> they have achieved quite a level of complexity. Although they are fairly
> streamlined to our needs, so about everyone else would probably find
> them quirky. And if you are looking for drag and drop, just forget it -
> it's all command-line. (And all Perl.)
> Unfortunately this toolset is not available to the public. Maybe I will
> be able to make it avilable in some form in the future.
> But I can give you some of the essence of the toolset, as it may give
> you inspiration to devise your own; of course you would start off with
> something very simple.
> The core is a file-loading module that reads an SQL file and performs a
> lot of processing of it, for instance changing CREATE to ALTER if
> required.
> There is a preprocessor and lots of bells and whistles. But the really
> important one is CREATE vs. ALTER, which you can handle in this way:
> IF object_id('some_proc') IS NULL
> EXEC ('CREATE PROCEDURE some_proc AS PRINT 1')
> go
> ALTER PROCEDURE some_proc -- real procedure follows here.
> You would have this prologue in every file. Or, if you write a simple
> file-loader in your favourite language, you can generate that code from
> the filename. Assume that procedure name and file name agrees and
> whip up all programmers that violate the rule. Of course, you can parse
> the code, but parsing T-SQL is messy, so you will avoid it if you can.
> Then there is one tool that builds an empty database from SourceSafe
> or from a structure on disk. We have a certain organisation with different
> types of files in different projects. The DB build tool just loads
> files type by type in alphabetic order. To handle dependency between
> stored procedure, we load those twice. Since you have views that
> refer to each other you will need to find a way to control the order.
> One way is to have a file that holds the load order of the views. For a
> long time this was never a problem for us, simply because we hardly
> used views at all. They have started to pop up, it we handle them with
> help of our preprocessor.
> Finally, there is a tool that reads SourceSafe between to two labels
> and generate a script to load all changed objects. The tricky part
> here is of course changed tables. Our tool generates a template to
> handle the change which builds on the principle create new table,
> move data and referencing FKs over, drop old table. In step one, you
> will probably prefer to just insert whatever migration you want manually.
> (Although it's quite boring.)
>> There is a problem that developers - well, the ones here - like the GUI,
>> draggy, droppy, clicky tools like SSMS, and given the choice between that
>> and a text editor to edit boring old scripts, you know which they will
>> choose.
> Then again, there is not much you can point and click with in SSMS for
> development. Oh, yeah, there is the Table Designer, but if they use
> that one, really make sure they stay away. There are several serious
> bugs in the Table Designer when it comes to change an existing table.
>> Perhaps what I want, then, is something that manages the scripts (files)
>> in a visual way (like VS does with source code), and enables changes to
>> be deployed to the database of choice. Does that sound better?
> There are certainly some options on the market, although I have not
> investigated the options very closely. DataDude that I mention is
> definitely worth looking at if you are a fan of Visual Studio.
> Red Gate's SQL Compare has no integration with version-control system,
> but their most recent version permits you to compare a database with
> files on disk. That permits you compare the current development with
> what you have in the version-control system, although the checkin-
> checkout process would be a two-step process. You can also generate
> update scripts from it. SQL Compare does not really align with the
> way I like to work, but I'm impressed by the tool as such. Not the
> least in comparison with SSMS which have quite a few shortcomings
> with scripting. Not the least in performance...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Oct 10, 5:21 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> There are certainly some options on the market, although I have not
> investigated the options very closely. DataDude that I mention is
> definitely worth looking at if you are a fan of Visual Studio.
> Red Gate's SQL Compare has no integration with version-control system,
> but their most recent version permits you to compare a database with
> files on disk. That permits you compare the current development with
> what you have in the version-control system, although the checkin-
> checkout process would be a two-step process. You can also generate
> update scripts from it. SQL Compare does not really align with the
> way I like to work, but I'm impressed by the tool as such. Not the
> least in comparison with SSMS which have quite a few shortcomings
> with scripting. Not the least in performance...
yep. On the other hand it allows you to choose whatever version
colntrol you prefer. I personally prefer Subversion which also easily
integrates with JIRA/Fisheye/Crucible.|||I wrote a database scripting tool that probably can solve your issues.
Regards
Robert Allen Schambach
www.dbconstructor.com
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||Hi Robert
Looks very interesting. I will take a look.
Thanks.
Charles
"rallen" <rallen@.dbconstructor.com> wrote in message
news:uUs74pBEIHA.2004@.TK2MSFTNGP06.phx.gbl...
>I wrote a database scripting tool that probably can solve your issues.
> Regards
> Robert Allen Schambach
> www.dbconstructor.com
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||On Oct 16, 9:35 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Hi Robert
> Looks very interesting. I will take a look.
> Thanks.
> Charles
> "rallen" <ral...@.dbconstructor.com> wrote in message
> news:uUs74pBEIHA.2004@.TK2MSFTNGP06.phx.gbl...
>
> >I wrote a database scripting tool that probably can solve your issues.
> > Regards
> > Robert Allen Schambach
> >www.dbconstructor.com
> > "Charles Law" <bl...@.nowhere.com> wrote in message
> >news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> >>I don't know if I have missed something here, but I blindly thought that
> >>if I used SSMS to script my database, I could run the script on a new
> >>instance and get a database out of it.
> >> The script fails, and I get an error saying that a stored procedure
> >> requires an object that doesn't exist. That is because the object hasn't
> >> been created yet. It appears later on in the script.
> >> How can I get the database scripting wizard to script objects in the
> >> correct order, that is in an order that takes account of dependencies?
> >> Either that, or does anyone know of a tool/product that does do this?
> >> TIA
> >> Charles- Hide quoted text -
> - Show quoted text -
Charles,
Please let me know if you need any help using dbConstructor, there
will be better documentation shortly, if you have any problems or
suggestions please drop me a line, use the sites Contact Us screen and
I will gladly give you any assistance necessary.
Regards,
Robert Allen Schambach
I used SSMS to script my database, I could run the script on a new instance
and get a database out of it.
The script fails, and I get an error saying that a stored procedure requires
an object that doesn't exist. That is because the object hasn't been created
yet. It appears later on in the script.
How can I get the database scripting wizard to script objects in the correct
order, that is in an order that takes account of dependencies?
Either that, or does anyone know of a tool/product that does do this?
TIA
CharlesUsually, I put my scripts together manually, because this issue troubled me
and I never got around to trying to find a solution for it. I did try
checking off "Generate scripts for all dependent objects" and that did not
help at all, although it did change the order of the script, just in useless
ways.
If you do find an answer, please let us all know.
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||Hi Jim
Will do :-)
Charles
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:eEWo$v3BIHA.5868@.TK2MSFTNGP05.phx.gbl...
> Usually, I put my scripts together manually, because this issue troubled
> me and I never got around to trying to find a solution for it. I did try
> checking off "Generate scripts for all dependent objects" and that did not
> help at all, although it did change the order of the script, just in
> useless ways.
> If you do find an answer, please let us all know.
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
I never used it myself. If you google there are more (don't miss the
sponsored links).
hth
Quentin
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
most of times it causes more problems. Besides nothing like doing it
yourself ;-) more fun, and you get to make sure everything is named how you
like it. Instead of using SQL naming convention for keys and such.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Charles Law" wrote:
> I don't know if I have missed something here, but I blindly thought that if
> I used SSMS to script my database, I could run the script on a new instance
> and get a database out of it.
> The script fails, and I get an error saying that a stored procedure requires
> an object that doesn't exist. That is because the object hasn't been created
> yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the correct
> order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>
>|||On Oct 5, 12:55 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> I don't know if I have missed something here, but I blindly thought that if
> I used SSMS to script my database, I could run the script on a new instance
> and get a database out of it.
> The script fails, and I get an error saying that a stored procedure requires
> an object that doesn't exist. That is because the object hasn't been created
> yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the correct
> order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
I regularly use Red Gate's SQL Compare for such tasks.|||Charles Law wrote:
> [...]
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
You can use the Database Publishing Wizard from
http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and
it takes care of the dependencies quite good (at least on SQL 2005).
--
Razvan Socol
SQL Server MVP|||Hi Quentin
Thanks for the suggestion. I will take a look.
Charles
"Quentin Ran" <remove_qran2@.yahoo.com> wrote in message
news:uYe4x$5BIHA.4836@.TK2MSFTNGP06.phx.gbl...
> http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
> I never used it myself. If you google there are more (don't miss the
> sponsored links).
> hth
> Quentin
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||Hi Mohit
Maintaining a script manually is really not an option. There are too many
objects to control in that way. Perhaps if SQL Server objects could be
properly source controlled then it might be an option, but not at the
moment.
Thanks.
Charles
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:AFF81E72-8292-462D-89FA-667E4CBDDB2B@.microsoft.com...
>I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
> most of times it causes more problems. Besides nothing like doing it
> yourself ;-) more fun, and you get to make sure everything is named how
> you
> like it. Instead of using SQL naming convention for keys and such.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Charles Law" wrote:
>> I don't know if I have missed something here, but I blindly thought that
>> if
>> I used SSMS to script my database, I could run the script on a new
>> instance
>> and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires
>> an object that doesn't exist. That is because the object hasn't been
>> created
>> yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct
>> order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>|||Hi Alex
I've just downloaded the toolkit trial which contains several utilities, so
I'll have a look at it now.
Cheers.
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1191640297.834009.175120@.d55g2000hsg.googlegroups.com...
> On Oct 5, 12:55 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> I don't know if I have missed something here, but I blindly thought that
>> if
>> I used SSMS to script my database, I could run the script on a new
>> instance
>> and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires
>> an object that doesn't exist. That is because the object hasn't been
>> created
>> yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct
>> order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
> I regularly use Red Gate's SQL Compare for such tasks.
>|||Hi Razvan
Thanks for the reply.
I have downloaded the wizard and run it. It looks identical to the scripting
wizard built into SS Management Studio. Do you know if it behaves
differently from the built-in version?
Charles
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:uI$0Lq9BIHA.4584@.TK2MSFTNGP06.phx.gbl...
> Charles Law wrote:
>> [...]
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
> You can use the Database Publishing Wizard from
> http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and
> it takes care of the dependencies quite good (at least on SQL 2005).
> --
> Razvan Socol
> SQL Server MVP|||Charles Law (blank@.nowhere.com) writes:
> Maintaining a script manually is really not an option. There are too many
> objects to control in that way. Perhaps if SQL Server objects could be
> properly source controlled then it might be an option, but not at the
> moment.
It isn't what? We keep all our SQL Server objects under version control
and have done so for many years. There's nothing special with SQL objects.
Source code is source code. Just do it.
The database on the other hand, is just a place were you put your
binaries. (Nevermind that the binaries in this case have a very strong
resemblence to the source code. It's nevertheless to be regarded as
binaries.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland
What do you use as your version control repository? The problem we get is
when someone makes a change to a database object, in the database, this is
not tracked and controlled. Is there a way of 'locking' objects until they
are checked out to the database, where developers can use SSMS to manage
their tables, views and stored procedures?
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C1EF8DD5814Yazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> Maintaining a script manually is really not an option. There are too many
>> objects to control in that way. Perhaps if SQL Server objects could be
>> properly source controlled then it might be an option, but not at the
>> moment.
> It isn't what? We keep all our SQL Server objects under version control
> and have done so for many years. There's nothing special with SQL objects.
> Source code is source code. Just do it.
> The database on the other hand, is just a place were you put your
> binaries. (Nevermind that the binaries in this case have a very strong
> resemblence to the source code. It's nevertheless to be regarded as
> binaries.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> What do you use as your version control repository? The problem we get is
> when someone makes a change to a database object, in the database, this is
> not tracked and controlled. Is there a way of 'locking' objects until they
> are checked out to the database, where developers can use SSMS to manage
> their tables, views and stored procedures?
We use Visual SourceSafe, although we really grew out of it long ago.
Database objects is not any different from any other type of objects.
You check it out when you work with it, you check it in when you are done.
If it is not checked in, it does not exist, it's as simlpe as that.
Someone might be changing things in a development database without checking
things out, but that is completely irrelevant. If it is not checked in,
it does not exist.
And, yes, someone could change a stored procedure in the production
environment without going the SourceSafe route. Just as someone could
change a C++ file, compile a DLL and install it in production without
going through SourceSafe.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland
I suppose the problem I have is that with database objects there is an
intermediate step/object, i.e. the script.
If I were in VS, I could select my objects, add them to VSS, and check them
in and out. In SQL Server, I have to create an intermediate object - the
script file - and put that into VSS. There is no direct control over the
object in the database. So, for example, there is no way for one person to
tell if someone else is working on a SP just by looking in the database.
Perhaps that level of tight integration and control will come in a future
release, but for now, it is not really adequate for our needs.
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to manage
>> their tables, views and stored procedures?
> We use Visual SourceSafe, although we really grew out of it long ago.
> Database objects is not any different from any other type of objects.
> You check it out when you work with it, you check it in when you are done.
> If it is not checked in, it does not exist, it's as simlpe as that.
> Someone might be changing things in a development database without
> checking
> things out, but that is completely irrelevant. If it is not checked in,
> it does not exist.
> And, yes, someone could change a stored procedure in the production
> environment without going the SourceSafe route. Just as someone could
> change a C++ file, compile a DLL and install it in production without
> going through SourceSafe.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> If I were in VS, I could select my objects, add them to VSS, and check
> them in and out. In SQL Server, I have to create an intermediate object -
> the script file - and put that into VSS. There is no direct control over
> the object in the database. So, for example, there is no way for one
> person to tell if someone else is working on a SP just by looking in the
> database.
I suggest you change your mindset to view the VSS SQL Server object script
file as the master copy rather than an "intermediate" file. This way, you
need only look in source control to see who is working on an object and can
follow the same sort of practices as you do for application code.
We use VSS as our source control repository (although we've outgrown it like
Erland) and are looking at TFS source control. I've also used ClearCase and
MKS in past positions. I don't think the source control tool is nearly as
important as the processes surrounding it. Sound development and release
management practices are mandatory, especially for larger teams.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles Law" <blank@.nowhere.com> wrote in message
news:OWsJUDaCIHA.972@.TK2MSFTNGP05.phx.gbl...
> Hi Erland
> I suppose the problem I have is that with database objects there is an
> intermediate step/object, i.e. the script.
> If I were in VS, I could select my objects, add them to VSS, and check
> them in and out. In SQL Server, I have to create an intermediate object -
> the script file - and put that into VSS. There is no direct control over
> the object in the database. So, for example, there is no way for one
> person to tell if someone else is working on a SP just by looking in the
> database.
> Perhaps that level of tight integration and control will come in a future
> release, but for now, it is not really adequate for our needs.
> Charles
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get
>> is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to manage
>> their tables, views and stored procedures?
>> We use Visual SourceSafe, although we really grew out of it long ago.
>> Database objects is not any different from any other type of objects.
>> You check it out when you work with it, you check it in when you are
>> done.
>> If it is not checked in, it does not exist, it's as simlpe as that.
>> Someone might be changing things in a development database without
>> checking
>> things out, but that is completely irrelevant. If it is not checked in,
>> it does not exist.
>> And, yes, someone could change a stored procedure in the production
>> environment without going the SourceSafe route. Just as someone could
>> change a C++ file, compile a DLL and install it in production without
>> going through SourceSafe.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||> Sound development and release management practices are mandatory,
> especially for larger teams.
I agree entirely. However, the script _is_ an intermediate file because
developers do not work on it directly. They work in SSMS on its version of
the object. By the same token, developers do not _need_ to check out an
object to work on it, only when they want to check a change into VSS.
If this were C# source, then the developer would have to check out a file
before working on it in VS, because it is the same file.
Procedures are fine, and necessary, but the more they can be enforced the
better. It just seems like an omission, or a gap, in what should be a joined
up process, that SQL Server and VSS do not integrate to the same extent as
VS and VSS.
Charles
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:EA5D139B-B8F6-40AA-9765-44FC573C522B@.microsoft.com...
>> If I were in VS, I could select my objects, add them to VSS, and check
>> them in and out. In SQL Server, I have to create an intermediate object -
>> the script file - and put that into VSS. There is no direct control over
>> the object in the database. So, for example, there is no way for one
>> person to tell if someone else is working on a SP just by looking in the
>> database.
> I suggest you change your mindset to view the VSS SQL Server object script
> file as the master copy rather than an "intermediate" file. This way, you
> need only look in source control to see who is working on an object and
> can follow the same sort of practices as you do for application code.
> We use VSS as our source control repository (although we've outgrown it
> like Erland) and are looking at TFS source control. I've also used
> ClearCase and MKS in past positions. I don't think the source control
> tool is nearly as important as the processes surrounding it. Sound
> development and release management practices are mandatory, especially for
> larger teams.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:OWsJUDaCIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Hi Erland
>> I suppose the problem I have is that with database objects there is an
>> intermediate step/object, i.e. the script.
>> If I were in VS, I could select my objects, add them to VSS, and check
>> them in and out. In SQL Server, I have to create an intermediate object -
>> the script file - and put that into VSS. There is no direct control over
>> the object in the database. So, for example, there is no way for one
>> person to tell if someone else is working on a SP just by looking in the
>> database.
>> Perhaps that level of tight integration and control will come in a future
>> release, but for now, it is not really adequate for our needs.
>> Charles
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get
>> is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to
>> manage
>> their tables, views and stored procedures?
>> We use Visual SourceSafe, although we really grew out of it long ago.
>> Database objects is not any different from any other type of objects.
>> You check it out when you work with it, you check it in when you are
>> done.
>> If it is not checked in, it does not exist, it's as simlpe as that.
>> Someone might be changing things in a development database without
>> checking
>> things out, but that is completely irrelevant. If it is not checked in,
>> it does not exist.
>> And, yes, someone could change a stored procedure in the production
>> environment without going the SourceSafe route. Just as someone could
>> change a C++ file, compile a DLL and install it in production without
>> going through SourceSafe.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>|||Charles Law (blank@.nowhere.com) writes:
> I agree entirely. However, the script _is_ an intermediate file because
> developers do not work on it directly. They work in SSMS on its version of
> the object. By the same token, developers do not _need_ to check out an
> object to work on it, only when they want to check a change into VSS.
> If this were C# source, then the developer would have to check out a file
> before working on it in VS, because it is the same file.
As Dan said, this is a question of mindset. There is no developer in my
shop that would understand what your talking about. For them, a stored
procedure is a file just like C# program is. Of course, it may help
that we don't use QA or SSMS to edit SQL code, but a third-party editor,
so there is still a clear notion of a compile step. But there is not
really any difference even if you work in SSMS. You have a file and
you edit it.
Yes, you can modify an object in the database without checking it out.
Just as you can modify a C# file without checking in out.
> Procedures are fine, and necessary, but the more they can be enforced
> the better. It just seems like an omission, or a gap, in what should be
> a joined up process, that SQL Server and VSS do not integrate to the
> same extent as VS and VSS.
Using the Source Control APIs it's possible to lock objects in SQL
Server with database triggers, so that any attempt to alter it is
met with "Check out first". But how do you know that the developer is
not modifying the object in another database?
By the way, it is possible to connect Mgmt Studio with at least SourceSafe.
(I'm uncertain about TFS). You still work with files, though. Which is
the way it should be. That's all what source code is about. Files.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Again, I'm not really disagreeing, but I see the database as the repository
for all these objects, where we are given convenient tools for editing and
maintaining the objects. We seem to be advancing in one way, having a GUI
that allows us to view and change these objects, but we still have to go
back to a script file if we want to make a permanent change. To me, it's a
bit like having Windows Explorer to look at the contents of a directory, but
opening a command prompt if we want to move a file.
Anyway, what I was really after was some means to simplify the task of
managing database objects, whether that be a database wizard or a scripting
tool, or something else, and something to make it easier to control changes.
Perhaps we will end up creating script files and manually manage a batch
file to run them in the correct order.
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C459CDD66EYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> I agree entirely. However, the script _is_ an intermediate file because
>> developers do not work on it directly. They work in SSMS on its version
>> of
>> the object. By the same token, developers do not _need_ to check out an
>> object to work on it, only when they want to check a change into VSS.
>> If this were C# source, then the developer would have to check out a file
>> before working on it in VS, because it is the same file.
> As Dan said, this is a question of mindset. There is no developer in my
> shop that would understand what your talking about. For them, a stored
> procedure is a file just like C# program is. Of course, it may help
> that we don't use QA or SSMS to edit SQL code, but a third-party editor,
> so there is still a clear notion of a compile step. But there is not
> really any difference even if you work in SSMS. You have a file and
> you edit it.
> Yes, you can modify an object in the database without checking it out.
> Just as you can modify a C# file without checking in out.
>> Procedures are fine, and necessary, but the more they can be enforced
>> the better. It just seems like an omission, or a gap, in what should be
>> a joined up process, that SQL Server and VSS do not integrate to the
>> same extent as VS and VSS.
> Using the Source Control APIs it's possible to lock objects in SQL
> Server with database triggers, so that any attempt to alter it is
> met with "Check out first". But how do you know that the developer is
> not modifying the object in another database?
> By the way, it is possible to connect Mgmt Studio with at least
> SourceSafe.
> (I'm uncertain about TFS). You still work with files, though. Which is
> the way it should be. That's all what source code is about. Files.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> Again, I'm not really disagreeing, but I see the database as the
> repository for all these objects,
And that is flat wrong in my opinion. In my shop there is no such things
as "the database". There is a unknown number of development and test
databases, all stemming from the same schema. In our case this is amplified
by the fact that our customers have so different configuration, that
you may need a particular configuration when you develop and test a certain
feature.
But even without that, developers may need their own sandboxes where they
can work without disturbing other developers. This does not at least
apply if needs to change tables.
I don't know if you have looked at Visual Studio Team System for
Database Developers, also known as DataDude. As I understand DataDude, you
work solely against a database on your local machine, you cannot work
against a central database.
> Anyway, what I was really after was some means to simplify the task of
> managing database objects, whether that be a database wizard or a
> scripting tool, or something else, and something to make it easier to
> control changes. Perhaps we will end up creating script files and
> manually manage a batch file to run them in the correct order.
Keep in mind that if you work from scripting the database, you probably
get a bunch of junk objects that should not be in the source control
system.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I'm starting to be persuaded. I've been looking at a silhouette of two
candlesticks, and suddenly I see two faces. I think you mentioned that you
use another tool to manage your scripts. What is that tool?
There is a problem that developers - well, the ones here - like the GUI,
draggy, droppy, clicky tools like SSMS, and given the choice between that
and a text editor to edit boring old scripts, you know which they will
choose.
Perhaps what I want, then, is something that manages the scripts (files) in
a visual way (like VS does with source code), and enables changes to be
deployed to the database of choice. Does that sound better?
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C4EFFCF5F73Yazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> Again, I'm not really disagreeing, but I see the database as the
>> repository for all these objects,
> And that is flat wrong in my opinion. In my shop there is no such things
> as "the database". There is a unknown number of development and test
> databases, all stemming from the same schema. In our case this is
> amplified
> by the fact that our customers have so different configuration, that
> you may need a particular configuration when you develop and test a
> certain
> feature.
> But even without that, developers may need their own sandboxes where they
> can work without disturbing other developers. This does not at least
> apply if needs to change tables.
> I don't know if you have looked at Visual Studio Team System for
> Database Developers, also known as DataDude. As I understand DataDude, you
> work solely against a database on your local machine, you cannot work
> against a central database.
>> Anyway, what I was really after was some means to simplify the task of
>> managing database objects, whether that be a database wizard or a
>> scripting tool, or something else, and something to make it easier to
>> control changes. Perhaps we will end up creating script files and
>> manually manage a batch file to run them in the correct order.
> Keep in mind that if you work from scripting the database, you probably
> get a bunch of junk objects that should not be in the source control
> system.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> I'm starting to be persuaded. I've been looking at a silhouette of two
> candlesticks, and suddenly I see two faces. I think you mentioned that you
> use another tool to manage your scripts. What is that tool?
We have a toolset that I have developed over the years, and by now
they have achieved quite a level of complexity. Although they are fairly
streamlined to our needs, so about everyone else would probably find
them quirky. And if you are looking for drag and drop, just forget it -
it's all command-line. (And all Perl.)
Unfortunately this toolset is not available to the public. Maybe I will
be able to make it avilable in some form in the future.
But I can give you some of the essence of the toolset, as it may give
you inspiration to devise your own; of course you would start off with
something very simple.
The core is a file-loading module that reads an SQL file and performs a
lot of processing of it, for instance changing CREATE to ALTER if required.
There is a preprocessor and lots of bells and whistles. But the really
important one is CREATE vs. ALTER, which you can handle in this way:
IF object_id('some_proc') IS NULL
EXEC ('CREATE PROCEDURE some_proc AS PRINT 1')
go
ALTER PROCEDURE some_proc -- real procedure follows here.
You would have this prologue in every file. Or, if you write a simple
file-loader in your favourite language, you can generate that code from
the filename. Assume that procedure name and file name agrees and
whip up all programmers that violate the rule. Of course, you can parse
the code, but parsing T-SQL is messy, so you will avoid it if you can.
Then there is one tool that builds an empty database from SourceSafe
or from a structure on disk. We have a certain organisation with different
types of files in different projects. The DB build tool just loads
files type by type in alphabetic order. To handle dependency between
stored procedure, we load those twice. Since you have views that
refer to each other you will need to find a way to control the order.
One way is to have a file that holds the load order of the views. For a
long time this was never a problem for us, simply because we hardly
used views at all. They have started to pop up, it we handle them with
help of our preprocessor.
Finally, there is a tool that reads SourceSafe between to two labels
and generate a script to load all changed objects. The tricky part
here is of course changed tables. Our tool generates a template to
handle the change which builds on the principle create new table,
move data and referencing FKs over, drop old table. In step one, you
will probably prefer to just insert whatever migration you want manually.
(Although it's quite boring.)
> There is a problem that developers - well, the ones here - like the GUI,
> draggy, droppy, clicky tools like SSMS, and given the choice between that
> and a text editor to edit boring old scripts, you know which they will
> choose.
Then again, there is not much you can point and click with in SSMS for
development. Oh, yeah, there is the Table Designer, but if they use
that one, really make sure they stay away. There are several serious
bugs in the Table Designer when it comes to change an existing table.
> Perhaps what I want, then, is something that manages the scripts (files)
> in a visual way (like VS does with source code), and enables changes to
> be deployed to the database of choice. Does that sound better?
There are certainly some options on the market, although I have not
investigated the options very closely. DataDude that I mention is
definitely worth looking at if you are a fan of Visual Studio.
Red Gate's SQL Compare has no integration with version-control system,
but their most recent version permits you to compare a database with
files on disk. That permits you compare the current development with
what you have in the version-control system, although the checkin-
checkout process would be a two-step process. You can also generate
update scripts from it. SQL Compare does not really align with the
way I like to work, but I'm impressed by the tool as such. Not the
least in comparison with SSMS which have quite a few shortcomings
with scripting. Not the least in performance...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland, thanks for bearing with me through this process. I will inwardly
digest and assimilate (makes me sound like the Borg).
I will continue to look into the possibility of a tool that will help here
or, who knows, even create one myself, as you have done.
Many thanks
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C647B2101DYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> I'm starting to be persuaded. I've been looking at a silhouette of two
>> candlesticks, and suddenly I see two faces. I think you mentioned that
>> you
>> use another tool to manage your scripts. What is that tool?
> We have a toolset that I have developed over the years, and by now
> they have achieved quite a level of complexity. Although they are fairly
> streamlined to our needs, so about everyone else would probably find
> them quirky. And if you are looking for drag and drop, just forget it -
> it's all command-line. (And all Perl.)
> Unfortunately this toolset is not available to the public. Maybe I will
> be able to make it avilable in some form in the future.
> But I can give you some of the essence of the toolset, as it may give
> you inspiration to devise your own; of course you would start off with
> something very simple.
> The core is a file-loading module that reads an SQL file and performs a
> lot of processing of it, for instance changing CREATE to ALTER if
> required.
> There is a preprocessor and lots of bells and whistles. But the really
> important one is CREATE vs. ALTER, which you can handle in this way:
> IF object_id('some_proc') IS NULL
> EXEC ('CREATE PROCEDURE some_proc AS PRINT 1')
> go
> ALTER PROCEDURE some_proc -- real procedure follows here.
> You would have this prologue in every file. Or, if you write a simple
> file-loader in your favourite language, you can generate that code from
> the filename. Assume that procedure name and file name agrees and
> whip up all programmers that violate the rule. Of course, you can parse
> the code, but parsing T-SQL is messy, so you will avoid it if you can.
> Then there is one tool that builds an empty database from SourceSafe
> or from a structure on disk. We have a certain organisation with different
> types of files in different projects. The DB build tool just loads
> files type by type in alphabetic order. To handle dependency between
> stored procedure, we load those twice. Since you have views that
> refer to each other you will need to find a way to control the order.
> One way is to have a file that holds the load order of the views. For a
> long time this was never a problem for us, simply because we hardly
> used views at all. They have started to pop up, it we handle them with
> help of our preprocessor.
> Finally, there is a tool that reads SourceSafe between to two labels
> and generate a script to load all changed objects. The tricky part
> here is of course changed tables. Our tool generates a template to
> handle the change which builds on the principle create new table,
> move data and referencing FKs over, drop old table. In step one, you
> will probably prefer to just insert whatever migration you want manually.
> (Although it's quite boring.)
>> There is a problem that developers - well, the ones here - like the GUI,
>> draggy, droppy, clicky tools like SSMS, and given the choice between that
>> and a text editor to edit boring old scripts, you know which they will
>> choose.
> Then again, there is not much you can point and click with in SSMS for
> development. Oh, yeah, there is the Table Designer, but if they use
> that one, really make sure they stay away. There are several serious
> bugs in the Table Designer when it comes to change an existing table.
>> Perhaps what I want, then, is something that manages the scripts (files)
>> in a visual way (like VS does with source code), and enables changes to
>> be deployed to the database of choice. Does that sound better?
> There are certainly some options on the market, although I have not
> investigated the options very closely. DataDude that I mention is
> definitely worth looking at if you are a fan of Visual Studio.
> Red Gate's SQL Compare has no integration with version-control system,
> but their most recent version permits you to compare a database with
> files on disk. That permits you compare the current development with
> what you have in the version-control system, although the checkin-
> checkout process would be a two-step process. You can also generate
> update scripts from it. SQL Compare does not really align with the
> way I like to work, but I'm impressed by the tool as such. Not the
> least in comparison with SSMS which have quite a few shortcomings
> with scripting. Not the least in performance...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Oct 10, 5:21 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> There are certainly some options on the market, although I have not
> investigated the options very closely. DataDude that I mention is
> definitely worth looking at if you are a fan of Visual Studio.
> Red Gate's SQL Compare has no integration with version-control system,
> but their most recent version permits you to compare a database with
> files on disk. That permits you compare the current development with
> what you have in the version-control system, although the checkin-
> checkout process would be a two-step process. You can also generate
> update scripts from it. SQL Compare does not really align with the
> way I like to work, but I'm impressed by the tool as such. Not the
> least in comparison with SSMS which have quite a few shortcomings
> with scripting. Not the least in performance...
yep. On the other hand it allows you to choose whatever version
colntrol you prefer. I personally prefer Subversion which also easily
integrates with JIRA/Fisheye/Crucible.|||I wrote a database scripting tool that probably can solve your issues.
Regards
Robert Allen Schambach
www.dbconstructor.com
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||Hi Robert
Looks very interesting. I will take a look.
Thanks.
Charles
"rallen" <rallen@.dbconstructor.com> wrote in message
news:uUs74pBEIHA.2004@.TK2MSFTNGP06.phx.gbl...
>I wrote a database scripting tool that probably can solve your issues.
> Regards
> Robert Allen Schambach
> www.dbconstructor.com
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||On Oct 16, 9:35 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Hi Robert
> Looks very interesting. I will take a look.
> Thanks.
> Charles
> "rallen" <ral...@.dbconstructor.com> wrote in message
> news:uUs74pBEIHA.2004@.TK2MSFTNGP06.phx.gbl...
>
> >I wrote a database scripting tool that probably can solve your issues.
> > Regards
> > Robert Allen Schambach
> >www.dbconstructor.com
> > "Charles Law" <bl...@.nowhere.com> wrote in message
> >news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> >>I don't know if I have missed something here, but I blindly thought that
> >>if I used SSMS to script my database, I could run the script on a new
> >>instance and get a database out of it.
> >> The script fails, and I get an error saying that a stored procedure
> >> requires an object that doesn't exist. That is because the object hasn't
> >> been created yet. It appears later on in the script.
> >> How can I get the database scripting wizard to script objects in the
> >> correct order, that is in an order that takes account of dependencies?
> >> Either that, or does anyone know of a tool/product that does do this?
> >> TIA
> >> Charles- Hide quoted text -
> - Show quoted text -
Charles,
Please let me know if you need any help using dbConstructor, there
will be better documentation shortly, if you have any problems or
suggestions please drop me a line, use the sites Contact Us screen and
I will gladly give you any assistance necessary.
Regards,
Robert Allen Schambach
Database Schema Documentation Tool?
I am looking for a database documentation tool/script that I can run against
a particular database to generate a document of all the tables, columns, and
relationships in the database. I have seen a data dictionary that lists the
table names with links to the table details further down the document, and
clicking on the relationships jumps you to that table in the document. That
particular one was generated out of the programming revision control system
they were using into xml and xsl files. I am looking for something that can
generate the information out of the metadata in SQL Server.
Thanks
I think you may be thinking of Enterprise Architect
http://www.sparxsystems.com.au/
Or ER/Studio
http://www.embarcadero.com/products/erstudio/index.html
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||Look at ApexSQL Doc from www.ApexSQL.com
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||I love ApexSQL . See the details from below URL:-
http://www.sql-server-performance.com/apex_sql_doc_spotlight.asp
http://www.apexsql.com/sql_tools_doc.asp
You could try the trial version and use it for a month:-
http://www.apexsql.com/downloads.asp
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OyNRBReFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Look at ApexSQL Doc from www.ApexSQL.com
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Greg Hess" <keadrix@.hotmail.com> wrote in message
> news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Another effective but much less costly option if you only want
documentation is
SqlSpec from ElsaSoft
Their website at www.elsasoft.org has a trial version and also samples
of the output
T
|||Greg Hess wrote:
> I am looking for a database documentation tool/script that I can run against
> a particular database to generate a document of all the tables, columns, and
> relationships in the database. I have seen a data dictionary that lists the
> table names with links to the table details further down the document, and
> clicking on the relationships jumps you to that table in the document. That
> particular one was generated out of the programming revision control system
> they were using into xml and xsl files. I am looking for something that can
> generate the information out of the metadata in SQL Server.
> Thanks
You might want to try SchemaToDoc for SQL Server
(http://www.schematodoc.com). It exports to a Word doc metadata info
such as primary keys, field info (types, size, nullable, defaults),
indexes, check constraints, foreign key constraints, triggers, views,
stored procedures, and extended properties. It also lets you annotate
your tables and fields and include those comments in the Word doc. An
Enterprise edition can create a series of linked HTML files in addition
to the Word output.
|||Thank you for all your suggestions. After evaluating them I have decided to
go with SqlSpec from Elsasoft (http://www.elsasoft.org/). It does exactly
what I need it to do for a good price.
a particular database to generate a document of all the tables, columns, and
relationships in the database. I have seen a data dictionary that lists the
table names with links to the table details further down the document, and
clicking on the relationships jumps you to that table in the document. That
particular one was generated out of the programming revision control system
they were using into xml and xsl files. I am looking for something that can
generate the information out of the metadata in SQL Server.
Thanks
I think you may be thinking of Enterprise Architect
http://www.sparxsystems.com.au/
Or ER/Studio
http://www.embarcadero.com/products/erstudio/index.html
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||Look at ApexSQL Doc from www.ApexSQL.com
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||I love ApexSQL . See the details from below URL:-
http://www.sql-server-performance.com/apex_sql_doc_spotlight.asp
http://www.apexsql.com/sql_tools_doc.asp
You could try the trial version and use it for a month:-
http://www.apexsql.com/downloads.asp
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OyNRBReFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Look at ApexSQL Doc from www.ApexSQL.com
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Greg Hess" <keadrix@.hotmail.com> wrote in message
> news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Another effective but much less costly option if you only want
documentation is
SqlSpec from ElsaSoft
Their website at www.elsasoft.org has a trial version and also samples
of the output
T
|||Greg Hess wrote:
> I am looking for a database documentation tool/script that I can run against
> a particular database to generate a document of all the tables, columns, and
> relationships in the database. I have seen a data dictionary that lists the
> table names with links to the table details further down the document, and
> clicking on the relationships jumps you to that table in the document. That
> particular one was generated out of the programming revision control system
> they were using into xml and xsl files. I am looking for something that can
> generate the information out of the metadata in SQL Server.
> Thanks
You might want to try SchemaToDoc for SQL Server
(http://www.schematodoc.com). It exports to a Word doc metadata info
such as primary keys, field info (types, size, nullable, defaults),
indexes, check constraints, foreign key constraints, triggers, views,
stored procedures, and extended properties. It also lets you annotate
your tables and fields and include those comments in the Word doc. An
Enterprise edition can create a series of linked HTML files in addition
to the Word output.
|||Thank you for all your suggestions. After evaluating them I have decided to
go with SqlSpec from Elsasoft (http://www.elsasoft.org/). It does exactly
what I need it to do for a good price.
Thursday, March 8, 2012
Database Restore Problem
I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.
After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]
There are no users connected to the database or processes running against it.
The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'
ALTER DATABASE DBA_pfaids_test SET MULTI_USER
Does this have something to do with the initial refresh from Production?
How can I get it to work?
Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.
You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.
Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.
Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.
Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)
~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).
Lex
After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]
There are no users connected to the database or processes running against it.
The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'
ALTER DATABASE DBA_pfaids_test SET MULTI_USER
Does this have something to do with the initial refresh from Production?
How can I get it to work?
Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.
You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.
Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.
Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.
Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)
~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).
Lex
Database restore help
Use Query Analyzer and run RESTORE FILELISTONLY for the backup file. Post
those results here. We'll follow up when we get those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:523266A9-C34C-47F9-9C22-FC91A9E798E7@.microsoft.com...
I need to restore a database over an existing DB (I have made a backup and
it's SQL 2000).
When I do try and restore it via SQL Enterprose Manager 2000 (the only way I
know) it it says "logical file 'database' is not part of a database
'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
RESTORE DATAVASE is terminating adbnormally.
Type:
use master
go
... before running the RESTORE. Also, be sure that no one is connected to
the DB when you restore it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
Hi I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
> From QA, run:
> RESTORE DATABASE RM_test1
> FROM DISK = 'f:\MSSQL80\BACKUP\RM_test1.bak'
> WITH REPLACE
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:9F74A63C-2524-4AE0-AEAC-9E9853D66B30@.microsoft.com...
> Many thanks:
> RESTORE FILElISTONLY from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
> I get:
> Btest_Data C:\Program Files\Microsoft SQL Server\MSSQL\Data\BRITLIVE.mdf
> D PRIMARY
> Btest_Log C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\BRITLIVE_log.ldf L NULL
>
> On the server we have these DB's:
> Btest
> RM_test1
>
> We sent a company the Btest DB to make some changes that they have done
> and
> sent the bak file back. I need to restore this over the RM_test1 DB, but
> it
> seems that it still references the original Btest DB everywhere.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OUAXLKMtHHA.4572@.TK2MSFTNGP02.phx.gbl...
>
|||Hi
That means that somebody is using the database while you are trying to
restore it. Make sure that nobody is accessing the database - including
the session you are restoring from...:-).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Gonzo wrote:
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||There is still a connection to the db somewhere. SOmetimes EM doesn't show
it. Do a sp_helpdb, get the dbid of the database that you're dealing with, do
a select * from master..sysprocess where dbid = <whatever the dbid is>, get
the coresponding spid and kill <spid>, then try to restore.
MG
"Gonzo" wrote:
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||You have to keep the logical names for the restore. You can change those
later.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
It seems I can restore it using EM (tried on a test server) but only if i
keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Data
the databse is RM_test1 but I right click on the database and go to
properties and then the tabs Data files and transaction log then the file
name is Btest_data and Btest_logs. Now a database on the live server is
already called 'Btest' will this cause a problem?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||Use
Restore Database <db_name>
from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
WITH Move 'Btest_Data' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2.mdf',
Move 'Btest_Log' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2_log.ldf',
just change the name of the .mdf & .ldf to something that doesn't already
exist.
MG
"Gonzo" wrote:
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>
|||Check out ALTER DATABASE in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
I have restored it now, how can I now change the logical names to something
else?
many thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
> You have to keep the logical names for the restore. You can change those
> later.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL
> Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>
|||No. Logical names are local to the DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:28BF2F6F-4256-4299-BE76-1F1DAAB4D61F@.microsoft.com...
I now get:
Processed 9336 pages for database 'RM_test1', file 'Btest_Data' on file 1.
Processed 1 pages for database 'RM_test1', file 'Btest_Log' on file 1.
RESTORE DATABASE successfully processed 9337 pages in 13.278 seconds (5.760
MB/sec).
There is a database caleld Btest already uses Btest for it's database name
and logical name, will this create a problem with both having the same name?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||Yep.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:46D17BBE-E208-4A83-942E-B7446A202C5A@.microsoft.com...
Woudl this be it? I would have to do this for both files I guess (this is
all new to me)
ALTER DATABASE
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23PPC$WNtHHA.4688@.TK2MSFTNGP05.phx.gbl...
> Check out ALTER DATABASE in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
> I have restored it now, how can I now change the logical names to
> something
> else?
> many thanks
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
>
|||Run:
sp_helpfile
...and post the results.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:OKu6WmNtHHA.768@.TK2MSFTNGP04.phx.gbl...
Sorry about this, I'm now getting:
ALTER DATABASE RM_TEST1
MODIFY FILE (Btest_Data = logical_file_name, RM_test1_DATA =
new_logical_name...).
Server: Msg 155, Level 15, State 1, Line 2
'Btest_Data' is not a recognized CREATE/ALTER DATABASE option.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O$$Y4jNtHHA.4824@.TK2MSFTNGP06.phx.gbl...
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:46D17BBE-E208-4A83-942E-B7446A202C5A@.microsoft.com...
> Woudl this be it? I would have to do this for both files I guess (this is
> all new to me)
> ALTER DATABASE
> MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23PPC$WNtHHA.4688@.TK2MSFTNGP05.phx.gbl...
>
those results here. We'll follow up when we get those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:523266A9-C34C-47F9-9C22-FC91A9E798E7@.microsoft.com...
I need to restore a database over an existing DB (I have made a backup and
it's SQL 2000).
When I do try and restore it via SQL Enterprose Manager 2000 (the only way I
know) it it says "logical file 'database' is not part of a database
'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
RESTORE DATAVASE is terminating adbnormally.
Type:
use master
go
... before running the RESTORE. Also, be sure that no one is connected to
the DB when you restore it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
Hi I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
> From QA, run:
> RESTORE DATABASE RM_test1
> FROM DISK = 'f:\MSSQL80\BACKUP\RM_test1.bak'
> WITH REPLACE
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:9F74A63C-2524-4AE0-AEAC-9E9853D66B30@.microsoft.com...
> Many thanks:
> RESTORE FILElISTONLY from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
> I get:
> Btest_Data C:\Program Files\Microsoft SQL Server\MSSQL\Data\BRITLIVE.mdf
> D PRIMARY
> Btest_Log C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\BRITLIVE_log.ldf L NULL
>
> On the server we have these DB's:
> Btest
> RM_test1
>
> We sent a company the Btest DB to make some changes that they have done
> and
> sent the bak file back. I need to restore this over the RM_test1 DB, but
> it
> seems that it still references the original Btest DB everywhere.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OUAXLKMtHHA.4572@.TK2MSFTNGP02.phx.gbl...
>
|||Hi
That means that somebody is using the database while you are trying to
restore it. Make sure that nobody is accessing the database - including
the session you are restoring from...:-).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Gonzo wrote:
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||There is still a connection to the db somewhere. SOmetimes EM doesn't show
it. Do a sp_helpdb, get the dbid of the database that you're dealing with, do
a select * from master..sysprocess where dbid = <whatever the dbid is>, get
the coresponding spid and kill <spid>, then try to restore.
MG
"Gonzo" wrote:
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||You have to keep the logical names for the restore. You can change those
later.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
It seems I can restore it using EM (tried on a test server) but only if i
keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Data
the databse is RM_test1 but I right click on the database and go to
properties and then the tabs Data files and transaction log then the file
name is Btest_data and Btest_logs. Now a database on the live server is
already called 'Btest' will this cause a problem?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||Use
Restore Database <db_name>
from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
WITH Move 'Btest_Data' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2.mdf',
Move 'Btest_Log' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2_log.ldf',
just change the name of the .mdf & .ldf to something that doesn't already
exist.
MG
"Gonzo" wrote:
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>
|||Check out ALTER DATABASE in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
I have restored it now, how can I now change the logical names to something
else?
many thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
> You have to keep the logical names for the restore. You can change those
> later.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL
> Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>
|||No. Logical names are local to the DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:28BF2F6F-4256-4299-BE76-1F1DAAB4D61F@.microsoft.com...
I now get:
Processed 9336 pages for database 'RM_test1', file 'Btest_Data' on file 1.
Processed 1 pages for database 'RM_test1', file 'Btest_Log' on file 1.
RESTORE DATABASE successfully processed 9337 pages in 13.278 seconds (5.760
MB/sec).
There is a database caleld Btest already uses Btest for it's database name
and logical name, will this create a problem with both having the same name?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>
|||Yep.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:46D17BBE-E208-4A83-942E-B7446A202C5A@.microsoft.com...
Woudl this be it? I would have to do this for both files I guess (this is
all new to me)
ALTER DATABASE
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23PPC$WNtHHA.4688@.TK2MSFTNGP05.phx.gbl...
> Check out ALTER DATABASE in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
> I have restored it now, how can I now change the logical names to
> something
> else?
> many thanks
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
>
|||Run:
sp_helpfile
...and post the results.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:OKu6WmNtHHA.768@.TK2MSFTNGP04.phx.gbl...
Sorry about this, I'm now getting:
ALTER DATABASE RM_TEST1
MODIFY FILE (Btest_Data = logical_file_name, RM_test1_DATA =
new_logical_name...).
Server: Msg 155, Level 15, State 1, Line 2
'Btest_Data' is not a recognized CREATE/ALTER DATABASE option.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O$$Y4jNtHHA.4824@.TK2MSFTNGP06.phx.gbl...
> Yep.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:46D17BBE-E208-4A83-942E-B7446A202C5A@.microsoft.com...
> Woudl this be it? I would have to do this for both files I guess (this is
> all new to me)
> ALTER DATABASE
> MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23PPC$WNtHHA.4688@.TK2MSFTNGP05.phx.gbl...
>
Database restore fixes problem - why?
We are using a SQL Server 7.0 database for a financial software package that
we use. Every year we run into problems with the software package not
working properly on a certain function. The solution to fixing the problem
we have discovered is to simply back-up the database and then restore it.
After doing the restore of the database, the problem is fixed. Does anyone
have any idea why? And, if so, can you tell me what could be down to prevent
the problem in the first place? What is it about a restore that would change
something enough to make it suddenly start working correctly? Thanks for
anything you can offer. This is driving me nuts.
JeffJeff
You did not say what is the problem?
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> We are using a SQL Server 7.0 database for a financial software package
> that
> we use. Every year we run into problems with the software package not
> working properly on a certain function. The solution to fixing the
> problem
> we have discovered is to simply back-up the database and then restore it.
> After doing the restore of the database, the problem is fixed. Does
> anyone
> have any idea why? And, if so, can you tell me what could be down to
> prevent
> the problem in the first place? What is it about a restore that would
> change
> something enough to make it suddenly start working correctly? Thanks for
> anything you can offer. This is driving me nuts.
> Jeff|||Uri,
The specific problem is within the financial sotfware - when we do a year
end close the software creates a batch of transactions then the next step is
to "post" those transactions. The software creates the batch OK, but when we
try to post the batch it just crashes the program without giving any error
messages.
After the restore, everything works as it should.
"Uri Dimant" wrote:
> Jeff
> You did not say what is the problem?
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> > We are using a SQL Server 7.0 database for a financial software package
> > that
> > we use. Every year we run into problems with the software package not
> > working properly on a certain function. The solution to fixing the
> > problem
> > we have discovered is to simply back-up the database and then restore it.
> > After doing the restore of the database, the problem is fixed. Does
> > anyone
> > have any idea why? And, if so, can you tell me what could be down to
> > prevent
> > the problem in the first place? What is it about a restore that would
> > change
> > something enough to make it suddenly start working correctly? Thanks for
> > anything you can offer. This is driving me nuts.
> >
> > Jeff
>
>|||Jeff,
without understand what your financial system does and without any errors
whatsoever, you can imagine that this is a needle in a haystack response!
If this happens everytime without fail, I suggest replicating it in you test
environment and profiling what happens to start with.
Immy
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> Uri,
> The specific problem is within the financial sotfware - when we do a year
> end close the software creates a batch of transactions then the next step
> is
> to "post" those transactions. The software creates the batch OK, but when
> we
> try to post the batch it just crashes the program without giving any error
> messages.
> After the restore, everything works as it should.
> "Uri Dimant" wrote:
>> Jeff
>> You did not say what is the problem?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> > We are using a SQL Server 7.0 database for a financial software package
>> > that
>> > we use. Every year we run into problems with the software package not
>> > working properly on a certain function. The solution to fixing the
>> > problem
>> > we have discovered is to simply back-up the database and then restore
>> > it.
>> > After doing the restore of the database, the problem is fixed. Does
>> > anyone
>> > have any idea why? And, if so, can you tell me what could be down to
>> > prevent
>> > the problem in the first place? What is it about a restore that would
>> > change
>> > something enough to make it suddenly start working correctly? Thanks
>> > for
>> > anything you can offer. This is driving me nuts.
>> >
>> > Jeff
>>|||Jeff
I still don't undertsand how does it relate to SQL Server? Is it possible
that the problem is in software program?
>but when we
> try to post the batch it just crashes the program without giving any error
> messages.
What does it mean? The program tries to insert the data into a table and
then it crashes?
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> Uri,
> The specific problem is within the financial sotfware - when we do a year
> end close the software creates a batch of transactions then the next step
> is
> to "post" those transactions. The software creates the batch OK, but when
> we
> try to post the batch it just crashes the program without giving any error
> messages.
> After the restore, everything works as it should.
> "Uri Dimant" wrote:
>> Jeff
>> You did not say what is the problem?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> > We are using a SQL Server 7.0 database for a financial software package
>> > that
>> > we use. Every year we run into problems with the software package not
>> > working properly on a certain function. The solution to fixing the
>> > problem
>> > we have discovered is to simply back-up the database and then restore
>> > it.
>> > After doing the restore of the database, the problem is fixed. Does
>> > anyone
>> > have any idea why? And, if so, can you tell me what could be down to
>> > prevent
>> > the problem in the first place? What is it about a restore that would
>> > change
>> > something enough to make it suddenly start working correctly? Thanks
>> > for
>> > anything you can offer. This is driving me nuts.
>> >
>> > Jeff
>>|||Uri,
It's possible that it is within the software, but what I'm wondering is -
what is it about a SQL database restore that would cause the same software
code to go from not working to working all of a sudden? What changes occur
to the database when a restore is done?
I realize this is a tough one to answer, I was just hoping that you might
have some ideas as to why the restore would "fix" things.
"Uri Dimant" wrote:
> Jeff
> I still don't undertsand how does it relate to SQL Server? Is it possible
> that the problem is in software program?
> >but when we
> > try to post the batch it just crashes the program without giving any error
> > messages.
> What does it mean? The program tries to insert the data into a table and
> then it crashes?
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> > Uri,
> >
> > The specific problem is within the financial sotfware - when we do a year
> > end close the software creates a batch of transactions then the next step
> > is
> > to "post" those transactions. The software creates the batch OK, but when
> > we
> > try to post the batch it just crashes the program without giving any error
> > messages.
> >
> > After the restore, everything works as it should.
> >
> > "Uri Dimant" wrote:
> >
> >> Jeff
> >> You did not say what is the problem?
> >>
> >>
> >>
> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> >> > We are using a SQL Server 7.0 database for a financial software package
> >> > that
> >> > we use. Every year we run into problems with the software package not
> >> > working properly on a certain function. The solution to fixing the
> >> > problem
> >> > we have discovered is to simply back-up the database and then restore
> >> > it.
> >> > After doing the restore of the database, the problem is fixed. Does
> >> > anyone
> >> > have any idea why? And, if so, can you tell me what could be down to
> >> > prevent
> >> > the problem in the first place? What is it about a restore that would
> >> > change
> >> > something enough to make it suddenly start working correctly? Thanks
> >> > for
> >> > anything you can offer. This is driving me nuts.
> >> >
> >> > Jeff
> >>
> >>
> >>
>
>|||Jeff
I see that you would not want to "reveal" more info about your business
requirements , it is ok , however we will no be able to help you much if you
cannot provide more info
RESTORE operations does simple thing , it reads all datapages/data from the
.BAK file and create a new database assume you want to create a new one
If you had a proper database backup and then your software dod some mistake
, you can come back to the previous state of the database to restore it. It
does not fix anything
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> Uri,
> It's possible that it is within the software, but what I'm wondering is -
> what is it about a SQL database restore that would cause the same software
> code to go from not working to working all of a sudden? What changes
> occur
> to the database when a restore is done?
> I realize this is a tough one to answer, I was just hoping that you might
> have some ideas as to why the restore would "fix" things.
> "Uri Dimant" wrote:
>> Jeff
>> I still don't undertsand how does it relate to SQL Server? Is it possible
>> that the problem is in software program?
>> >but when we
>> > try to post the batch it just crashes the program without giving any
>> > error
>> > messages.
>> What does it mean? The program tries to insert the data into a table and
>> then it crashes?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
>> > Uri,
>> >
>> > The specific problem is within the financial sotfware - when we do a
>> > year
>> > end close the software creates a batch of transactions then the next
>> > step
>> > is
>> > to "post" those transactions. The software creates the batch OK, but
>> > when
>> > we
>> > try to post the batch it just crashes the program without giving any
>> > error
>> > messages.
>> >
>> > After the restore, everything works as it should.
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Jeff
>> >> You did not say what is the problem?
>> >>
>> >>
>> >>
>> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> >> > We are using a SQL Server 7.0 database for a financial software
>> >> > package
>> >> > that
>> >> > we use. Every year we run into problems with the software package
>> >> > not
>> >> > working properly on a certain function. The solution to fixing the
>> >> > problem
>> >> > we have discovered is to simply back-up the database and then
>> >> > restore
>> >> > it.
>> >> > After doing the restore of the database, the problem is fixed. Does
>> >> > anyone
>> >> > have any idea why? And, if so, can you tell me what could be down
>> >> > to
>> >> > prevent
>> >> > the problem in the first place? What is it about a restore that
>> >> > would
>> >> > change
>> >> > something enough to make it suddenly start working correctly?
>> >> > Thanks
>> >> > for
>> >> > anything you can offer. This is driving me nuts.
>> >> >
>> >> > Jeff
>> >>
>> >>
>> >>
>>|||Uri,
It's not so much that I don't want to "reveal" anything, it's just that I
don't know exactly what the software is doing as it hits the database. It's
a third party software, and they have no idea why just doing a restore works.
So I thought I would pose the question to someone more knowledgable on the
SQL database side.
Somethings that I was thinking might be going on (keep in mind I am in no
way an expect on any of this) - maybe there is a bad sector on the disk, and
doing the restore ends up writing the data necessary for the Year End process
to complete correctly to a different sector of the disk. Or maybe something
like - the restore causes indexes to be updated, or causes page files to be
readjusted or anything like that.
"Uri Dimant" wrote:
> Jeff
> I see that you would not want to "reveal" more info about your business
> requirements , it is ok , however we will no be able to help you much if you
> cannot provide more info
> RESTORE operations does simple thing , it reads all datapages/data from the
> ..BAK file and create a new database assume you want to create a new one
> If you had a proper database backup and then your software dod some mistake
> , you can come back to the previous state of the database to restore it. It
> does not fix anything
>
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> > Uri,
> >
> > It's possible that it is within the software, but what I'm wondering is -
> > what is it about a SQL database restore that would cause the same software
> > code to go from not working to working all of a sudden? What changes
> > occur
> > to the database when a restore is done?
> >
> > I realize this is a tough one to answer, I was just hoping that you might
> > have some ideas as to why the restore would "fix" things.
> >
> > "Uri Dimant" wrote:
> >
> >> Jeff
> >> I still don't undertsand how does it relate to SQL Server? Is it possible
> >> that the problem is in software program?
> >>
> >> >but when we
> >> > try to post the batch it just crashes the program without giving any
> >> > error
> >> > messages.
> >>
> >> What does it mean? The program tries to insert the data into a table and
> >> then it crashes?
> >>
> >>
> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> >> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> >> > Uri,
> >> >
> >> > The specific problem is within the financial sotfware - when we do a
> >> > year
> >> > end close the software creates a batch of transactions then the next
> >> > step
> >> > is
> >> > to "post" those transactions. The software creates the batch OK, but
> >> > when
> >> > we
> >> > try to post the batch it just crashes the program without giving any
> >> > error
> >> > messages.
> >> >
> >> > After the restore, everything works as it should.
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Jeff
> >> >> You did not say what is the problem?
> >> >>
> >> >>
> >> >>
> >> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> >> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> >> >> > We are using a SQL Server 7.0 database for a financial software
> >> >> > package
> >> >> > that
> >> >> > we use. Every year we run into problems with the software package
> >> >> > not
> >> >> > working properly on a certain function. The solution to fixing the
> >> >> > problem
> >> >> > we have discovered is to simply back-up the database and then
> >> >> > restore
> >> >> > it.
> >> >> > After doing the restore of the database, the problem is fixed. Does
> >> >> > anyone
> >> >> > have any idea why? And, if so, can you tell me what could be down
> >> >> > to
> >> >> > prevent
> >> >> > the problem in the first place? What is it about a restore that
> >> >> > would
> >> >> > change
> >> >> > something enough to make it suddenly start working correctly?
> >> >> > Thanks
> >> >> > for
> >> >> > anything you can offer. This is driving me nuts.
> >> >> >
> >> >> > Jeff
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> Uri,
> It's possible that it is within the software, but what I'm wondering is -
> what is it about a SQL database restore that would cause the same software
> code to go from not working to working all of a sudden? What changes
> occur
> to the database when a restore is done?
> I realize this is a tough one to answer, I was just hoping that you might
> have some ideas as to why the restore would "fix" things.
>
Well a restore will finish rolling forward or backwards any pending
transactions depending on where they were.
It's possible your financial app posts some large transaction and never sees
it complete and hence locks up.
In any event I think you need to take it up with the financial app vendor.
One option though is to run profiler and record what the app is doing and
seeing if there's anything strange going on there.
And are you sure the app is locking up, or simply waiting for a huge
transaction to commit on the SQL side?
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||There are likely other steps you can take before the restore
that can help you track down what the issues are -
have you run DBCC checks when you hit this?
Are you checking for open transactions?
Are you monitoring with profiler? Are you executing sp_who2,
checking sysprocesses, checking wait stats, locking,
blocking, etc?
Those would probably be the first steps anyway and you'd
want to go from there - depending on the results.
-Sue
On Tue, 20 Feb 2007 06:22:50 -0800, Jeff
<Jeff@.discussions.microsoft.com> wrote:
>Uri,
>It's possible that it is within the software, but what I'm wondering is -
>what is it about a SQL database restore that would cause the same software
>code to go from not working to working all of a sudden? What changes occur
>to the database when a restore is done?
>I realize this is a tough one to answer, I was just hoping that you might
>have some ideas as to why the restore would "fix" things.
>"Uri Dimant" wrote:
>> Jeff
>> I still don't undertsand how does it relate to SQL Server? Is it possible
>> that the problem is in software program?
>> >but when we
>> > try to post the batch it just crashes the program without giving any error
>> > messages.
>> What does it mean? The program tries to insert the data into a table and
>> then it crashes?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
>> > Uri,
>> >
>> > The specific problem is within the financial sotfware - when we do a year
>> > end close the software creates a batch of transactions then the next step
>> > is
>> > to "post" those transactions. The software creates the batch OK, but when
>> > we
>> > try to post the batch it just crashes the program without giving any error
>> > messages.
>> >
>> > After the restore, everything works as it should.
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Jeff
>> >> You did not say what is the problem?
>> >>
>> >>
>> >>
>> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> >> > We are using a SQL Server 7.0 database for a financial software package
>> >> > that
>> >> > we use. Every year we run into problems with the software package not
>> >> > working properly on a certain function. The solution to fixing the
>> >> > problem
>> >> > we have discovered is to simply back-up the database and then restore
>> >> > it.
>> >> > After doing the restore of the database, the problem is fixed. Does
>> >> > anyone
>> >> > have any idea why? And, if so, can you tell me what could be down to
>> >> > prevent
>> >> > the problem in the first place? What is it about a restore that would
>> >> > change
>> >> > something enough to make it suddenly start working correctly? Thanks
>> >> > for
>> >> > anything you can offer. This is driving me nuts.
>> >> >
>> >> > Jeff
>> >>
>> >>
>> >>
>>
we use. Every year we run into problems with the software package not
working properly on a certain function. The solution to fixing the problem
we have discovered is to simply back-up the database and then restore it.
After doing the restore of the database, the problem is fixed. Does anyone
have any idea why? And, if so, can you tell me what could be down to prevent
the problem in the first place? What is it about a restore that would change
something enough to make it suddenly start working correctly? Thanks for
anything you can offer. This is driving me nuts.
JeffJeff
You did not say what is the problem?
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> We are using a SQL Server 7.0 database for a financial software package
> that
> we use. Every year we run into problems with the software package not
> working properly on a certain function. The solution to fixing the
> problem
> we have discovered is to simply back-up the database and then restore it.
> After doing the restore of the database, the problem is fixed. Does
> anyone
> have any idea why? And, if so, can you tell me what could be down to
> prevent
> the problem in the first place? What is it about a restore that would
> change
> something enough to make it suddenly start working correctly? Thanks for
> anything you can offer. This is driving me nuts.
> Jeff|||Uri,
The specific problem is within the financial sotfware - when we do a year
end close the software creates a batch of transactions then the next step is
to "post" those transactions. The software creates the batch OK, but when we
try to post the batch it just crashes the program without giving any error
messages.
After the restore, everything works as it should.
"Uri Dimant" wrote:
> Jeff
> You did not say what is the problem?
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> > We are using a SQL Server 7.0 database for a financial software package
> > that
> > we use. Every year we run into problems with the software package not
> > working properly on a certain function. The solution to fixing the
> > problem
> > we have discovered is to simply back-up the database and then restore it.
> > After doing the restore of the database, the problem is fixed. Does
> > anyone
> > have any idea why? And, if so, can you tell me what could be down to
> > prevent
> > the problem in the first place? What is it about a restore that would
> > change
> > something enough to make it suddenly start working correctly? Thanks for
> > anything you can offer. This is driving me nuts.
> >
> > Jeff
>
>|||Jeff,
without understand what your financial system does and without any errors
whatsoever, you can imagine that this is a needle in a haystack response!
If this happens everytime without fail, I suggest replicating it in you test
environment and profiling what happens to start with.
Immy
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> Uri,
> The specific problem is within the financial sotfware - when we do a year
> end close the software creates a batch of transactions then the next step
> is
> to "post" those transactions. The software creates the batch OK, but when
> we
> try to post the batch it just crashes the program without giving any error
> messages.
> After the restore, everything works as it should.
> "Uri Dimant" wrote:
>> Jeff
>> You did not say what is the problem?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> > We are using a SQL Server 7.0 database for a financial software package
>> > that
>> > we use. Every year we run into problems with the software package not
>> > working properly on a certain function. The solution to fixing the
>> > problem
>> > we have discovered is to simply back-up the database and then restore
>> > it.
>> > After doing the restore of the database, the problem is fixed. Does
>> > anyone
>> > have any idea why? And, if so, can you tell me what could be down to
>> > prevent
>> > the problem in the first place? What is it about a restore that would
>> > change
>> > something enough to make it suddenly start working correctly? Thanks
>> > for
>> > anything you can offer. This is driving me nuts.
>> >
>> > Jeff
>>|||Jeff
I still don't undertsand how does it relate to SQL Server? Is it possible
that the problem is in software program?
>but when we
> try to post the batch it just crashes the program without giving any error
> messages.
What does it mean? The program tries to insert the data into a table and
then it crashes?
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> Uri,
> The specific problem is within the financial sotfware - when we do a year
> end close the software creates a batch of transactions then the next step
> is
> to "post" those transactions. The software creates the batch OK, but when
> we
> try to post the batch it just crashes the program without giving any error
> messages.
> After the restore, everything works as it should.
> "Uri Dimant" wrote:
>> Jeff
>> You did not say what is the problem?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> > We are using a SQL Server 7.0 database for a financial software package
>> > that
>> > we use. Every year we run into problems with the software package not
>> > working properly on a certain function. The solution to fixing the
>> > problem
>> > we have discovered is to simply back-up the database and then restore
>> > it.
>> > After doing the restore of the database, the problem is fixed. Does
>> > anyone
>> > have any idea why? And, if so, can you tell me what could be down to
>> > prevent
>> > the problem in the first place? What is it about a restore that would
>> > change
>> > something enough to make it suddenly start working correctly? Thanks
>> > for
>> > anything you can offer. This is driving me nuts.
>> >
>> > Jeff
>>|||Uri,
It's possible that it is within the software, but what I'm wondering is -
what is it about a SQL database restore that would cause the same software
code to go from not working to working all of a sudden? What changes occur
to the database when a restore is done?
I realize this is a tough one to answer, I was just hoping that you might
have some ideas as to why the restore would "fix" things.
"Uri Dimant" wrote:
> Jeff
> I still don't undertsand how does it relate to SQL Server? Is it possible
> that the problem is in software program?
> >but when we
> > try to post the batch it just crashes the program without giving any error
> > messages.
> What does it mean? The program tries to insert the data into a table and
> then it crashes?
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> > Uri,
> >
> > The specific problem is within the financial sotfware - when we do a year
> > end close the software creates a batch of transactions then the next step
> > is
> > to "post" those transactions. The software creates the batch OK, but when
> > we
> > try to post the batch it just crashes the program without giving any error
> > messages.
> >
> > After the restore, everything works as it should.
> >
> > "Uri Dimant" wrote:
> >
> >> Jeff
> >> You did not say what is the problem?
> >>
> >>
> >>
> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> >> > We are using a SQL Server 7.0 database for a financial software package
> >> > that
> >> > we use. Every year we run into problems with the software package not
> >> > working properly on a certain function. The solution to fixing the
> >> > problem
> >> > we have discovered is to simply back-up the database and then restore
> >> > it.
> >> > After doing the restore of the database, the problem is fixed. Does
> >> > anyone
> >> > have any idea why? And, if so, can you tell me what could be down to
> >> > prevent
> >> > the problem in the first place? What is it about a restore that would
> >> > change
> >> > something enough to make it suddenly start working correctly? Thanks
> >> > for
> >> > anything you can offer. This is driving me nuts.
> >> >
> >> > Jeff
> >>
> >>
> >>
>
>|||Jeff
I see that you would not want to "reveal" more info about your business
requirements , it is ok , however we will no be able to help you much if you
cannot provide more info
RESTORE operations does simple thing , it reads all datapages/data from the
.BAK file and create a new database assume you want to create a new one
If you had a proper database backup and then your software dod some mistake
, you can come back to the previous state of the database to restore it. It
does not fix anything
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> Uri,
> It's possible that it is within the software, but what I'm wondering is -
> what is it about a SQL database restore that would cause the same software
> code to go from not working to working all of a sudden? What changes
> occur
> to the database when a restore is done?
> I realize this is a tough one to answer, I was just hoping that you might
> have some ideas as to why the restore would "fix" things.
> "Uri Dimant" wrote:
>> Jeff
>> I still don't undertsand how does it relate to SQL Server? Is it possible
>> that the problem is in software program?
>> >but when we
>> > try to post the batch it just crashes the program without giving any
>> > error
>> > messages.
>> What does it mean? The program tries to insert the data into a table and
>> then it crashes?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
>> > Uri,
>> >
>> > The specific problem is within the financial sotfware - when we do a
>> > year
>> > end close the software creates a batch of transactions then the next
>> > step
>> > is
>> > to "post" those transactions. The software creates the batch OK, but
>> > when
>> > we
>> > try to post the batch it just crashes the program without giving any
>> > error
>> > messages.
>> >
>> > After the restore, everything works as it should.
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Jeff
>> >> You did not say what is the problem?
>> >>
>> >>
>> >>
>> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> >> > We are using a SQL Server 7.0 database for a financial software
>> >> > package
>> >> > that
>> >> > we use. Every year we run into problems with the software package
>> >> > not
>> >> > working properly on a certain function. The solution to fixing the
>> >> > problem
>> >> > we have discovered is to simply back-up the database and then
>> >> > restore
>> >> > it.
>> >> > After doing the restore of the database, the problem is fixed. Does
>> >> > anyone
>> >> > have any idea why? And, if so, can you tell me what could be down
>> >> > to
>> >> > prevent
>> >> > the problem in the first place? What is it about a restore that
>> >> > would
>> >> > change
>> >> > something enough to make it suddenly start working correctly?
>> >> > Thanks
>> >> > for
>> >> > anything you can offer. This is driving me nuts.
>> >> >
>> >> > Jeff
>> >>
>> >>
>> >>
>>|||Uri,
It's not so much that I don't want to "reveal" anything, it's just that I
don't know exactly what the software is doing as it hits the database. It's
a third party software, and they have no idea why just doing a restore works.
So I thought I would pose the question to someone more knowledgable on the
SQL database side.
Somethings that I was thinking might be going on (keep in mind I am in no
way an expect on any of this) - maybe there is a bad sector on the disk, and
doing the restore ends up writing the data necessary for the Year End process
to complete correctly to a different sector of the disk. Or maybe something
like - the restore causes indexes to be updated, or causes page files to be
readjusted or anything like that.
"Uri Dimant" wrote:
> Jeff
> I see that you would not want to "reveal" more info about your business
> requirements , it is ok , however we will no be able to help you much if you
> cannot provide more info
> RESTORE operations does simple thing , it reads all datapages/data from the
> ..BAK file and create a new database assume you want to create a new one
> If you had a proper database backup and then your software dod some mistake
> , you can come back to the previous state of the database to restore it. It
> does not fix anything
>
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> > Uri,
> >
> > It's possible that it is within the software, but what I'm wondering is -
> > what is it about a SQL database restore that would cause the same software
> > code to go from not working to working all of a sudden? What changes
> > occur
> > to the database when a restore is done?
> >
> > I realize this is a tough one to answer, I was just hoping that you might
> > have some ideas as to why the restore would "fix" things.
> >
> > "Uri Dimant" wrote:
> >
> >> Jeff
> >> I still don't undertsand how does it relate to SQL Server? Is it possible
> >> that the problem is in software program?
> >>
> >> >but when we
> >> > try to post the batch it just crashes the program without giving any
> >> > error
> >> > messages.
> >>
> >> What does it mean? The program tries to insert the data into a table and
> >> then it crashes?
> >>
> >>
> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> >> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
> >> > Uri,
> >> >
> >> > The specific problem is within the financial sotfware - when we do a
> >> > year
> >> > end close the software creates a batch of transactions then the next
> >> > step
> >> > is
> >> > to "post" those transactions. The software creates the batch OK, but
> >> > when
> >> > we
> >> > try to post the batch it just crashes the program without giving any
> >> > error
> >> > messages.
> >> >
> >> > After the restore, everything works as it should.
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Jeff
> >> >> You did not say what is the problem?
> >> >>
> >> >>
> >> >>
> >> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> >> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> >> >> > We are using a SQL Server 7.0 database for a financial software
> >> >> > package
> >> >> > that
> >> >> > we use. Every year we run into problems with the software package
> >> >> > not
> >> >> > working properly on a certain function. The solution to fixing the
> >> >> > problem
> >> >> > we have discovered is to simply back-up the database and then
> >> >> > restore
> >> >> > it.
> >> >> > After doing the restore of the database, the problem is fixed. Does
> >> >> > anyone
> >> >> > have any idea why? And, if so, can you tell me what could be down
> >> >> > to
> >> >> > prevent
> >> >> > the problem in the first place? What is it about a restore that
> >> >> > would
> >> >> > change
> >> >> > something enough to make it suddenly start working correctly?
> >> >> > Thanks
> >> >> > for
> >> >> > anything you can offer. This is driving me nuts.
> >> >> >
> >> >> > Jeff
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> Uri,
> It's possible that it is within the software, but what I'm wondering is -
> what is it about a SQL database restore that would cause the same software
> code to go from not working to working all of a sudden? What changes
> occur
> to the database when a restore is done?
> I realize this is a tough one to answer, I was just hoping that you might
> have some ideas as to why the restore would "fix" things.
>
Well a restore will finish rolling forward or backwards any pending
transactions depending on where they were.
It's possible your financial app posts some large transaction and never sees
it complete and hence locks up.
In any event I think you need to take it up with the financial app vendor.
One option though is to run profiler and record what the app is doing and
seeing if there's anything strange going on there.
And are you sure the app is locking up, or simply waiting for a huge
transaction to commit on the SQL side?
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||There are likely other steps you can take before the restore
that can help you track down what the issues are -
have you run DBCC checks when you hit this?
Are you checking for open transactions?
Are you monitoring with profiler? Are you executing sp_who2,
checking sysprocesses, checking wait stats, locking,
blocking, etc?
Those would probably be the first steps anyway and you'd
want to go from there - depending on the results.
-Sue
On Tue, 20 Feb 2007 06:22:50 -0800, Jeff
<Jeff@.discussions.microsoft.com> wrote:
>Uri,
>It's possible that it is within the software, but what I'm wondering is -
>what is it about a SQL database restore that would cause the same software
>code to go from not working to working all of a sudden? What changes occur
>to the database when a restore is done?
>I realize this is a tough one to answer, I was just hoping that you might
>have some ideas as to why the restore would "fix" things.
>"Uri Dimant" wrote:
>> Jeff
>> I still don't undertsand how does it relate to SQL Server? Is it possible
>> that the problem is in software program?
>> >but when we
>> > try to post the batch it just crashes the program without giving any error
>> > messages.
>> What does it mean? The program tries to insert the data into a table and
>> then it crashes?
>>
>> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
>> > Uri,
>> >
>> > The specific problem is within the financial sotfware - when we do a year
>> > end close the software creates a batch of transactions then the next step
>> > is
>> > to "post" those transactions. The software creates the batch OK, but when
>> > we
>> > try to post the batch it just crashes the program without giving any error
>> > messages.
>> >
>> > After the restore, everything works as it should.
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Jeff
>> >> You did not say what is the problem?
>> >>
>> >>
>> >>
>> >> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
>> >> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>> >> > We are using a SQL Server 7.0 database for a financial software package
>> >> > that
>> >> > we use. Every year we run into problems with the software package not
>> >> > working properly on a certain function. The solution to fixing the
>> >> > problem
>> >> > we have discovered is to simply back-up the database and then restore
>> >> > it.
>> >> > After doing the restore of the database, the problem is fixed. Does
>> >> > anyone
>> >> > have any idea why? And, if so, can you tell me what could be down to
>> >> > prevent
>> >> > the problem in the first place? What is it about a restore that would
>> >> > change
>> >> > something enough to make it suddenly start working correctly? Thanks
>> >> > for
>> >> > anything you can offer. This is driving me nuts.
>> >> >
>> >> > Jeff
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)