How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx
.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some oth
er filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by cre
ate a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you ne
ed to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the
DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear
. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports
Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
H
Hi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H
|||Thanks Hari
I'll give it a go!
Regards
H
|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H
|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
--
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I know if it is working?

Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciatedAs the service is running try to access it using "osql" utility.

If you are talking about data files, yes you can declare the path while creating your database...

check create database syntax for thissql

How do I get AdventureWorks to show in sql2005.

How do you make the AdventureWorks sample show up (June ctp)? I already installed it using the msi file:
"C:\Program Files\Microsoft SQL Server\90\Tools\Samples\SQLServerSamples.msi"

But following these tutorial directions doesn't get me anywhere, since AdventureWorks won't show up on the database list (last step). What's missing?

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens. In the Server name text box, type the name of your SQL Server instance. In the Registered server name box, type AdventureWorks. On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.The adventureworks sample databases are not part of the MSI they are installed separately, did you select them at install time? If not then go back into Add/Remove programs and select them

-Euan|||

Yes, I selected everything in install time. I also ran: "C:\Program Files\Microsoft SQL Server\90Tools\Samples\SQLServerSamples.msi"

I see some adventureworks scripts and files in the sql server program directory, but I don't see how to make the sample show up as a database in sql management studio, or see the data.

|||The databases are not installed via that MSI, that contains the code and projects.

When you say you installed everything, did you go into the advanced tree from the component selection dialog, then drill down to sample databases and select both sample databases? They are not selected by default.

As a double check can you do a dir adv*.mdf /s from the root of the drive where you installed everything to please?

-Euan|||Yes AdventureWorks_Data.mdf and AdventureWorksDW_Data.mdf show up in my directory listing. (I installed everything by selecting "install ALL FEATURES to hard drive" on every option, exactly to avoid this kind of issue.)|||Right click on the databases folder and select All Tasks, Attach Database, point the dialog at the .mdf files and all shold be well.

-Euan|||Thanks, that works. The directions in the docs should get corrected, at least the tutorial-type, for those who don't know their way around yet.|||Fair point, actually the databases should have been auto attached and thats a bug.|||

I tried attaching Adventureworks and I get an error:

could not find row in sysindexes for database id 21, object id 1, index id 1...

could not open new database 'Adventureworks'. Create database is aborted. SQL Server Error: 602). Please advise. Thanks.

|||

This might be helpful?

The adventureWorks database installed at setup is only a starting point for creating

all the stored procedures used in the StoreFront. You must run the SQL install script

located in the C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP directory.

This procedure will create a new AdventureWorks.mdf that can be attached

in the App_Data Directory of your VS2005 project or site.

See the following

http://msdn2.microsoft.com/en-us/library/ms160715(en-US,SQL.90).aspx

|||Right click the DATABASE of the instance of your SQL server ,choose the "Attach to" ,in a new opened window click the "Add" button to find your the AdventureWorks.mdf which often is in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data " directiory. Additionally ,the .mis is AdventureWorksdb.mis.

How do I get AdventureWorks to show in sql2005.

How do you make the AdventureWorks sample show up (June ctp)? I already installed it using the msi file:
"C:\Program Files\Microsoft SQL Server\90\Tools\Samples\SQLServerSamples.msi"

But following these tutorial directions doesn't get me anywhere, since AdventureWorks won't show up on the database list (last step). What's missing?

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens. In the Server name text box, type the name of your SQL Server instance. In the Registered server name box, type AdventureWorks. On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.The adventureworks sample databases are not part of the MSI they are installed separately, did you select them at install time? If not then go back into Add/Remove programs and select them

-Euan|||

Yes, I selected everything in install time. I also ran: "C:\Program Files\Microsoft SQL Server\90Tools\Samples\SQLServerSamples.msi"

I see some adventureworks scripts and files in the sql server program directory, but I don't see how to make the sample show up as a database in sql management studio, or see the data.

|||The databases are not installed via that MSI, that contains the code and projects.

When you say you installed everything, did you go into the advanced tree from the component selection dialog, then drill down to sample databases and select both sample databases? They are not selected by default.

As a double check can you do a dir adv*.mdf /s from the root of the drive where you installed everything to please?

-Euan|||Yes AdventureWorks_Data.mdf and AdventureWorksDW_Data.mdf show up in my directory listing. (I installed everything by selecting "install ALL FEATURES to hard drive" on every option, exactly to avoid this kind of issue.)|||Right click on the databases folder and select All Tasks, Attach Database, point the dialog at the .mdf files and all shold be well.

