Difference between Shrinking a DB and a FILE

Hi,

What is the difference between shrinking a DB with that of a file.

When you select a DB to shrink will it not shrink all the files in it.

Because when I select Files to shrink it does not prompt me with
a specific file.

So I am confused as to why SQL server 2K5 asks if you need to shrink DB or FILE

Tnx

Shrink database shrinks all the files in a database, shrink file shrinks one specific file in a database.

The way that data files(.mdfs) and log files(.ldfs) shrink is different, you should read in Books On Line about it.

Difference between Partial Backup (SQL 2005) and File Group Backup

What is the difference between "Files aned File Groups" backup and Partial Backup?

Looks like both are same.. Please comment.

A File Group backup is a backup which only saves the data from the named file group(s).

A Partial backup is also a backup which only saves the data from some file groups, but it usually refers to the syntax:

BACKUP DATABASE foo READ_WRITE_FILEGROUPS TO....

which backs up only the primary filegroup and any read-write filegroups.

If you have a good backup of the read-only filegroups, you don't need to save them at every backup, which limits volume.

So, a Partial backup is a special case of a File Group backup.

Difference between Flat File Source Output ?

Can someone tell me the difference between the Flat File Source Output - External Columns and Output Columns ?

I always end up changing the datatype properities in both to make things work :-)

? Hi, Have you tried setting the datatypes for the external columns using the Advanced pane of the Flat File Connection Manager? What you set there ought to be carried through to the Output columns. Andrew Watt [MVP] <cgpl@.discussions.microsoft..com> wrote in message news:760e2f6b-3480-4430-850e-8b30237e462d@.discussions.microsoft.com... Can someone tell me the difference between the Flat File Source Output - External Columns and Output Columns ? I always end up changing the datatype properities in both to make things work :-)|||

By definition, output columns represent the column schema of the component itself. External columns represent the column schema of the database that the component is connected to.

Try editing the column defs in the connection manager and see if that eases the experience.

Difference between Backup/Restore & Attach/Detach

Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>

Difference between Backup/Restore & Attach/Detach

Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
>> recovery. Databases can be backed up while online. The backup file size
>> is usually smaller than the database files since only used pages are
>> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can
>> reduce potential data loss by performing transaction log backups.
>> Detaching a database removes the database from SQL Server while leaving
>> the physical database files intact. This allows you to rename or move
>> the physical files and then re-attach. Although one could perform cold
>> backups using this technique, detach/attach isn't really intended to be
>> used as a backup/recovery process.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "KP" <kp@.msn.com> wrote in message
>> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>>
>

Difference between Backup/Restore & Attach/Detach

Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.
Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>
|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
sql

Difference between backing up to a file and a logical backup device?

What's is the between backing up a database to a file and a logical backup device?

If I point the logical backup device to a file on the filesystem, it's same as backing up to a file? isn't?

Thanks

Yep, that is right, the backup device just allows you to change the underlying target for the backup without changing the backup jobs themselves.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Difference between .rdl and .rdlc files

What is the difference between reports created using Business Intelligence Projects Template and the reports created using .rdlc file which is present in the various file types displayed. Why SSRS needs to be installed when you can create reports using .rdlc file.

The way I understand it, .rdl is a server controlled report and .rdlc is a client-side controlled report. You have to deploy an .rdl file to a report server and access it using that server. With a .rdlc report, you can create the report and use a ReportViewer control to run the report in your application.

|||

RDL files are created by the SQL Server 2005 version of Report Designer. RDLC files are created by the Visual Studio 2005 version of Report Designer.

RDL and RDLC formats have the same XML schema. However, in RDLC files, some values (such as query text) are allowed to be empty, which means that they are not immediately ready to be published to a Report Server. The missing values can be entered by opening the RDLC file using the SQL Server 2005 version of Report Designer. (You have to rename .rdlc to .rdl first.)

RDL files are fully compatible with the ReportViewer control runtime. However, RDL files do not contain some information that the design-time of the ReportViewer control depends on for automatically generating data-binding code. Bymanually binding data, RDL files can be used in the ReportViewer control.

