Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
IvanOnce you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...[vbcol
=seagreen]
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>[/vbcol]|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||... and even use EFS to encrypt the files, in case someone get their hands
on the machine and
installs another OS on top of the old OS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
> You can secure your database files (and backups) with NTFS permissions so
that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTN
GP06.phx.gbl...
>|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/col...menting_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Data Files

Hi,
I've got one database with a 50GB file. I want to split that file on, for
example 12 files of 4GB each. What is the best aproach to this operation ?
Any ideas ?
Thanks in avance
CCHere's the FileGroup example from BOL, this will enable the creation of the
12 x 4Gb files. If you then re-create all your CLUSTERED indexes specifying
this new filegroup the data will move...
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A
and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default
filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
HTH. Ryan
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC|||Why do you want to split them? if you mean in operation, you can use
filegroups...
MC
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CCsql

Data Files

Hi,
I've got one database with a 50GB file. I want to split that file on, for
example 12 files of 4GB each. What is the best aproach to this operation ?
Any ideas ?
Thanks in avance
CCHere's the FileGroup example from BOL, this will enable the creation of the
12 x 4Gb files. If you then re-create all your CLUSTERED indexes specifying
this new filegroup the data will move...
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A
and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default
filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
--
HTH. Ryan
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC|||Why do you want to split them? if you mean in operation, you can use
filegroups...
MC
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC

Data File will not shrink

I have an issue, and hopefully I can explain this fully enough. We
have a 3 server environment, all running SQL Server 2000 SP4 on
Windows 2000 Server. Server A, Server B, and Server C. Server A
replicates to Server B, Server B then replicates to Server C.
Server B has a database that two data files and two log files on two
separate drives. So drive D has 1 datafile and 1 log file, and drive
E has the same. Drive E is running out of disk space and drive D had
some to spare. In Enterprise Manager, I went to the Shrink Database
menu for that database, and I choosed 'Empty the file (data will
migrate to other files in the file group) option. That finished
successfully. But now I see that only 2 mb is being used, but the
database size is still 22358 mb. I have tried the other shrink
options with no luck.
So I have tried:
-Compress Pages and then truncate free space from the file
-Truncate free space from the other end of file
-Shrink file to "min size"
-I have also tried checking the 'Move pages to beginning of file
before shrinking, then retrying the steps above.
Also, we have tried shrinking the transaction table using a stored
proceedure called sp_forceshrink_log as well as a few other things we
have seen online.
After all this, the file size is still 20+ gb. My question is this: Is
there anything else we can try to free up the close to 20 gigs of
space? Also, this database is in the middle of a replicated chain,
what effect would there be in deleting the empty file in the
properties of the database. Is that even possible with replication
enabled?
Thanks for any help you can provide.
JonIf you want to shrink a log file, you should do it immediately after
you backup the transaction log. If you do it later, you will get a
warning like "Cannot shrink log file 2 (LogFile) because all logical
log files are in use." and the file will not be shrinked.
If you get a warning saying "The log was not truncated because records
at the beginning of the log are pending replication. Ensure the Log
Reader Agent is running or use sp_repldone to mark transactions as
distributed", you should see this thread, for example:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
read/23ca52b9df791e59/
In any case, you should not delete the transaction log file (especially
if the database is in a transactional replication chain).
--
Razvan Socol
SQL Server MVP|||Thanks for your response, but the issue is not with the log file, but
the data file. I emptied the contents of one data file to another
data file on another drive. So I have 2 mb used in the file, but the
file size is 20+ gb on the physical drive. I am unable to shrink the
file size using the options found in the "Shrink Database" window. So
I am looking for alternatives to getting the data file size down.
Jon
On Feb 3, 2:42 am, "Razvan Socol" <rso...@.gmail.com> wrote:
> If you want to shrink a log file, you should do it immediately after
> you backup the transaction log. If you do it later, you will get a
> warning like "Cannot shrink log file 2 (LogFile) because all logical
> log files are in use." and the file will not be shrinked.
> If you get a warning saying "The log was not truncated because records
> at the beginning of the log are pending replication. Ensure the Log
> Reader Agent is running or use sp_repldone to mark transactions as
> distributed", you should see this thread, for example:http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
> read/23ca52b9df791e59/
> In any case, you should not delete the transaction log file (especially
> if the database is in a transactional replication chain).
> --
> Razvan Socol
> SQL Server MVP

Data File Used Space

Using the master.sysperfinfo table in a SQL Server database you can obtain
the "Log file(s) Used sizes (KB)".
I would like to determine the "Database file(s) Used Sizes (KB)".
How can I determine this value?
Thank You,Lookup the usage and details of the system procedure sp_spaceused in SQL
Server Books Online.
Anith

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.com
See my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.droptable.co m...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com
|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.comSee my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQ
droptable.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.codecomments.com ***sql