-Euan|||Thanks, that works. The directions in the docs should get corrected, at least the tutorial-type, for those who don't know their way around yet.|||Fair point, actually the databases should have been auto attached and thats a bug.|||

I tried attaching Adventureworks and I get an error:

could not find row in sysindexes for database id 21, object id 1, index id 1...

could not open new database 'Adventureworks'. Create database is aborted. SQL Server Error: 602). Please advise. Thanks.

|||

This might be helpful?

The adventureWorks database installed at setup is only a starting point for creating

all the stored procedures used in the StoreFront. You must run the SQL install script

located in the C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP directory.

This procedure will create a new AdventureWorks.mdf that can be attached

in the App_Data Directory of your VS2005 project or site.

See the following

http://msdn2.microsoft.com/en-us/library/ms160715(en-US,SQL.90).aspx

|||Right click the DATABASE of the instance of your SQL server ,choose the "Attach to" ,in a new opened window click the "Add" button to find your the AdventureWorks.mdf which often is in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data " directiory. Additionally ,the .mis is AdventureWorksdb.mis.

How do I get AdventureWorks to show in sql2005.

How do you make the AdventureWorks sample show up (June ctp)? I already installed it using the msi file:
"C:\Program Files\Microsoft SQL Server\90\Tools\Samples\SQLServerSamples.msi"

But following these tutorial directions doesn't get me anywhere, since AdventureWorks won't show up on the database list (last step). What's missing?

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens. In the Server name text box, type the name of your SQL Server instance. In the Registered server name box, type AdventureWorks. On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.
The adventureworks sample databases are not part of the MSI they are installed separately, did you select them at install time? If not then go back into Add/Remove programs and select them

-Euan|||

Yes, I selected everything in install time. I also ran: "C:\Program Files\Microsoft SQL Server\90Tools\Samples\SQLServerSamples.msi"

I see some adventureworks scripts and files in the sql server program directory, but I don't see how to make the sample show up as a database in sql management studio, or see the data.|||The databases are not installed via that MSI, that contains the code and projects.

When you say you installed everything, did you go into the advanced tree from the component selection dialog, then drill down to sample databases and select both sample databases? They are not selected by default.

As a double check can you do a dir adv*.mdf /s from the root of the drive where you installed everything to please?

-Euan|||Yes AdventureWorks_Data.mdf and AdventureWorksDW_Data.mdf show up in my directory listing. (I installed everything by selecting "install ALL FEATURES to hard drive" on every option, exactly to avoid this kind of issue.)
|||Right click on the databases folder and select All Tasks, Attach Database, point the dialog at the .mdf files and all shold be well.

-Euan|||Thanks, that works. The directions in the docs should get corrected, at least the tutorial-type, for those who don't know their way around yet.
|||Fair point, actually the databases should have been auto attached and thats a bug.|||

I tried attaching Adventureworks and I get an error:

could not find row in sysindexes for database id 21, object id 1, index id 1...

could not open new database 'Adventureworks'. Create database is aborted. SQL Server Error: 602). Please advise. Thanks.

|||

This might be helpful?

The adventureWorks database installed at setup is only a starting point for creating

all the stored procedures used in the StoreFront. You must run the SQL install script

located in the C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP directory.

This procedure will create a new AdventureWorks.mdf that can be attached

in the App_Data Directory of your VS2005 project or site.

See the following

http://msdn2.microsoft.com/en-us/library/ms160715(en-US,SQL.90).aspx

|||Right click the DATABASE of the instance of your SQL server ,choose the "Attach to" ,in a new opened window click the "Add" button to find your the AdventureWorks.mdf which often is in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data " directiory. Additionally ,the .mis is AdventureWorksdb.mis.

How do I get AdventureWorks to show in sql2005.

How do you make the AdventureWorks sample show up (June ctp)? I already installed it using the msi file:
"C:\Program Files\Microsoft SQL Server\90\Tools\Samples\SQLServerSamples.msi"

But following these tutorial directions doesn't get me anywhere, since AdventureWorks won't show up on the database list (last step). What's missing?

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens. In the Server name text box, type the name of your SQL Server instance. In the Registered server name box, type AdventureWorks. On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.
The adventureworks sample databases are not part of the MSI they are installed separately, did you select them at install time? If not then go back into Add/Remove programs and select them