Note that the ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all data sources, including non-database data sources. However this means that when an RDL file is used by the ReportViewer control, the SQL related information in the RDL file is simply ignored by the control. It is the host application's responsibility to connect to databases, execute queries and supply data to the ReportViewer control in the form of ADO.NET DataTables.

|||

Thanks Tamer Fathy. But I have certain doubts. I can build reports using the .rdlc files provided by VS 2005. The data could be fetched from any database.To display the report, we have got the reportviewer. The reports as far as I understand will be hosted on the application server not on Report Server as in case of
SSRS 2005. So what are the additional advantages of SSRS 2005 and what are the components that I should install to receive the benefits? I have got sql server 2000 as my backend.

Thanks in advance

|||

you don't need reporting service as you use rdlc read this areticle and see the samples

http://www.gotreportviewer.com/

|||

Thanks Tamer Fathy. But I did not understand the following abstract from the article you asked me to go through.

Q: Why doesn't the ReportViewer control support parameter prompting in local mode?

A: The ReportViewer control does not prompt for parameters when in local mode. It prompts for parameters when it is connected to a Report Server.

In local mode it does not make sense for ReportViewer to prompt for parameters. The rationale is as follows: The most common use of report parameters is to pass to queries as values of query parameters. But unlike the Report Server, the ReportViewer control does not execute queries itself. Rather, queries are executed by the host application, and the result is passed to the ReportViewer control. So the ReportViewer control does not have the opportunity to set query parameters. Applications should take advantage of the parameterization features of Visual Studio data wizards instead.

|||

You welcome any time :),

concerning your question it was easy, this is the difference between the RDL and RDLC in case you need to pass parameters to your reports if you use RDLC which in client side processing as get the data from the database and assign it to the report you need to handle the passing of the parameters by developing the form which get the parameters from the user and then use it in your query then when you get the results you pass it to the report to display.

but if you use server side processing you can use the report viewer control (custom control used to view reports in your application (web or windows)) to handle the parameti hope i explain it in good way and i help you

|||

Thanks Tamer Fathy. But I have one more doubt. Making use of .rdlc file type and the reportviewer provided by VS2005 instead of SSRS 2005 would mean that the reports will be on the application server rather than being hosted on the reportserver since I won't be having a seprate report server. Will this affect the performance of my application server?

Thanks in advance.

|||

no this will not affect your application performance as it wil be as you query the data base for specific data

DeviceInfo in .config file (SQL RS 2000)

Hi,

I am trying to change the default behavior of .CSV Renderer, by specifying NoHeader=false in the RSReportServer.config file. The exact info specified is here:

<Configuration>
<DeviceInfo>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>

Unfortunately even after restarting the reporting services, the changes don't apply and i still get 'Header' as part of the .CSV export.

Would appreciate if anyone could guide me as what is wrong here..

thanks

sreeram.

Ram,

This feature (default DeviceInfo settings in config file) was implemented in RS2005. It is not in RS2000.

Device activation error problem, Index file missing

