How do I move a database I have developed on my local server (2005) to my client?

Hi all. Probably a simple thing to do but I need to know how to do this. I
have to move the database I created in 2005 Enterprise to 2005 Express on my
client site. Do I just do a back up and burn to a CD and after installing
2005 Express restore the backup? Don't I need the .mdf and the .ldf files
or does the backup procedure in the Studio backup both? I set it to backup
the Database.
Regards,
RobertYou can A) drop the database, copy the mdf and ldf files to CD or
somewhere that you can move them and at the client site attach them or
b) do a backup of the database and restore at the client site. Did you
use any Enterprise features that Express may not support?
Robert Johnson wrote:
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on my
> client site. Do I just do a back up and burn to a CD and after installing
> 2005 Express restore the backup? Don't I need the .mdf and the .ldf files
> or does the backup procedure in the Studio backup both? I set it to backup
> the Database.
> Regards,
> Robert
>|||I don't believe so.. Just added tables and one view. No triggers, functions
or stuff like that. Relationships between tables and constraints are in
place. The client has less than 5 concurent users so Express should work
fine for him. It's a very small client and small database needs.
Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
think that would work. I'll give that a shot.
Regards and Thanks
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
> You can A) drop the database, copy the mdf and ldf files to CD or
> somewhere that you can move them and at the client site attach them or b)
> do a backup of the database and restore at the client site. Did you use
> any Enterprise features that Express may not support?
>
> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert|||I had to ask <G>. I use the attach/detach all the time. After I've
copied the ldf and mdf I reattach to the original db. That's how I move
from our test to production server.
Robert Johnson wrote:
> I don't believe so.. Just added tables and one view. No triggers, functions
> or stuff like that. Relationships between tables and constraints are in
> place. The client has less than 5 concurent users so Express should work
> fine for him. It's a very small client and small database needs.
> Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
> think that would work. I'll give that a shot.
> Regards and Thanks
> Robert
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or b)
>> do a backup of the database and restore at the client site. Did you use
>> any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>|||Thanks again Brett. I'll test it out tomorrow on site. If your ears are
burning...lol.
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:eKtpB4UBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>I had to ask <G>. I use the attach/detach all the time. After I've copied
>the ldf and mdf I reattach to the original db. That's how I move from our
>test to production server.
> Robert Johnson wrote:
>> I don't believe so.. Just added tables and one view. No triggers,
>> functions or stuff like that. Relationships between tables and
>> constraints are in place. The client has less than 5 concurent users so
>> Express should work fine for him. It's a very small client and small
>> database needs. Interesting idea about detaching and copy the mdf and ldf
>> to CD. Didn't think that would work. I'll give that a shot.
>> Regards and Thanks
>> Robert
>> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
>> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or
>> b) do a backup of the database and restore at the client site. Did you
>> use any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do
>> this. I have to move the database I created in 2005 Enterprise to 2005
>> Express on my client site. Do I just do a back up and burn to a CD and
>> after installing 2005 Express restore the backup? Don't I need the
>> .mdf and the .ldf files or does the backup procedure in the Studio
>> backup both? I set it to backup the Database.
>> Regards,
>> Robert
>>|||Hello Robert,
When you take a backup of a database, you do not need mdf or ldf files of
your database. *.BAK files (which is the backup file) contains all that
files.
This is a comman way used to copy databases or as Brett mentioned, you can
attach\detach your database files.
For additional info: Attaching and Detaching a database is the fastest
method to copy or move a database. However, it takes your database offline
and this would not be good for production environments. So, backing up a
database does not cause a problem like this.
Ekrem Önsoy
"Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on
> my client site. Do I just do a back up and burn to a CD and after
> installing 2005 Express restore the backup? Don't I need the .mdf and the
> .ldf files or does the backup procedure in the Studio backup both? I set
> it to backup the Database.
> Regards,
> Robert
>|||Thanks Ekrem. I wasn't sure if it had all that I needed.
Regards,
Robert
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:F98D9090-F8AE-4DF2-AEFC-30898DE47197@.microsoft.com...
> Hello Robert,
>
> When you take a backup of a database, you do not need mdf or ldf files of
> your database. *.BAK files (which is the backup file) contains all that
> files.
> This is a comman way used to copy databases or as Brett mentioned, you can
> attach\detach your database files.
> For additional info: Attaching and Detaching a database is the fastest
> method to copy or move a database. However, it takes your database offline
> and this would not be good for production environments. So, backing up a
> database does not cause a problem like this.
>
> --
> Ekrem Önsoy
>
> "Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
> news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>>
>