-Euan|||

Yes, I selected everything in install time. I also ran: "C:\Program Files\Microsoft SQL Server\90Tools\Samples\SQLServerSamples.msi"

I see some adventureworks scripts and files in the sql server program directory, but I don't see how to make the sample show up as a database in sql management studio, or see the data.

|||The databases are not installed via that MSI, that contains the code and projects.

When you say you installed everything, did you go into the advanced tree from the component selection dialog, then drill down to sample databases and select both sample databases? They are not selected by default.

As a double check can you do a dir adv*.mdf /s from the root of the drive where you installed everything to please?

-Euan|||Yes AdventureWorks_Data.mdf and AdventureWorksDW_Data.mdf show up in my directory listing. (I installed everything by selecting "install ALL FEATURES to hard drive" on every option, exactly to avoid this kind of issue.)
|||Right click on the databases folder and select All Tasks, Attach Database, point the dialog at the .mdf files and all shold be well.

-Euan|||Thanks, that works. The directions in the docs should get corrected, at least the tutorial-type, for those who don't know their way around yet.
|||Fair point, actually the databases should have been auto attached and thats a bug.|||

I tried attaching Adventureworks and I get an error:

could not find row in sysindexes for database id 21, object id 1, index id 1...

could not open new database 'Adventureworks'. Create database is aborted. SQL Server Error: 602). Please advise. Thanks.

|||

This might be helpful?

The adventureWorks database installed at setup is only a starting point for creating

all the stored procedures used in the StoreFront. You must run the SQL install script

located in the C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP directory.

This procedure will create a new AdventureWorks.mdf that can be attached

in the App_Data Directory of your VS2005 project or site.

See the following

http://msdn2.microsoft.com/en-us/library/ms160715(en-US,SQL.90).aspx

|||Right click the DATABASE of the instance of your SQL server ,choose the "Attach to" ,in a new opened window click the "Add" button to find your the AdventureWorks.mdf which often is in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data " directiory. Additionally ,the .mis is AdventureWorksdb.mis.

How do I find out how full my files are?

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?caseahr (caseahr@.gmail.com) writes:

Quote:

Originally Posted by

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?

It should be possible to find by querying a couple fo system tables.
But before I go ahead, I need to know which version of SQL Server you
are using, because the solution for SQL 2005 is completely different
than for previous versions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,

You may try the following commands :
sp_spaceused : gets the statistics of usage for a DB or a table. these
stats are not splitted by file.
DBCC SHOWFILESTATS : Gets the statistics of usage per data file. Log
file is ignored. The size is given in extents, depending on your system
(usually, the factor number is 64 to get the size in KB).
DBCC SQLPERF (LOGSPACE) : Gets the statistics of usage for the log
file.
sp_helpdb and sp_helpfile : gets the information about size and growth,
for the database and the files.

These functions work properly either on SQL Server 2000 and SQL Server
2005.

Hope this will fit your needs.

Cdric Del Nibbio
MCP since 2003
MCAD .NET
MCTS SQL Server 2005

caseahr a crit :

Quote:

Originally Posted by

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?

|||Merci beaucoup, Cdric. That's exactly what I needed.

Cdric Del Nibbio wrote:

Quote:

Originally Posted by

Hi,
>
You may try the following commands :
sp_spaceused : gets the statistics of usage for a DB or a table. these
stats are not splitted by file.
DBCC SHOWFILESTATS : Gets the statistics of usage per data file. Log
file is ignored. The size is given in extents, depending on your system
(usually, the factor number is 64 to get the size in KB).
DBCC SQLPERF (LOGSPACE) : Gets the statistics of usage for the log
file.
sp_helpdb and sp_helpfile : gets the information about size and growth,
for the database and the files.
>
These functions work properly either on SQL Server 2000 and SQL Server
2005.
>
Hope this will fit your needs.
>
Cdric Del Nibbio
MCP since 2003
MCAD .NET
MCTS SQL Server 2005
>
>
caseahr a crit :
>

Quote:

Originally Posted by

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?

How do I figure out where SSIS logs are?

Dear all,

I was wondering how to open .LOG files created for a SSIS execution package.

Thanks in advance,

Open them in a text editor. I'm a bag fan of Textpad (www.textpad.com)

You control where they are. You tell tell the package where to log to.

-Jamie

|||

Hi Jamie,

Come on man, textpad? Is my favourite without a doubt, I like it a lot too.

