Database Unit Testing: database

Showing posts with label database. Show all posts

Thursday, March 29, 2012

Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data?
Any Help Will Be Appreciated
Hi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David
|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>

Database Structure

Hi,
Is there a way to automate a process that export the database strucuture once a day !
All the objects - Tables, Indexes, Procedures, Views Etc..
Any Help I apreciate !
Thank's
You could run a sql agent job that uses SQL-DMO to script your database.
Here is an article that I wrote that might help:
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:5964A3E3-E149-4E4D-812D-B6B62FE9C878@.microsoft.com...
> Hi,
> Is there a way to automate a process that export the database strucuture
once a day !
> All the objects - Tables, Indexes, Procedures, Views Etc..
> Any Help I apreciate !
> Thank's
>
sql

Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data'
Any Help Will Be AppreciatedHi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>

Database stress tester

Hello

Are there any database stress testing tools like database hammer bundled with any editions of 2005?

Or is there a resource kit somewhere that has one?

Thanks

this is the closest thing from MSFT http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/ .|||

third-party tool...

http://www.idera.com/Products/SQLscaler/?s=GW

|||

The original DBHammer tool works with SQL 2005, I've used it several times on it, have you tried it?

|||

Thanks for the replies

I had heard of the database hammer, but I have never used it. I will try it as, as soon as I can find a copy of it somewhere! Is it available to download?

Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
Possible? yes.
Advisable? definitely NOT.
Supported? No.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||check kb below
http://support.microsoft.com/default...b;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default...b;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas

"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
ThanksPossible? yes.
Advisable? definitely NOT.
Supported? No.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||check kb below
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default.aspx?scid=kb;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Kno
w
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel
it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files some
where else), and with
restore use the MOVE option to specify new file names. Or possibly only deta
ch, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives
you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000
).
> Databases are set up in reverse. Data is stored in .ldf, and log files ar
e
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (K
now
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log fil
e
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>sql

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.
Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database storage limitations in sql server

Whats the limitations of data storage in sqlserver DB. How will be the perforamcne if i have database which will get a data of 400 GB per year and all the data should be there in the table the whole year and then it can be archived.according to ms you can continue to scale out and up if you use multiple servers clustered together with distributed partitioned views. more hardware. more sql liscenses. no problem. right.|||And the alternative is...? 32-way 32-bit cluster with a terabyte or 2 of RAM and the SAN of SAM. You gotta go up or out. I prefer out or both. I don't expect my software for free.

Read up on Google's technology. They can go out on 386 machines - discards. And as soon as people start discarding 1GHz machines, they'll switch to a real operating system.

database still exists after deletion

hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

--Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

--The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

--The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data\\riskDatabase.mdf'";
string database_ldf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data\\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}Have someone tried to create a database then drop it in runtime?

Database status: Loading

After restoring my old transaction logs, the database
showing a status: 'Loading'. The database status still
remain 'Loading' even after 3 days.
Is there anyway I can stop the loading and will it cause
any problem to my data?Perhaps the last log was restored with NORECOVERY. In this case, you
can recover the database with the command below. This will not
introduce any data problems.
RESTORE DATABASE MyDatabase
WITH RECOVERY
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"TEO" <teo.yongjiew@.eonbank.com.my> wrote in message
news:067501c3a80b$73cf54e0$a401280a@.phx.gbl...
> After restoring my old transaction logs, the database
> showing a status: 'Loading'. The database status still
> remain 'Loading' even after 3 days.
> Is there anyway I can stop the loading and will it cause
> any problem to my data?sql

Database status check

Hey guys,

A question about database status !

I would like to do 2 checks through T-sql :

* Check if a database exists in my Sql Server

- I found following T-Sql code and it does the trick, so no problem here I guess :

Code Snippet

select * from sys.databases where name = 'Testing'

* Check if the database is Online or Offline

Here I'm having trouble selecting a good approach.

At first I would just launch a query to one of my tables, but it got me thinking, this would create a timeout if the database is offline, not ?

So how could I avoid this timeout ? I found out that the SysDatabases table has a status field I can query, but I can't find any document reference for the difference in Status Online or Offline ( I see a change when I do the test local, but I can't get a int reference to really check the value ).

NOTE : I'm on sql 2000 !

So any suggestions ?

Hey Depechie,

check out this tip: http://www.mssqltips.com/tip.asp?tip1033

success,

hansco

Database Status = Loading

