db_owner to all tables

is there a command that can change a login role to db_owner in all the
tables, or do i have to use
{
USE table_name
EXEC sp_adduser 'login name'
EXEC sp_addrolemember 'db_owner', 'login name'
}
for each of the tables ?

thanksHi

Rather than adding the login to the db_owner role, why not changed the
object ownership using sp_changeobjectowner?

e.g. to get a list of commands

DECLARE @.username sysname
SET @.username = 'ABC'
select 'EXEC sp_changeobjectowner ''' + u.name + '.' + o.name + ''',
''dbo''' from sysobjects o
JOIN sysusers u on o.uid = u.uid
where u.name = @.username
and o.type = 'U'

You could change this to a cursor and run each statement with EXEC. You will
need to watch out of dependencies and also make sure the correct owner
prefix is used wherever it is referenced.

If you want to change the owner or the database use sp_changedbowner.

The USE statement is for databases not tables.

John

<liorhal@.gmail.com> wrote in message
news:1116165893.813043.270140@.g44g2000cwa.googlegr oups.com...
> is there a command that can change a login role to db_owner in all the
> tables, or do i have to use
> {
> USE table_name
> EXEC sp_adduser 'login name'
> EXEC sp_addrolemember 'db_owner', 'login name'
> }
> for each of the tables ?
> thanks

DB Restore

Hi,
I am restoring a DB from osql command line and I am getting following
message at the end of the resotration. I couldn't understand first two
lines. I searched BOL but couldn't find anything.. anyone know what is
the _dat file? and why it was created? I can see that DB was restored
succesfully. Just puzzlling by these two lines...
Processed 1816 pages for database 'mydb_oltp', file 'mydbtemp_dat' on
file 1.
Processed 1 pages for database 'mydb_oltp', file 'mydbtemp_log' on file
1.
RESTORE DATABASE successfully processed 1817 pages in 12.770 seconds
(1.165
MB/sec).
Thank you,
hjHitesh wrote:
> Hi,
> I am restoring a DB from osql command line and I am getting following
> message at the end of the resotration. I couldn't understand first two
> lines. I searched BOL but couldn't find anything.. anyone know what is
> the _dat file? and why it was created? I can see that DB was restored
> succesfully. Just puzzlling by these two lines...
> Processed 1816 pages for database 'mydb_oltp', file 'mydbtemp_dat' on
> file 1.
> Processed 1 pages for database 'mydb_oltp', file 'mydbtemp_log' on file
> 1.
> RESTORE DATABASE successfully processed 1817 pages in 12.770 seconds
> (1.165
> MB/sec).
> Thank you,
> hj
>
First of all, these are NOT error messages, they are simply status
messages. A SQL Server database consists of, at minimum, two physical
files - a data file (.MDF) and a transaction log file (.LDF). Each
physical file is associated with a "logical" internal file within the
database. These messages you see are telling you that the logical files
"mydbtemp_dat" and "mydbtemp_log" were created successfully.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I thought it creates MDF and LDF as the name of the database i.e.
mydb_oltp.mdf and mydb_oltp.ldf. I do not have another instance running
on this server that has logical or physical files name like that.. so I
was kind of puzzled why it created temp names.
Thank you anyway,
hj
Tracy McKibben wrote:
> Hitesh wrote:
> > Hi,
> > I am restoring a DB from osql command line and I am getting following
> > message at the end of the resotration. I couldn't understand first two
> > lines. I searched BOL but couldn't find anything.. anyone know what is
> > the _dat file? and why it was created? I can see that DB was restored
> > succesfully. Just puzzlling by these two lines...
> >
> > Processed 1816 pages for database 'mydb_oltp', file 'mydbtemp_dat' on
> > file 1.
> > Processed 1 pages for database 'mydb_oltp', file 'mydbtemp_log' on file
> > 1.
> > RESTORE DATABASE successfully processed 1817 pages in 12.770 seconds
> > (1.165
> > MB/sec).
> >
> > Thank you,
> > hj
> >
> First of all, these are NOT error messages, they are simply status
> messages. A SQL Server database consists of, at minimum, two physical
> files - a data file (.MDF) and a transaction log file (.LDF). Each
> physical file is associated with a "logical" internal file within the
> database. These messages you see are telling you that the logical files
> "mydbtemp_dat" and "mydbtemp_log" were created successfully.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The database that was backed up has logical file names like
that - nothing in the restore process created temp file
names. The mdf and ldf files are physical files. As Tracy
already pointed out, mydbtemp_dat and mydbtemp_log are the
logical file names. mydb_oltp.mdf and mydb_oltp.ldf are the
physical files. The restore will restore by default with the
same logical and physical file names that the backed up
database used. You can see both the physical file names and
the logical file names by executing the following in query
analyzer or osql:
use mydb_oltp
go
exec sp_helpfile
In the result set, name is the logical file name and
filename is the physical file name.
-Sue
On 8 Aug 2006 06:06:23 -0700, "Hitesh" <hitesh287@.gmail.com>
wrote:
>I thought it creates MDF and LDF as the name of the database i.e.
>mydb_oltp.mdf and mydb_oltp.ldf. I do not have another instance running
>on this server that has logical or physical files name like that.. so I
>was kind of puzzled why it created temp names.
>Thank you anyway,
>hj
>
>Tracy McKibben wrote:
>> Hitesh wrote:
>> > Hi,
>> > I am restoring a DB from osql command line and I am getting following
>> > message at the end of the resotration. I couldn't understand first two
>> > lines. I searched BOL but couldn't find anything.. anyone know what is
>> > the _dat file? and why it was created? I can see that DB was restored
>> > succesfully. Just puzzlling by these two lines...
>> >
>> > Processed 1816 pages for database 'mydb_oltp', file 'mydbtemp_dat' on
>> > file 1.
>> > Processed 1 pages for database 'mydb_oltp', file 'mydbtemp_log' on file
>> > 1.
>> > RESTORE DATABASE successfully processed 1817 pages in 12.770 seconds
>> > (1.165
>> > MB/sec).
>> >
>> > Thank you,
>> > hj
>> >
>> First of all, these are NOT error messages, they are simply status
>> messages. A SQL Server database consists of, at minimum, two physical
>> files - a data file (.MDF) and a transaction log file (.LDF). Each
>> physical file is associated with a "logical" internal file within the
>> database. These messages you see are telling you that the logical files
>> "mydbtemp_dat" and "mydbtemp_log" were created successfully.
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com

