How do I backup a data file & transaction log from one instance of SQL to a different sql server. I have done a complete backup and now I want to restore the database but on a different SQL server, but the *.mdf and *.ldf is missing, so how do I restore them also?When you did the full backup you created a backup file. When you restore
this the mdf and ldf will be created.
On the instance you want to restore to restore from the device. On the
second tab will be the location of the mdf and ldf - this will be the
file names from the other instance so you will have to overtype with
another filename or path.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you restore to another SQL server you also have to think about databaseusers. The users from the database you restore stil are in the DB when it's on the new server but you not see them. You can either run the drop user or use sp_change_users_login 'Auto_Fix', 'username', NULL. This is call ophran users
/Joel|||Thanks a million. I completed the backup and did the restore on the other instance of SQL server and it worked like a charm. You're a life saver!
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Thursday, March 29, 2012
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> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
> 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
>|||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>
> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>> 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
>>
>|||> 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...
>> 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
>|||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/columnists/bkelley/implementing_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...
>> 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
>>
>
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> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
> 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
>|||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>
> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>> 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
>>
>|||> 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...
>> 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
>|||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/columnists/bkelley/implementing_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...
>> 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
>>
>
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,
Ivan
Once 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.gb l...
> 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...
>
|||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.gb l...
>
|||> 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@.TK2MSFTNGP06.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/colu...enting_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...
>
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
Once 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.gb l...
> 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...
>
|||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.gb l...
>
|||> 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@.TK2MSFTNGP06.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/colu...enting_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 & 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...
>
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 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
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 space used calculations
Hi,
Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.
Thanks for any help.
VinnieYou can do this
select cast(size * 8 as int) as Size from dbo.sysfiles
The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)
HTH|||Originally posted by rhigdon
You can do this
select cast(size * 8 as int) as Size from dbo.sysfiles
The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)
HTH
Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.
Thanks in advance.|||For log file you can do:
dbcc sqlperf('logspace')
I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.
HTH|||Originally posted by rhigdon
For log file you can do:
dbcc sqlperf('logspace')
I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.
HTH
Hi,
That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.
Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.
Run SP_HELPFILE to get the information for physical files associated to that database.
Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.
Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.
Thanks for any help.
VinnieYou can do this
select cast(size * 8 as int) as Size from dbo.sysfiles
The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)
HTH|||Originally posted by rhigdon
You can do this
select cast(size * 8 as int) as Size from dbo.sysfiles
The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)
HTH
Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.
Thanks in advance.|||For log file you can do:
dbcc sqlperf('logspace')
I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.
HTH|||Originally posted by rhigdon
For log file you can do:
dbcc sqlperf('logspace')
I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.
HTH
Hi,
That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.
Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.
Run SP_HELPFILE to get the information for physical files associated to that database.
Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.
Data file size used space
In the SQL EM, we are able to get the data file size with
used space and free space details. Similarly for the log
file also.
We have the dbcc sqlperf command to find out the log file
used space details. Is there any way to find out the
data file size? sp_helpfile just gives the total size of
the data file wherein sp_spaceused is also not
distinguishing between used and free space.
Could anyone help me out in this?Do a google search on DBCC SHOWFILESTATS. This is what EM is using. Not documented, so all usual
warnings apply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:08fc01c3ad95$4cf28380$a301280a@.phx.gbl...
> In the SQL EM, we are able to get the data file size with
> used space and free space details. Similarly for the log
> file also.
> We have the dbcc sqlperf command to find out the log file
> used space details. Is there any way to find out the
> data file size? sp_helpfile just gives the total size of
> the data file wherein sp_spaceused is also not
> distinguishing between used and free space.
> Could anyone help me out in this?
used space and free space details. Similarly for the log
file also.
We have the dbcc sqlperf command to find out the log file
used space details. Is there any way to find out the
data file size? sp_helpfile just gives the total size of
the data file wherein sp_spaceused is also not
distinguishing between used and free space.
Could anyone help me out in this?Do a google search on DBCC SHOWFILESTATS. This is what EM is using. Not documented, so all usual
warnings apply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:08fc01c3ad95$4cf28380$a301280a@.phx.gbl...
> In the SQL EM, we are able to get the data file size with
> used space and free space details. Similarly for the log
> file also.
> We have the dbcc sqlperf command to find out the log file
> used space details. Is there any way to find out the
> data file size? sp_helpfile just gives the total size of
> the data file wherein sp_spaceused is also not
> distinguishing between used and free space.
> Could anyone help me out in this?
data file size problem
how can i change the initial size of the data and log file size ?
in my database properties it shows that my data file size is 81 mb and log file size is 985 mb! but my database only contains some tables and stored procedures with few rows of data in each table
and i checked that the actual mdf and ldf files are really that big... i tried to change it but it didn't work...
can someone please teach me how to change it thanks!
Backup the DB with Truncate log. check books online for more info.
|||http://www.codeproject.com/useritems/truncate_log_SQL_server.asp
Tuesday, March 27, 2012
Data File in a Different machine on the network.
Hi All,
I have a requirement to create the database with data and log files in
a different machine on the network.
Googling, I got a link which said Mapped drives do not work but UNC paths
work. However in my case, both are failing.
Any advices or links on net will be highly useful.
Thanks and Regards,
Chandra MohanDon't do this. It's not supported by Microsoft, you will lose all the
benefits of a client-server database and you risk corrupting your data.
Read this article:
http://www.mssqlserver.com/faq/gene...tworkdrives.asp
--
David Portas
----
Please reply only to the newsgroup
--|||bschandramohan@.yahoo.com (Chandra Mohan) wrote in message news:<bb0ef6.0309282105.59f0d698@.posting.google.com>...
> Hi All,
> I have a requirement to create the database with data and log files in
> a different machine on the network.
> Googling, I got a link which said Mapped drives do not work but UNC paths
> work. However in my case, both are failing.
> Any advices or links on net will be highly useful.
> Thanks and Regards,
> Chandra Mohan
Check this KB article:
http://support.microsoft.com/defaul...1&Product=sql2k
You can put database files on network drives by setting trace flag
1807, but it is not supported unless the files are on a NAS which is
specifically certified for SQL Server. Often, the NAS will make the
network drive appear like a local one, so you won't need to set the
trace flag on anyway.
Putting a database on a normal UNC share is a very bad idea - you
won't be able to guarantee data integrity and performance will suffer.
Simon|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Don't do this. It's not supported by Microsoft, you will lose all the
> benefits of a client-server database and you risk corrupting your data.
I agree that putting a database on a network drive is an extremely bad
idea, but I don't see where the client-server issue comes in. You can
still connect a lots of clients to that database, as if the database
had been a drive local to the database server. (Well, lots and lots.
If there are lots, corruption might appear within the hour.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What I had in mind was that you lose the performance advantage (compared to
a desktop database running on a network) of server-based processing. And you
sacrifice much of the resilience of transaction management and logging.
--
David Portas
----
Please reply only to the newsgroup
--
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9405EBB63563DYazorman@.127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> > Don't do this. It's not supported by Microsoft, you will lose all the
> > benefits of a client-server database and you risk corrupting your data.
> I agree that putting a database on a network drive is an extremely bad
> idea, but I don't see where the client-server issue comes in. You can
> still connect a lots of clients to that database, as if the database
> had been a drive local to the database server. (Well, lots and lots.
> If there are lots, corruption might appear within the hour.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> What I had in mind was that you lose the performance advantage (compared
> to a desktop database running on a network) of server-based processing.
> And you sacrifice much of the resilience of transaction management and
> logging.
You mean that rather running client on machine A and server on machine B,
we're running both server and client on A, and only have the database files
on B.
Yes, this is killing the client-server concept. (Ever heard of Visual
SourceSafe? That's an example of this architecture.)
I don't know why people want to put databases on network devices, but
my assumption is that the most common reason is simply space constraint
on the server, so they try to rent space somewhere else in the network.
In this case, I guess clients still connect from somewhere else.
But rather than renting the space, it's better to rent the entire disk
and move into cabinet. Or see your local hardware dealer...
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Data File and Log File
In our current scanario log and data file on the same drive, I want to move
the log file into another drive for optimization and database is in use. how
can I
Current Scanario :-
MyDB1_DATA 1C:\MSSQL\data\MyDB1.mdf
MyDB1_TLOG 2C:\MSSQL\data\MyDB1.ldf
Desiring Scanario
MyDB1_DATA 1C:\MSSQL\data\MyDB1.mdf
MyDB1_TLOG 2D:\MSSQL\data\MyDB1.ldf
>> I want to move the log file into another drive for optimization and[vbcol=seagreen]
You cannot do this while the database is in use. One easy alternative is to
detach the database and move the log file to the new drive. When you attach
the database to the server, specify the new location of the logfile.
Anith
the log file into another drive for optimization and database is in use. how
can I
Current Scanario :-
MyDB1_DATA 1C:\MSSQL\data\MyDB1.mdf
MyDB1_TLOG 2C:\MSSQL\data\MyDB1.ldf
Desiring Scanario
MyDB1_DATA 1C:\MSSQL\data\MyDB1.mdf
MyDB1_TLOG 2D:\MSSQL\data\MyDB1.ldf
>> I want to move the log file into another drive for optimization and[vbcol=seagreen]
You cannot do this while the database is in use. One easy alternative is to
detach the database and move the log file to the new drive. When you attach
the database to the server, specify the new location of the logfile.
Anith
Wednesday, March 7, 2012
data base size, help!
The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||LU
http://support.microsoft.com/default...650-- how
to shrink tr log
http://support.microsoft.com/default...72318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||LU
http://support.microsoft.com/default...650-- how
to shrink tr log
http://support.microsoft.com/default...72318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?
data base size, help!
The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q256650-- how
to shrink tr log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q256650-- how
to shrink tr log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?
data base size, help!
The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/defaul...6650-- h
ow
to shrink tr log
http://support.microsoft.com/defaul...272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/defaul...6650-- h
ow
to shrink tr log
http://support.microsoft.com/defaul...272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?
Data Base Shrink
I have SQL Server 2000 on WIN2k Advanced Server And I have
a Database Consists of:-
1-Single Database File .
2-single transaction log file(initial size 2 MB).
I have Observed that the transaction log file'Capacity
reaches 23 Giga Byte so I Made a Backup for the whole
database and then I tried to shrink the log file using
enterprise manager shrink database wizard.then I
discovered that the physical file capacity was not
reduced, although enterprise manager gave me a message
that the file has been shrinked.
I tried More And More But No result.
Help will be so much appreciated
Best Regards:-
Ahmed NourGood shrink article can be found :-
http://www.mssqlserver.com/faq/logs-shrinklog.asp
--
HTH
Ryan Waight, MCDBA, MCSE
"Ahmed Nour" <a_m_nour@.hotmail.com> wrote in message
news:0d7201c393e7$706c2590$a401280a@.phx.gbl...
> I have SQL Server 2000 on WIN2k Advanced Server And I have
> a Database Consists of:-
> 1-Single Database File .
> 2-single transaction log file(initial size 2 MB).
> I have Observed that the transaction log file'Capacity
> reaches 23 Giga Byte so I Made a Backup for the whole
> database and then I tried to shrink the log file using
> enterprise manager shrink database wizard.then I
> discovered that the physical file capacity was not
> reduced, although enterprise manager gave me a message
> that the file has been shrinked.
> I tried More And More But No result.
> Help will be so much appreciated
> Best Regards:-
> Ahmed Nour|||Ahmed ,
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
--
- Vishal
a Database Consists of:-
1-Single Database File .
2-single transaction log file(initial size 2 MB).
I have Observed that the transaction log file'Capacity
reaches 23 Giga Byte so I Made a Backup for the whole
database and then I tried to shrink the log file using
enterprise manager shrink database wizard.then I
discovered that the physical file capacity was not
reduced, although enterprise manager gave me a message
that the file has been shrinked.
I tried More And More But No result.
Help will be so much appreciated
Best Regards:-
Ahmed NourGood shrink article can be found :-
http://www.mssqlserver.com/faq/logs-shrinklog.asp
--
HTH
Ryan Waight, MCDBA, MCSE
"Ahmed Nour" <a_m_nour@.hotmail.com> wrote in message
news:0d7201c393e7$706c2590$a401280a@.phx.gbl...
> I have SQL Server 2000 on WIN2k Advanced Server And I have
> a Database Consists of:-
> 1-Single Database File .
> 2-single transaction log file(initial size 2 MB).
> I have Observed that the transaction log file'Capacity
> reaches 23 Giga Byte so I Made a Backup for the whole
> database and then I tried to shrink the log file using
> enterprise manager shrink database wizard.then I
> discovered that the physical file capacity was not
> reduced, although enterprise manager gave me a message
> that the file has been shrinked.
> I tried More And More But No result.
> Help will be so much appreciated
> Best Regards:-
> Ahmed Nour|||Ahmed ,
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
--
- Vishal
Data base File is suspect
My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
Data base File is suspect
My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
Tanweer
Hi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
..
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
|||Have a look here:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
Tanweer
Hi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
..
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
|||Have a look here:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
Data base File is suspect
My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
Data Available in a Trigger
I am creating a transaction log trigger for a table.
I would like to log the following data
The user login id: SYSTEM_USER
The User's Computer name: ?
The servers time and date: GetDate()
The trigger Action: Update, Delete, or Insert
The unique record ID for the affected table:
One column for the deleted row in xml raw:
One column for the inserted row in xml raw:
Is there a way to get the users computer name?
Consider this:
"select * from inserts for xml raw"
This is nice because i want to store the inserted and/or deleted table information as xml columns. But I would like to handle the transaction log in a way that created one transaction row add per row in the inserted or deleted table.
My end goal is to insert into the transaction log table as follows:
Lets say that my update trigger contains an inserted table with two rows and the deleted table would have the same, two rows.
I would like to insert two rows into my transaction log. with the username, date time, action and one column for the inserted row as xml raw, and one column for the deleted row as xml raw.
Anyone know how to do this?
It would be nice if i could impliment something like this:
'select * as xml raw from inserted' And it ould return as many rows as is in the inserted table, each row having one column wich represents that row in xml format.
'select * from inserted for xml raw' This is not good because it returns one row with one column whose value is an xml representation of the entire inserted table.
Thanx
Jerry Cicierega
The host_name() function will return this information, but it is not always set. Try looking this up in books online.|||Thank you for responding. Yes this works on my system. The computer name part of my issue is solved.
The xml part of my question still stands.
Thank you !
data and tran log location
I am new to SQl server and would apreciate help in
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchI don't follow you. You say that you have one physical drive, so how can any
thing be mirrored?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:1353a01c41289$79c4d9e0$a
501280a@.phx.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>|||"miro" <anonymous@.discussions.microsoft.com> wrote in message
news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>
If you have only one physical disc you have _no_ fault tolerance, regardless
of your file locations.
If you only have one disc and you put your transaction logs on a different
logical drive you will actually slow your performance down, as the heads of
the drive will have further to travel.
The only acceptable minimum configuration if you want any fault tolerance at
all is to have at least 2 physical drives, data on one, log on the other, so
that if you loose the data disk you can restore from your backup and roll
your logs forward to the point of failure. If this is a mission critical
server then you need hardware RAID and as much redundancy as you can afford.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||Hi Tibor,
you are right the number of physical drives is two. They
are mirrored. I am wondering now if two logical drives
would be suficient and appropriate for 1.- OS, SQL
Server and 2 - database files. What is the best practice ?
Would creating additional logical drives be of any
benefit ?
Thanks
>--Original Message--
>I don't follow you. You say that you have one physical
drive, so how can anything be mirrored?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"miro" <anonymous@.discussions.microsoft.com> wrote in
message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
logical
>
>.
>|||Whether you create logical drives or not does not in any way affect performa
nce as all logical drives are on
the same mirrored physical drive.
Assuming that you only have two drives to work with, and you estimate that i
t will give sufficient
performance:
Having the OS on it's own partition can be a good thing so autogrow (if you
use that) of the database files
cannot starve the OS from disk. Apart from that, it doesn't matter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:10b1b01c412a5$55098e90$a
601280a@.phx.gbl...
> Hi Tibor,
> you are right the number of physical drives is two. They
> are mirrored. I am wondering now if two logical drives
> would be suficient and appropriate for 1.- OS, SQL
> Server and 2 - database files. What is the best practice ?
> Would creating additional logical drives be of any
> benefit ?
> Thanks
> drive, so how can anything be mirrored?
> message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> logical
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchI don't follow you. You say that you have one physical drive, so how can any
thing be mirrored?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:1353a01c41289$79c4d9e0$a
501280a@.phx.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>|||"miro" <anonymous@.discussions.microsoft.com> wrote in message
news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>
If you have only one physical disc you have _no_ fault tolerance, regardless
of your file locations.
If you only have one disc and you put your transaction logs on a different
logical drive you will actually slow your performance down, as the heads of
the drive will have further to travel.
The only acceptable minimum configuration if you want any fault tolerance at
all is to have at least 2 physical drives, data on one, log on the other, so
that if you loose the data disk you can restore from your backup and roll
your logs forward to the point of failure. If this is a mission critical
server then you need hardware RAID and as much redundancy as you can afford.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||Hi Tibor,
you are right the number of physical drives is two. They
are mirrored. I am wondering now if two logical drives
would be suficient and appropriate for 1.- OS, SQL
Server and 2 - database files. What is the best practice ?
Would creating additional logical drives be of any
benefit ?
Thanks
>--Original Message--
>I don't follow you. You say that you have one physical
drive, so how can anything be mirrored?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"miro" <anonymous@.discussions.microsoft.com> wrote in
message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
logical
>
>.
>|||Whether you create logical drives or not does not in any way affect performa
nce as all logical drives are on
the same mirrored physical drive.
Assuming that you only have two drives to work with, and you estimate that i
t will give sufficient
performance:
Having the OS on it's own partition can be a good thing so autogrow (if you
use that) of the database files
cannot starve the OS from disk. Apart from that, it doesn't matter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:10b1b01c412a5$55098e90$a
601280a@.phx.gbl...
> Hi Tibor,
> you are right the number of physical drives is two. They
> are mirrored. I am wondering now if two logical drives
> would be suficient and appropriate for 1.- OS, SQL
> Server and 2 - database files. What is the best practice ?
> Would creating additional logical drives be of any
> benefit ?
> Thanks
> drive, so how can anything be mirrored?
> message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> logical
data and tran log location
I am new to SQl server and would apreciate help in
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchIf you have only one logical drive you cannot separate log and data aiming b
oost performance. For this kind of tuning to work, you must have a bunch of
logical drives, with different RAID levels.
Mirror volumes are better for writting on disk than reading compared to RAID
5, so would be good for tran lob, that is writing intensive. But read perfor
mance for RAID5(when all disks are working fine) is better, so, storing rea
d-only data here would be n
ice. RAID 5 overall performance is better then mirrored volume, but it need
s at least 3 physical disks to be built.
So, if you have only 2 disks, separating log, data and index is worthless.
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchIf you have only one logical drive you cannot separate log and data aiming b
oost performance. For this kind of tuning to work, you must have a bunch of
logical drives, with different RAID levels.
Mirror volumes are better for writting on disk than reading compared to RAID
5, so would be good for tran lob, that is writing intensive. But read perfor
mance for RAID5(when all disks are working fine) is better, so, storing rea
d-only data here would be n
ice. RAID 5 overall performance is better then mirrored volume, but it need
s at least 3 physical disks to be built.
So, if you have only 2 disks, separating log, data and index is worthless.
Subscribe to:
Posts (Atom)