A coworker is repeatedly experiencing problems restoring database backups on
a new sql virtual server (clustered). He has restored a database backup
using enterprise manager. Enterprise manager told him that the restore has
completed without giving any errors. We can not access the database and
enterprise manager shows the status of the database as "Loading". I ran the
command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
basically saying that the database was not completely loaded or the restore
did not complete. Can anyone tell me what the problem is or tell me how to
avoid this scenario?
--
-SJHi
Have you checked the SQL Server log and the System Event log to see if there
is any additional information in them?
It is usually a good idea to post the exact error numbers and messages.
John
"SJ" wrote:
> A coworker is repeatedly experiencing problems restoring database backups on
> a new sql virtual server (clustered). He has restored a database backup
> using enterprise manager. Enterprise manager told him that the restore has
> completed without giving any errors. We can not access the database and
> enterprise manager shows the status of the database as "Loading". I ran the
> command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> basically saying that the database was not completely loaded or the restore
> did not complete. Can anyone tell me what the problem is or tell me how to
> avoid this scenario?
> --
> -SJ|||Here is the error:
File '<logical file name>' was only partially restored by a database or file
restore. The entire file must be successfully restored before applying the
log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Enterprise manager told me "Restore of database '<databasename>' completed
successfully."
-SJ
"John Bell" wrote:
> Hi
> Have you checked the SQL Server log and the System Event log to see if there
> is any additional information in them?
> It is usually a good idea to post the exact error numbers and messages.
> John
> "SJ" wrote:
> > A coworker is repeatedly experiencing problems restoring database backups on
> > a new sql virtual server (clustered). He has restored a database backup
> > using enterprise manager. Enterprise manager told him that the restore has
> > completed without giving any errors. We can not access the database and
> > enterprise manager shows the status of the database as "Loading". I ran the
> > command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> > basically saying that the database was not completely loaded or the restore
> > did not complete. Can anyone tell me what the problem is or tell me how to
> > avoid this scenario?
> >
> > --
> > -SJ|||Hi SJ
Check out:
http://groups.google.com/group/microsoft.public.sqlserver.clients/browse_frm/thread/b5447588fe32acf0/527357de5278e8bd#527357de5278e8bd
John
"SJ" wrote:
> Here is the error:
> File '<logical file name>' was only partially restored by a database or file
> restore. The entire file must be successfully restored before applying the
> log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Enterprise manager told me "Restore of database '<databasename>' completed
> successfully."
> -SJ
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the SQL Server log and the System Event log to see if there
> > is any additional information in them?
> >
> > It is usually a good idea to post the exact error numbers and messages.
> >
> > John
> >
> > "SJ" wrote:
> >
> > > A coworker is repeatedly experiencing problems restoring database backups on
> > > a new sql virtual server (clustered). He has restored a database backup
> > > using enterprise manager. Enterprise manager told him that the restore has
> > > completed without giving any errors. We can not access the database and
> > > enterprise manager shows the status of the database as "Loading". I ran the
> > > command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> > > basically saying that the database was not completely loaded or the restore
> > > did not complete. Can anyone tell me what the problem is or tell me how to
> > > avoid this scenario?
> > >
> > > --
> > > -SJ

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?
When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>
sql

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>

Database Statistics

I am trying to find an efficient way to get the number of times that each ro
w
in a table is selected. I want to avoid using triggers.
Any ideas?
Thanks
John
jpd0861@.msn.comIf you're using stored procedures to access data, then I'd simply suggest
extending those to implement table-use auditing. Of course you'd need to kee
p
statistics in a separate table - something like:
PK_column : AccesedTime (default getdate()) : AccessedBy (default system_use
r)
If your db design allows direct access to tables, then maybe your auditing
requirement is just another point in favour of using procedures.
ML|||From the good news/bad news category:
Good news. We don't have SELECT triggers, so you wont have any problem
Bad news, there really isn't any other way to do this unless you are using
stored procedures, and even then it would be a big drag on performance.
Or I am missing something?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:77D095BA-04A6-4EB7-83EB-57B28AA21C51@.microsoft.com...
>I am trying to find an efficient way to get the number of times that each
>row
> in a table is selected. I want to avoid using triggers.
> Any ideas?
> Thanks
> John
> jpd0861@.msn.com|||Thank you Louis, I was not clear in my original message. I know that there
are no SELECT triggers in SQL Server...just good old INSERT, UPDATE, and
DELETE.
FYI, I did get an answer about using table-use auditing from ML that seems
to make sense.
"Louis Davidson" wrote:

> From the good news/bad news category:
> Good news. We don't have SELECT triggers, so you wont have any problem
> Bad news, there really isn't any other way to do this unless you are using
> stored procedures, and even then it would be a big drag on performance.
> Or I am missing something?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:77D095BA-04A6-4EB7-83EB-57B28AA21C51@.microsoft.com...
>
>|||I agree, I just wanted to be clear that without procs this would not be
possible. I rarely expect that people are actually using stored procedures,
but clearly this is yet another reason why they are so excellent.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:6D10890D-C733-4673-852F-81C747F2EF10@.microsoft.com...
> Thank you Louis, I was not clear in my original message. I know that there
> are no SELECT triggers in SQL Server...just good old INSERT, UPDATE, and
> DELETE.
> FYI, I did get an answer about using table-use auditing from ML that seems
> to make sense.
> "Louis Davidson" wrote:
>

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>

Thursday, March 29, 2012

Database Structure

Database Structure

Database Structure

Database stress tester

Database Stored on NaS

Database Stored on NaS

Database stored in .ldf file

Database stored in .ldf file

Database stored in .ldf file

Database storage limitations in sql server

database still exists after deletion

Database status: Loading

Database status check

Database Status = Loading

Database Status = Loading

Database Status = Loading

Database statistics

Database statistics

Database Statistics

Database statistics

Database Unit Testing

Blog Archive

About Me