DB Restore

Hi,
I am restoring a DB from osql command line and I am getting following
message at the end of the resotration. I couldn't understand first two
lines. I searched BOL but couldn't find anything.. anyone know what is
the _dat file? and why it was created? I can see that DB was restored
succesfully. Just puzzlling by these two lines...
Processed 1816 pages for database 'mydb_oltp', file 'mydbtemp_dat' on
file 1.
Processed 1 pages for database 'mydb_oltp', file 'mydbtemp_log' on file
1.
RESTORE DATABASE successfully processed 1817 pages in 12.770 seconds
(1.165
MB/sec).
Thank you,
hjHitesh wrote:
> Hi,
> I am restoring a DB from osql command line and I am getting following
> message at the end of the resotration. I couldn't understand first two
> lines. I searched BOL but couldn't find anything.. anyone know what is
> the _dat file? and why it was created? I can see that DB was restored
> succesfully. Just puzzlling by these two lines...
> Processed 1816 pages for database 'mydb_oltp', file 'mydbtemp_dat' on
> file 1.
> Processed 1 pages for database 'mydb_oltp', file 'mydbtemp_log' on file
> 1.
> RESTORE DATABASE successfully processed 1817 pages in 12.770 seconds
> (1.165
> MB/sec).
> Thank you,
> hj
>
First of all, these are NOT error messages, they are simply status
messages. A SQL Server database consists of, at minimum, two physical
files - a data file (.MDF) and a transaction log file (.LDF). Each
physical file is associated with a "logical" internal file within the
database. These messages you see are telling you that the logical files
"mydbtemp_dat" and "mydbtemp_log" were created successfully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I thought it creates MDF and LDF as the name of the database i.e.
mydb_oltp.mdf and mydb_oltp.ldf. I do not have another instance running
on this server that has logical or physical files name like that.. so I
was kind of puzzled why it created temp names.
Thank you anyway,
hj
Tracy McKibben wrote:
> Hitesh wrote:
> First of all, these are NOT error messages, they are simply status
> messages. A SQL Server database consists of, at minimum, two physical
> files - a data file (.MDF) and a transaction log file (.LDF). Each
> physical file is associated with a "logical" internal file within the
> database. These messages you see are telling you that the logical files
> "mydbtemp_dat" and "mydbtemp_log" were created successfully.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The database that was backed up has logical file names like
that - nothing in the restore process created temp file
names. The mdf and ldf files are physical files. As Tracy
already pointed out, mydbtemp_dat and mydbtemp_log are the
logical file names. mydb_oltp.mdf and mydb_oltp.ldf are the
physical files. The restore will restore by default with the
same logical and physical file names that the backed up
database used. You can see both the physical file names and
the logical file names by executing the following in query
analyzer or osql:
use mydb_oltp
go
exec sp_helpfile
In the result set, name is the logical file name and
filename is the physical file name.
-Sue
On 8 Aug 2006 06:06:23 -0700, "Hitesh" <hitesh287@.gmail.com>
wrote:
[vbcol=seagreen]
>I thought it creates MDF and LDF as the name of the database i.e.
>mydb_oltp.mdf and mydb_oltp.ldf. I do not have another instance running
>on this server that has logical or physical files name like that.. so I
>was kind of puzzled why it created temp names.
>Thank you anyway,
>hj
>
>Tracy McKibben wrote:

Db Owner query

Hi!! please kindly help me on this
How can set to my query that the particular is db_owner of that database ?
aside from using the command sp_helplogins.. thanksHi,
use the below script,
declare @.owner varchar(30)
select @.owner=a.name from sysdatabases b,syslogins a
where a.sid=b.sid
and b.name='master'
select @.owner
Thanks
Hari
MCDBA
"Mark Vergara" <markvergara007@.hotmail.com> wrote in message
news:OQ55Oi#DEHA.548@.TK2MSFTNGP10.phx.gbl...
> Hi!! please kindly help me on this
> How can set to my query that the particular is db_owner of that database ?
> aside from using the command sp_helplogins.. thanks
>|||Try:
EXEC sp_helpdb 'MyDatabase'
For all databases:
EXEC sp_helpdb
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Vergara" <markvergara007@.hotmail.com> wrote in message
news:OQ55Oi%23DEHA.548@.TK2MSFTNGP10.phx.gbl...
> Hi!! please kindly help me on this
> How can set to my query that the particular is db_owner of that database ?
> aside from using the command sp_helplogins.. thanks
>

DB name not in sysfiles

This command:
dbcc shrinkfile(ABC2_Log,10)
yields:
"Could not locate file 'ABC2_Log' in sysfiles."
Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
shows the name of the DB as "ABC". I think that might be the problem.
However, this command:
update sysfiles
set name = 'ABC2_Log' where name = 'ABC_Log
yields:
"Ad hoc updates to system catalogs are not enabled"
Not sure if my solution of modifing sysfiles is a correct one or not. Any
help or insight would be appreciated.
Bottom line is i really need to truncate the LOG file. It's almost filled up
the hard drive.
Don
SQL 2000Are you in the right database when you execute the command? Also, what version?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:29968CB2-A525-4507-8C65-3F5D23C49689@.microsoft.com...
> This command:
> dbcc shrinkfile(ABC2_Log,10)
> yields:
> "Could not locate file 'ABC2_Log' in sysfiles."
> Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
> shows the name of the DB as "ABC". I think that might be the problem.
> However, this command:
> update sysfiles
> set name = 'ABC2_Log' where name = 'ABC_Log
> yields:
> "Ad hoc updates to system catalogs are not enabled"
> Not sure if my solution of modifing sysfiles is a correct one or not. Any
> help or insight would be appreciated.
> Bottom line is i really need to truncate the LOG file. It's almost filled up
> the hard drive.
> Don
> SQL 2000
>|||yes, I'm in the right DB. SQL 2000
Don
"Tibor Karaszi" wrote:
> Are you in the right database when you execute the command? Also, what version?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:29968CB2-A525-4507-8C65-3F5D23C49689@.microsoft.com...
> > This command:
> >
> > dbcc shrinkfile(ABC2_Log,10)
> >
> > yields:
> >
> > "Could not locate file 'ABC2_Log' in sysfiles."
> >
> > Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
> > shows the name of the DB as "ABC". I think that might be the problem.
> >
> > However, this command:
> >
> > update sysfiles
> > set name = 'ABC2_Log' where name = 'ABC_Log
> >
> > yields:
> >
> > "Ad hoc updates to system catalogs are not enabled"
> >
> > Not sure if my solution of modifing sysfiles is a correct one or not. Any
> > help or insight would be appreciated.
> >
> > Bottom line is i really need to truncate the LOG file. It's almost filled up
> > the hard drive.
> >
> > Don
> > SQL 2000
> >
> >
>|||Compare the logical names in sysfiles to what you have in master..sysaltfiles.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B2745B8E-813E-4150-8977-9A775EA677EA@.microsoft.com...
> yes, I'm in the right DB. SQL 2000
> Don
>
> "Tibor Karaszi" wrote:
>> Are you in the right database when you execute the command? Also, what version?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:29968CB2-A525-4507-8C65-3F5D23C49689@.microsoft.com...
>> > This command:
>> >
>> > dbcc shrinkfile(ABC2_Log,10)
>> >
>> > yields:
>> >
>> > "Could not locate file 'ABC2_Log' in sysfiles."
>> >
>> > Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
>> > shows the name of the DB as "ABC". I think that might be the problem.
>> >
>> > However, this command:
>> >
>> > update sysfiles
>> > set name = 'ABC2_Log' where name = 'ABC_Log
>> >
>> > yields:
>> >
>> > "Ad hoc updates to system catalogs are not enabled"
>> >
>> > Not sure if my solution of modifing sysfiles is a correct one or not. Any
>> > help or insight would be appreciated.
>> >
>> > Bottom line is i really need to truncate the LOG file. It's almost filled up
>> > the hard drive.
>> >
>> > Don
>> > SQL 2000
>> >
>> >
>>|||master..sysaltfile
ABC_Data,C:\path\ABC_Data.MDF
ABC_Log,C:\path\ABC_Log.LDF
ABC_Data,C:\path\ABC2.mdf
ABC_Log,C:\path\ABC2_log.ldf
ABC..sysfiles
ABC_Data,C:\path\ABC_Data.MDF
ABC_Log ,C:\path\ABC_Log.LDF
ABC2..sysfiles
ABC_Data,C:\path\ABC2.mdf
ABC_Log,C:\path\ABC2_log.ldf
"Tibor Karaszi" wrote:
> Compare the logical names in sysfiles to what you have in master..sysaltfiles.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B2745B8E-813E-4150-8977-9A775EA677EA@.microsoft.com...
> > yes, I'm in the right DB. SQL 2000
> >
> > Don
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you in the right database when you execute the command? Also, what version?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:29968CB2-A525-4507-8C65-3F5D23C49689@.microsoft.com...
> >> > This command:
> >> >
> >> > dbcc shrinkfile(ABC2_Log,10)
> >> >
> >> > yields:
> >> >
> >> > "Could not locate file 'ABC2_Log' in sysfiles."
> >> >
> >> > Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
> >> > shows the name of the DB as "ABC". I think that might be the problem.
> >> >
> >> > However, this command:
> >> >
> >> > update sysfiles
> >> > set name = 'ABC2_Log' where name = 'ABC_Log
> >> >
> >> > yields:
> >> >
> >> > "Ad hoc updates to system catalogs are not enabled"
> >> >
> >> > Not sure if my solution of modifing sysfiles is a correct one or not. Any
> >> > help or insight would be appreciated.
> >> >
> >> > Bottom line is i really need to truncate the LOG file. It's almost filled up
> >> > the hard drive.
> >> >
> >> > Don
> >> > SQL 2000
> >> >
> >> >
> >>
> >>
>|||I'd use ALTER DATABASE to set a desired logical filenames, avoid duplication between databases (just
in case this is what confuses SQL Server) then try again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:9310BC2F-3B76-40BA-99B9-E8B6FA5751ED@.microsoft.com...
> master..sysaltfile
> ABC_Data,C:\path\ABC_Data.MDF
> ABC_Log,C:\path\ABC_Log.LDF
> ABC_Data,C:\path\ABC2.mdf
> ABC_Log,C:\path\ABC2_log.ldf
> ABC..sysfiles
> ABC_Data,C:\path\ABC_Data.MDF
>
> ABC_Log ,C:\path\ABC_Log.LDF
>
> ABC2..sysfiles
> ABC_Data,C:\path\ABC2.mdf
>
> ABC_Log,C:\path\ABC2_log.ldf
>
>
> "Tibor Karaszi" wrote:
>> Compare the logical names in sysfiles to what you have in master..sysaltfiles.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B2745B8E-813E-4150-8977-9A775EA677EA@.microsoft.com...
>> > yes, I'm in the right DB. SQL 2000
>> >
>> > Don
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Are you in the right database when you execute the command? Also, what version?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:29968CB2-A525-4507-8C65-3F5D23C49689@.microsoft.com...
>> >> > This command:
>> >> >
>> >> > dbcc shrinkfile(ABC2_Log,10)
>> >> >
>> >> > yields:
>> >> >
>> >> > "Could not locate file 'ABC2_Log' in sysfiles."
>> >> >
>> >> > Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
>> >> > shows the name of the DB as "ABC". I think that might be the problem.
>> >> >
>> >> > However, this command:
>> >> >
>> >> > update sysfiles
>> >> > set name = 'ABC2_Log' where name = 'ABC_Log
>> >> >
>> >> > yields:
>> >> >
>> >> > "Ad hoc updates to system catalogs are not enabled"
>> >> >
>> >> > Not sure if my solution of modifing sysfiles is a correct one or not. Any
>> >> > help or insight would be appreciated.
>> >> >
>> >> > Bottom line is i really need to truncate the LOG file. It's almost filled up
>> >> > the hard drive.
>> >> >
>> >> > Don
>> >> > SQL 2000
>> >> >
>> >> >
>> >>
>> >>
>>|||Worked like a million!!
THANKS
Don
"Tibor Karaszi" wrote:
> I'd use ALTER DATABASE to set a desired logical filenames, avoid duplication between databases (just
> in case this is what confuses SQL Server) then try again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:9310BC2F-3B76-40BA-99B9-E8B6FA5751ED@.microsoft.com...
> > master..sysaltfile
> > ABC_Data,C:\path\ABC_Data.MDF
> > ABC_Log,C:\path\ABC_Log.LDF
> > ABC_Data,C:\path\ABC2.mdf
> > ABC_Log,C:\path\ABC2_log.ldf
> >
> > ABC..sysfiles
> > ABC_Data,C:\path\ABC_Data.MDF
> >
> >
> > ABC_Log ,C:\path\ABC_Log.LDF
> >
> >
> >
> > ABC2..sysfiles
> > ABC_Data,C:\path\ABC2.mdf
> >
> >
> > ABC_Log,C:\path\ABC2_log.ldf
> >
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Compare the logical names in sysfiles to what you have in master..sysaltfiles.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B2745B8E-813E-4150-8977-9A775EA677EA@.microsoft.com...
> >> > yes, I'm in the right DB. SQL 2000
> >> >
> >> > Don
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Are you in the right database when you execute the command? Also, what version?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> >> news:29968CB2-A525-4507-8C65-3F5D23C49689@.microsoft.com...
> >> >> > This command:
> >> >> >
> >> >> > dbcc shrinkfile(ABC2_Log,10)
> >> >> >
> >> >> > yields:
> >> >> >
> >> >> > "Could not locate file 'ABC2_Log' in sysfiles."
> >> >> >
> >> >> > Initially, I had backed up ABC and restored it as ABC2. Sysfiles on DB ABC2
> >> >> > shows the name of the DB as "ABC". I think that might be the problem.
> >> >> >
> >> >> > However, this command:
> >> >> >
> >> >> > update sysfiles
> >> >> > set name = 'ABC2_Log' where name = 'ABC_Log
> >> >> >
> >> >> > yields:
> >> >> >
> >> >> > "Ad hoc updates to system catalogs are not enabled"
> >> >> >
> >> >> > Not sure if my solution of modifing sysfiles is a correct one or not. Any
> >> >> > help or insight would be appreciated.
> >> >> >
> >> >> > Bottom line is i really need to truncate the LOG file. It's almost filled up
> >> >> > the hard drive.
> >> >> >
> >> >> > Don
> >> >> > SQL 2000
> >> >> >
> >> >> >
> >> >>
> >> >>
> >>
> >>
>