I was saying, in what path ssis leave its logs. where?

Thanks again

|||

Like I said previous, the logs are put wherever you tell SSIS to put them. That can be a text file (it is you, not SSIS, that defines where that file resides), SQL Server (in which case they'll be in the msdn..sysdtspackageslog90 table if memory serves), event log, SQL Profiler or an XML file.

-Jamie

How do I determine the backup date of a restored Database..?

Hi All,

I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...

For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...

Any help will be much appreciated.. Thanks in advance.
- AravinQuery Builder (querybuilder@.gmail.com) writes:

Quote:

Originally Posted by

I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...
>
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
>
Any help will be much appreciated.. Thanks in advance.

If the backups were taken on the same server, you may be able to dig
this out from the tables in msdb. You would have to start with
restorehistory and go backwards from there. I'm offering a query,
because I have worked very little with these tables myself. But they
are documented in Books Online, althoughly fairly briefly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- Aravin
Hello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegro ups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>> Hi All,
>> I am in a delima here. I have to a few backups restored from a set of
>> backup file. The backup files have been removed from the drive because
>> of some storage constrains...
>> For some data comparison reasons, I need to figure out the actual date
>> of these backup files that is used to restore these databases. Is
>> there any system table I can query to figure the actual backup times
>> of these databases...
>> Any help will be much appreciated.. Thanks in advance.
>> - Aravin
>

how do I determine how many columns there are in a file?

Hi,

I have a few different files in a directory.

I want to take each file and determine which columns and data types it has.

How can I know how many columns there are in a file?

Thank you.

Columns. That depends entirely on what you define a column to be. How is it delimited?

Column data-type. In a file the type of every column is text/char/call it whatever you want. A file is just a text string. You give semantic meaning to those strings (e.g. data-types) which you define yourself.

There are a million and one answers to your question given the information that you have provided. Only you can answer it.

-Jamie

|||

Jamie,

let's assume that I get a csv file from my customer.

I don't know how many columns there are in that file (and I don't want to count it by myself).

I wanted to know which component can take this file and tell me how many columns it has.

This is stage 1.

|||

The Flat File allows you to define a delimiter and therefore tell you how many columns there are.

-Jamie

|||

suppose I use a "flat file" component, and I see that there are 4 columns.

I want to use the "for each" component and apply a "data flow task" on each one of the columns.

The "for each" component will take one column at a time, and run 4 times.

I want to use the same "data flow task" for all of the columns.

How can I do it automatically?

|||

one way is to start a new project in vs as ssis

right click over solution explorer->SSIS Packages and select import and export wizard

select your flat file as source and destination as server, by this way create a new package for every text file, later you can copy and paste of each package contents into one and with little change this will be work for you and hope you get column list by looking at tables.

|||

reut wrote:

suppose I use a "flat file" component, and I see that there are 4 columns.

I want to use the "for each" component and apply a "data flow task" on each one of the columns.

The "for each" component will take one column at a time, and run 4 times.

I want to use the same "data flow task" for all of the columns.

How can I do it automatically?

Data flows operate on datasets, not on a column at a time. Even if you could do what you want to (which you can't - see below) I would recommend not doing it.

The reason that you can't apply the same data-flow to different data set (which in your case would exist of a single column) is because the metadata of the the data-flow (i.e. the columns) is set at design-time and cannot be changed when the package is executed.

-Jamie

|||

This is a good way, thanks for that, but what do I do if I have 30 different files with 100 columns in each one of them, and I don"t want to create 30 different packages?...

I want to create one "smart" packege, that will take a file, take a column, and insert the relevant data into a table on the server.

If I decide to do it with the "for each" component, it will run 30*100 times.

The resault has to be something like this:

fileName columnName dataType

xxx.csv column1 int

xxx.csv column2 date

...

xxx.csv column100 varchar

yyy.csv column1 date

...

How do I do it?

Thank you for your patience.

|||

you can create a raw table with columns from 1 to 500, then fetch every text file into it and call that data flow in your for each loop and then you better know which column tells you about specific table then put the data into that table from raw table by calling just one stored proc from your ssis, and that sp will decide data insert into that specific table based on data

|||

Your only option is to create a "Smart" custom source that is either a script source or a script component. Your custom component would take the column name, or position and output the relevant column into the data flow.

You could also unpivot the data so that your columns were now rows. You then filter the flow based on the column you want to process.