How do I force new databases to be created on another drive on same server?

I successfuly installed SQL Server 2005 Enterprise Edition today (on the first shot!) but can't figure out how to force new databases, etc to be created on another drive on the server. SQL installed on the c: drive (I had no choice!) but it is a very small partition. I need to install Sharepoint Portal 2003 next (on a different server) and want to make sure everything goes on the other drive.

Help me, please!!

Thanks :-)

As far as I can remember Sharepoint let you choose the destination of the data files. Anyway the default data directory can be changed in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thanks, I'll give it a try.|||

I just tried it and it is still creating new databases on c: drive. We had to slick the server for another reason, so I have a fresh install and told it to install to d:\Program Files\.... during installation and the registry entry did not change; it still pointed to c: drive. I created a d:\Program Files\Microsoft SQL Server\Data directory and changed the registry and no joy. Even restarted the server a few times and it didn't matter.

Shouldn't there be a way to change this default in the Server Management Studio?!?

If any can help me, it would be appreciated! This problem is holding up installation of a few more apps.

Thanks!!

|||Sorry for that I copied the wrong key :-(

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407351&SiteID=1

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks. I actually found out where to do it in the SQL Server Management Studio!

SERVER--PROPERTIES--DATABASE SETTINGS

The path for databases and logs are blank. After I selected my folders on the D: drive I created a new db and it went to the correct path! Whew. Glad that's over with :-)

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 export an MDF file to another DB with SQL Server 2005?

Hi whoever can help,

I have created a web site using VWD platform.

DB tables have been created with the build in facility within Visual Web Developer tool.

Now that I have hosted my site (towardes production) I do need to pupulate/create all the tables on the hosting server (that includes ASPNETDB tables).

I guess some export tool can help (have tried with SQL Server Management Studio Express but I can not figure out how to perform the task.

Can you please help on that.

Thanks & Best Regards

hi,

if you can "copy" the database's files (.Mdf and .Ldf) to the web hosting server, you can execute an attach of the database (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp ) via some sort of gui provided by your hoster...

or, you can generate the Data Definition Language scripts to create that objects via SQL Server Management Studio Express (or amScript, a free tool of mine you can find in the Download2005 area of my site, see my sign.)... then you have to execute those scripts (again, via some sort of gui provided by your hoster).. as regard the "data" part, you have to provide a way to generate INSERT INTO scripts (currently working on such a free tool as well) or BCP (Bulk Copy Program, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_2e5s.asp, http://msdn2.microsoft.com/en-us/library/ms162802.aspx) to export theme to file and then bulk insert theme onto the destination server...

regards

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005's taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?

How do I display the day name instead of the day #

Hi,

I created a Time Dimension and ended up with the following (as shown in the attached image). The problem is that when I go to the Day level, I see numbers e.g. 1, 2, ..., 31. Instead I want to display the Weekday Name e.g. Monday, Tueday and so on (i.e. what we get with the datename(dw,<date>) function. Right?)

However I am not sure what to change and where in order to get the desired results. Can someone kindly guide me.

Thanks.use:
datename(dw,"dbo"."TimeDimension"."TheDate")

ie

select datename(dw,getdate())|||use:
datename(dw,"dbo"."TimeDimension"."TheDate")


Hi hirshal.

I tried entering the above in the Member Name Column and the Member Key column but did not get the desired effect :(

Can you kindly guide? Do I need to enter the above quoted formula in both fields (Member Name Column and Member Key column) or in only one of these?

Thanks.