DB Move Question

I a familiar with the command to move a database in this manner:
---
use master
go
sp_detach_db 'test'
go
"move files to new location"
use master
go
sp_attach_db 'test','D:\MSSQL\Data\test_data.mdf','D:\MSSQL\DATA\test_log.ldf'
go
---
However I have a situation where the database is mae up of several data
files and filegroups. For a long time a stored procedure was creating new
data files on the C drive of this server. I finally modified the SP to store
them on the desired data drive, but I have several *.ndf files still on the C
drive. So I am unsure how to get all the files in the same location on the
dedicated data drive.
Since the *.mdf files are already in the proper location and it is only the
DB_LOB filegroup that has files stored on the C drive, how exactly are these
moved?You should be able to move it by:
Detaching the database.
Move the files to the desired location.
Then attach the database, and specify the full path and file name for all the database files.
Just make sure you try above on a test db with similar file/filegroup set first, and also have a
proper backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shon Miles" <ShonMiles@.discussions.microsoft.com> wrote in message
news:01B11C65-CC08-443B-A954-2A9A090D7FF5@.microsoft.com...
>I a familiar with the command to move a database in this manner:
> ---
> use master
> go
> sp_detach_db 'test'
> go
> "move files to new location"
> use master
> go
> sp_attach_db 'test','D:\MSSQL\Data\test_data.mdf','D:\MSSQL\DATA\test_log.ldf'
> go
> ---
> However I have a situation where the database is mae up of several data
> files and filegroups. For a long time a stored procedure was creating new
> data files on the C drive of this server. I finally modified the SP to store
> them on the desired data drive, but I have several *.ndf files still on the C
> drive. So I am unsure how to get all the files in the same location on the
> dedicated data drive.
> Since the *.mdf files are already in the proper location and it is only the
> DB_LOB filegroup that has files stored on the C drive, how exactly are these
> moved?|||Thanks I will try that.
"Shon Miles" wrote:
> I a familiar with the command to move a database in this manner:
> ---
> use master
> go
> sp_detach_db 'test'
> go
> "move files to new location"
> use master
> go
> sp_attach_db 'test','D:\MSSQL\Data\test_data.mdf','D:\MSSQL\DATA\test_log.ldf'
> go
> ---
> However I have a situation where the database is mae up of several data
> files and filegroups. For a long time a stored procedure was creating new
> data files on the C drive of this server. I finally modified the SP to store
> them on the desired data drive, but I have several *.ndf files still on the C
> drive. So I am unsure how to get all the files in the same location on the
> dedicated data drive.
> Since the *.mdf files are already in the proper location and it is only the
> DB_LOB filegroup that has files stored on the C drive, how exactly are these
> moved?