Hi, everybody.
I have a big problem. One of the disks in RAID 5 array went dead, and
our costumer's administrator tried to fix the problem, which he did, in
the end, but thet ment that all the data on that partition was lost. A
great way to fix the problem, don't you think? Unfortunately, on that
partition there was Index_data.ndf, file that I used for all the
indexes in my database, all the data is in the other file, on the other
partition. There's no backup bacause the database size is about 500GB:(
Now, database is in suspect mode, of course, and I'm thinking about my
options. I know there's a way to put database back from suspect mode
and transport the data using DTS or something to another db, but I'd
really love to skip the part where I get to transport manually 300 GB
of data from one db to another.
What I want to know is this: is there a way to delete indexes from
tables in database, or to delete the index file from database
properties so I could start database again, recreate index file and
recreate indexes?
Any halp is appreciated...
Regards,
MarkoOf course database size does not deteremine backups. Have them buy idera or
redgate. I backed up a 600GB DB to 20GB file with both, no fuss no muss.
On to your problem. Is this 2000? You can reset the suspect flag with
sp_resetstatus. Barring any other options, I would use that proc and then
try to get a backup even if you have to download red-gates eval tool. If you
have a good backup, attempt to drop all indexes located on the now missing
partition by generating a script or turning on ad-hoc updates to system
tables(2k only) and attempting to drop them from there(sysindexes). if you
can't get a reliable backup(ensure you can restore it before doing anything
permanent) once you have the db out of suspect mode, you might have to force
table scans wth queries to get data out as the missing indexes could play
hell with the optimizer and any tool you choose. Red-gate's suite may also
help here, saving you some legwork.
Lastly, slap the admin.
"Marko.Sunjic@.gmail.com" wrote:
> Hi, everybody.
> I have a big problem. One of the disks in RAID 5 array went dead, and
> our costumer's administrator tried to fix the problem, which he did, in
> the end, but thet ment that all the data on that partition was lost. A
> great way to fix the problem, don't you think? Unfortunately, on that
> partition there was Index_data.ndf, file that I used for all the
> indexes in my database, all the data is in the other file, on the other
> partition. There's no backup bacause the database size is about 500GB:(
> Now, database is in suspect mode, of course, and I'm thinking about my
> options. I know there's a way to put database back from suspect mode
> and transport the data using DTS or something to another db, but I'd
> really love to skip the part where I get to transport manually 300 GB
> of data from one db to another.
> What I want to know is this: is there a way to delete indexes from
> tables in database, or to delete the index file from database
> properties so I could start database again, recreate index file and
> recreate indexes?
> Any halp is appreciated...
> Regards,
> Marko
>

Device activation error

Hi All,

Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:\data\.When I detach from the database, place a copy of the two files noted above on my networkdrive @. u:\data and try to attach I get the following error’s:

SQL[1] exec error = -1: Changed database context to 'master'.

“Device activation error. The physical file name u:\data\MyData.MDF may be incorrect.”

I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:\).So far it seems to only be an issue if I move it to a mapped network drive.If anyone could please provide me with any info it would be greatly appreciated.

Thanks.

James D.

First, your data must be stored on an approved network server/storage you want to be supported. Second, you will have to use a special trace flag to force sqlserver to mount a networked db file. See the following article:
http://support.microsoft.com/kb/304261|||

It's because your U: drive isn't a local drive. By default, it's not support to place data files on mapped shares or UNC paths. You can find more information in the following article:

Description of support for network database files in SQL Server

http://support.microsoft.com/?id=304261

-Sue

|||

Excellent, thank you for the response.

After I use the trace flag mentioned and get my MS SQL Server Desktop Engine to mount to the networked db files, will it be possible to have a second PC with MS SQL Server Desktop Engine also installed on it to connect to the same networked DB files at the same time as the first unit, or will it be limited to just one PC at a time due to file locks? We require that at least two units(possibly up to 3 more in the future) can all share, write, and read to same networked db files at the same time. If you could please let me know when you have a moment it would be great.

Thanks.

James D.

|||

Sqlserver is a shared nothing technology. I.e. only one instance can access the data at any time. So, no, you cannot have another server hits that network database file(s). As it stands, unless your hardware is part of the qualified HCL, you're running in an unsupported platform.

Btw, sqlserver has a feature called Scalable Shared Database (SSD) which allows multiple instances to share the same database file. However, this requires a SAN.

Deteted file still imports

Have created a Job with 2 Steps. Step1 imports data into a table from a text
file on another drive. Step2 deletes the file. All executes fine and the
text file disappears from the other drive. If I execute the Job a second
time, the same data is imported even though the file no longer exists. How
can this be'Never mind. I found the issue. Forgot to modify DTS pathway!!!
"Ray" wrote:

> Have created a Job with 2 Steps. Step1 imports data into a table from a te
xt
> file on another drive. Step2 deletes the file. All executes fine and the
> text file disappears from the other drive. If I execute the Job a second
> time, the same data is imported even though the file no longer exists. How
> can this be'