DB Maintenance Plan from command line

Hi everyone !
Is it possible to execute a DB Maintenance Plan from command line (like
dtsrun) ?
Thank you !
Hugo
Great !
Thank you !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news: OzlKPcvPHHA.140@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000? If so, you can use OSQL, and from there execute
> sp_start_job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hugo" <hugorobichrg@.news.postalias> wrote in message
> news:u9Z1eFvPHHA.4424@.TK2MSFTNGP06.phx.gbl...
>

DB Maintenance Plan from command line

Hi everyone !
Is it possible to execute a DB Maintenance Plan from command line (like
dtsrun) ?
Thank you !
HugoSQL Server 2000? If so, you can use OSQL, and from there execute sp_start_jo
b.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugo" <hugorobichrg@.news.postalias> wrote in message news:u9Z1eFvPHHA.4424@.TK2MSFTNGP06.phx
.gbl...
> Hi everyone !
> Is it possible to execute a DB Maintenance Plan from command line (like dt
srun) ?
> Thank you !
> Hugo
>|||Great !
Thank you !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news: OzlKPcvPHHA.140@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000? If so, you can use OSQL, and from there execute
> sp_start_job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hugo" <hugorobichrg@.news.postalias> wrote in message
> news:u9Z1eFvPHHA.4424@.TK2MSFTNGP06.phx.gbl...
>

DB Maintenance Plan from command line

Hi everyone !
Is it possible to execute a DB Maintenance Plan from command line (like
dtsrun) ?
Thank you !
HugoSQL Server 2000? If so, you can use OSQL, and from there execute sp_start_job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugo" <hugorobichrg@.news.postalias> wrote in message news:u9Z1eFvPHHA.4424@.TK2MSFTNGP06.phx.gbl...
> Hi everyone !
> Is it possible to execute a DB Maintenance Plan from command line (like dtsrun) ?
> Thank you !
> Hugo
>|||Great !
Thank you !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a écrit
dans le message de news: OzlKPcvPHHA.140@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000? If so, you can use OSQL, and from there execute
> sp_start_job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hugo" <hugorobichrg@.news.postalias> wrote in message
> news:u9Z1eFvPHHA.4424@.TK2MSFTNGP06.phx.gbl...
>> Hi everyone !
>> Is it possible to execute a DB Maintenance Plan from command line (like
>> dtsrun) ?
>> Thank you !
>> Hugo
>