Database size shrunk

I will like to request for understanding with regards to SQL data size.

On Friday, 21 March, I checked from the SQL Server Enterpise Manager that my Data Size was 30 MB.

I then had my database quota size increased.

However, on Monday 24 March, I noted that the data size has shrunk to 24 MB even though my client has added about 7k+ of records to the database since.

Today on 25 March, the database showed 15 MB instead. I checked my records and there was nothing lost in them. The number remains the same or slightly higher. This is because they have almost completed the porting of the data.

I am confused as to the difference in the display of Data Size. It has lead me to a wrong decision earlier to upgrade to 45 MB when in actual fact, the data doesnt even take more than 16 MB.

I have during the time truncated the transaction logs. Could this be the cause of the database size to shrink?Check whether AUTO_SHRINK option is used.|||Originally posted by Satya
Check whether AUTO_SHRINK option is used.

Thanks for replying

I just checked and AUTO_shrink is off

Would there be anything else that could cause it to shrink?sql

Database Size (Free,Used)

In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,
See sp_spaceused in SQL Server Books Online. In master databse, you could
run the following to see what tables this procedure accesses:
sp_helptext sp_spaceused
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:805D5942-D569-4083-99B4-E537F845D880@.microsoft.com...
In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,
|||Hi Joe,
Sysindexes system table in each database stores all the space information. You could also use the system stored procedure
"sp_spaceused " to get the space usage. For transaction log usage use DBCC SQLPERF(LOGSPACE)
Thanks
Hari
SQL Server MVP
____________________________________
Joe K. Wrote:
In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,
Sent via SreeSharp NewsReader http://www.SreeSharp.com
sql

Database Size (Free,Used)

In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,See sp_spaceused in SQL Server Books Online. In master databse, you could
run the following to see what tables this procedure accesses:
sp_helptext sp_spaceused
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:805D5942-D569-4083-99B4-E537F845D880@.microsoft.com...
In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,|||Hi Joe,
Sysindexes system table in each database stores all the space information. Y
ou could also use the system stored procedure
"sp_spaceused " to get the space usage. For transaction log usage use DBCC S
QLPERF(LOGSPACE)
Thanks
Hari
SQL Server MVP
____________________________________
Joe K. Wrote:
In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,
Sent via SreeSharp NewsReader http://www.SreeSharp.com

Database Size (Free,Used)

In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,See sp_spaceused in SQL Server Books Online. In master databse, you could
run the following to see what tables this procedure accesses:
sp_helptext sp_spaceused
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:805D5942-D569-4083-99B4-E537F845D880@.microsoft.com...
In SQL Server Enterprise Manager taskpad will display database size free and
used space.
What tables within SQL Server is this information stored?
Is there another way to obtain database size free and used space?
Thanks,

Database Size

I am attempting to create a new database with a size of 7gig. I have 16 gig
available but Enterprise Manager errors with not enough disk space with any
attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.=20
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...

quote:

---

> I am attempting to create a new database with a size of 7gig. I have =

---

16 gig available but Enterprise Manager errors with not enough disk =
space with any attempt greater than 4gig. any suggestions are very much =
welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...

quote:

---

> I am attempting to create a new database with a size of 7gig. I have 16

---

gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive
is formatted to FAT32 much to my suprise

Database Size

I am attempting to create a new database with a size of 7gig. I have 16 gig available but Enterprise Manager errors with not enough disk space with any attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
> I am attempting to create a new database with a size of 7gig. I have =16 gig available but Enterprise Manager errors with not enough disk =space with any attempt greater than 4gig. any suggestions are very much =welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
> I am attempting to create a new database with a size of 7gig. I have 16
gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive is formatted to FAT32 much to my suprisesql

Database Schema copy

I want to a DB, and create an empty DB with the same schema. This was
preety easy with Enterprise Manager, how can I do it with the new suite?
Using Management Studio, right click on the database name and select "Script
Database as".
Mark
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:BZmdnbmZgJx5Y-DeRVn-tQ@.comcast.com...
>I want to a DB, and create an empty DB with the same schema. This was
> preety easy with Enterprise Manager, how can I do it with the new suite?
>
|||I want the whole schema, that only does the DBCreate?
Dave
"mark sullivan" wrote:

> Using Management Studio, right click on the database name and select "Script
> Database as".
> Mark
>
> "Dave H" <DaveH@.noemail.nospam> wrote in message
> news:BZmdnbmZgJx5Y-DeRVn-tQ@.comcast.com...
>
>
|||Use the Generate Scripts Wizard: Right-click the database, point to Tasks,
and then click Generate Scripts.
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Dave H" <DaveH@.discussions.microsoft.com> wrote in message
news:BBC90ED6-8824-4731-B29A-2B1F3A83B680@.microsoft.com...[vbcol=seagreen]
>I want the whole schema, that only does the DBCreate?
> --
> Dave
>
> "mark sullivan" wrote:

Database restores using Enterprise Manager

Good afternoon. I am using MS SQL 2K and was wondering if it is possible to restore multiple back-up files (database and transaction logs) to a database, if you haven't created a back-up set, using Enterprise Manager. I know that you can write T-SQL to first restore the back-up file and each of the transaction log files, except the last one, with the option of norecovery, and then the last transaction log file, with recovery. Any help would be greatly appreciated. Thank you.

Chris

That the way, restoring the database files one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the reply, Jens.

Database restore help

I need to restore a database over an existing DB (I have made a backup and
it's SQL 2000).
When I do try and restore it via SQL Enterprose Manager 2000 (the only way I
know) it it says "logical file 'database' is not part of a database
'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
RESTORE DATAVASE is terminating adbnormally.Use Query Analyzer and run RESTORE FILELISTONLY for the backup file. Post
those results here. We'll follow up when we get those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:523266A9-C34C-47F9-9C22-FC91A9E798E7@.microsoft.com...
I need to restore a database over an existing DB (I have made a backup and
it's SQL 2000).
When I do try and restore it via SQL Enterprose Manager 2000 (the only way I
know) it it says "logical file 'database' is not part of a database
'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
RESTORE DATAVASE is terminating adbnormally.|||I think you might have to tell it to overwrite your existing database. I
can't remember the actual steps to do it in EM, but somewhere in the
Restore wizard you have the option to "overwrite existing database" or
something like that.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Gonzo wrote:
> I need to restore a database over an existing DB (I have made a backup
> and it's SQL 2000).
> When I do try and restore it via SQL Enterprose Manager 2000 (the only
> way I know) it it says "logical file 'database' is not part of a
> database 'database2'. Use RESTORE FILELISTONLY to list the logocal file
> names. RESTORE DATAVASE is terminating adbnormally.|||Many thanks:
RESTORE FILElISTONLY from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
I get:
Btest_Data C:\Program Files\Microsoft SQL Server\MSSQL\Data\BRITLIVE.mdf
D PRIMARY
Btest_Log C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_log.ldf L NULL
On the server we have these DB's:
Btest
RM_test1
We sent a company the Btest DB to make some changes that they have done and
sent the bak file back. I need to restore this over the RM_test1 DB, but it
seems that it still references the original Btest DB everywhere.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OUAXLKMtHHA.4572@.TK2MSFTNGP02.phx.gbl...
> Use Query Analyzer and run RESTORE FILELISTONLY for the backup file. Post
> those results here. We'll follow up when we get those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:523266A9-C34C-47F9-9C22-FC91A9E798E7@.microsoft.com...
> I need to restore a database over an existing DB (I have made a backup and
> it's SQL 2000).
> When I do try and restore it via SQL Enterprose Manager 2000 (the only way
> I
> know) it it says "logical file 'database' is not part of a database
> 'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
> RESTORE DATAVASE is terminating adbnormally.
>|||Hi there, I tried that, but get the same error. I have just replied to the
other post too with a bit more info
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:eOsdMVMtHHA.4888@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>I think you might have to tell it to overwrite your existing database. I
>can't remember the actual steps to do it in EM, but somewhere in the
>Restore wizard you have the option to "overwrite existing database" or
>something like that.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
> Gonzo wrote:|||From QA, run:
RESTORE DATABASE RM_test1
FROM DISK = 'f:\MSSQL80\BACKUP\RM_test1.bak'
WITH REPLACE
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:9F74A63C-2524-4AE0-AEAC-9E9853D66B30@.microsoft.com...
Many thanks:
RESTORE FILElISTONLY from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
I get:
Btest_Data C:\Program Files\Microsoft SQL Server\MSSQL\Data\BRITLIVE.mdf
D PRIMARY
Btest_Log C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_log.ldf L NULL
On the server we have these DB's:
Btest
RM_test1
We sent a company the Btest DB to make some changes that they have done and
sent the bak file back. I need to restore this over the RM_test1 DB, but it
seems that it still references the original Btest DB everywhere.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OUAXLKMtHHA.4572@.TK2MSFTNGP02.phx.gbl...
> Use Query Analyzer and run RESTORE FILELISTONLY for the backup file. Post
> those results here. We'll follow up when we get those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:523266A9-C34C-47F9-9C22-FC91A9E798E7@.microsoft.com...
> I need to restore a database over an existing DB (I have made a backup and
> it's SQL 2000).
> When I do try and restore it via SQL Enterprose Manager 2000 (the only way
> I
> know) it it says "logical file 'database' is not part of a database
> 'database2'. Use RESTORE FILELISTONLY to list the logocal file names.
> RESTORE DATAVASE is terminating adbnormally.
>|||Use
Restore Database <db_name>
from Disk = 'f:\MSSQL80\BACKUP\RM_test1.bak'
WITH Move 'Btest_Data' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2.mdf',
Move 'Btest_Log' To 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\BRITLIVE_2_log.ldf',
just change the name of the .mdf & .ldf to something that doesn't already
exist.
--
MG
"Gonzo" wrote:

> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL Server\MSSQL\Da
ta
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>|||I have restored it now, how can I now change the logical names to something
else?
many thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
> You have to keep the logical names for the restore. You can change those
> later.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL
> Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>|||Check out ALTER DATABASE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:%23JzdGUNtHHA.768@.TK2MSFTNGP04.phx.gbl...
I have restored it now, how can I now change the logical names to something
else?
many thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O4i5xSNtHHA.3556@.TK2MSFTNGP05.phx.gbl...
> You have to keep the logical names for the restore. You can change those
> later.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:A4969E2E-1F25-484A-BDE3-91247145CD79@.microsoft.com...
> It seems I can restore it using EM (tried on a test server) but only if i
> keep the logical names. in E:\Program Files\Microsoft SQL
> Server\MSSQL\Data
> the databse is RM_test1 but I right click on the database and go to
> properties and then the tabs Data files and transaction log then the file
> name is Btest_data and Btest_logs. Now a database on the live server is
> already called 'Btest' will this cause a problem?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
>|||No. Logical names are local to the DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gonzo" <no@.no123.com> wrote in message
news:28BF2F6F-4256-4299-BE76-1F1DAAB4D61F@.microsoft.com...
I now get:
Processed 9336 pages for database 'RM_test1', file 'Btest_Data' on file 1.
Processed 1 pages for database 'RM_test1', file 'Btest_Log' on file 1.
RESTORE DATABASE successfully processed 9337 pages in 13.278 seconds (5.760
MB/sec).
There is a database caleld Btest already uses Btest for it's database name
and logical name, will this create a problem with both having the same name?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4XJkINtHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Type:
> use master
> go
> ... before running the RESTORE. Also, be sure that no one is connected
> to
> the DB when you restore it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gonzo" <no@.no123.com> wrote in message
> news:863AE2DE-3B3B-4647-8AC6-6BFAE4FFDDEC@.microsoft.com...
> Hi I get:
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23SUif2MtHHA.4796@.TK2MSFTNGP04.phx.gbl...
>

Database Restore

I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this file.
Try:
restore database MyDB
from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this
file.
|||Hi,
Or from Enterprise manager:-
1. Right click above the databases option
2. All Tasks...Restore database.. Give the database name in "Restore as
database:....
3.CLick the select devices command button
4. Click add and select the backup file
5. Click OK
6. In the main restore screen "Click OK
Thanks
hari
SQL Server mvp
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eqZHAc0sFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Try:
> restore database MyDB
> from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
> news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
> I made a very big mistake. In Enterprise Manager I right clicked on one
> of
> the databases and selected Delete. In addition I answered OK to the
> following dialog box. I realize that this was wrong. Now I need to
> restore
> it. Immediately before deleting I right-clicked, selected all tasks and
> Backup Database. I can see the file in the \\program files\Microsoft SQL
> Sever\MSSQL\BACKUP directory. How do I restore the database using this
> file.
>

Database Restore

I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this file
.Try:
restore database MyDB
from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this
file.|||Hi,
Or from Enterprise manager:-
1. Right click above the databases option
2. All Tasks...Restore database.. Give the database name in "Restore as
database:....
3.CLick the select devices command button
4. Click add and select the backup file
5. Click OK
6. In the main restore screen "Click OK
Thanks
hari
SQL Server mvp
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eqZHAc0sFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Try:
> restore database MyDB
> from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
> news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
> I made a very big mistake. In Enterprise Manager I right clicked on one
> of
> the databases and selected Delete. In addition I answered OK to the
> following dialog box. I realize that this was wrong. Now I need to
> restore
> it. Immediately before deleting I right-clicked, selected all tasks and
> Backup Database. I can see the file in the \\program files\Microsoft SQL
> Sever\MSSQL\BACKUP directory. How do I restore the database using this
> file.
>

Database Restore

I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this file.Try:
restore database MyDB
from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this
file.|||Hi,
Or from Enterprise manager:-
1. Right click above the databases option
2. All Tasks...Restore database.. Give the database name in "Restore as
database:....
3.CLick the select devices command button
4. Click add and select the backup file
5. Click OK
6. In the main restore screen "Click OK
Thanks
hari
SQL Server mvp
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eqZHAc0sFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Try:
> restore database MyDB
> from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
> news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
> I made a very big mistake. In Enterprise Manager I right clicked on one
> of
> the databases and selected Delete. In addition I answered OK to the
> following dialog box. I realize that this was wrong. Now I need to
> restore
> it. Immediately before deleting I right-clicked, selected all tasks and
> Backup Database. I can see the file in the \\program files\Microsoft SQL
> Sever\MSSQL\BACKUP directory. How do I restore the database using this
> file.
>

DataBase Restoration Problem (SQL Server 7)

I'm restoring a database daily. However until last two days, I'm getting the following error (this happens in Enterprise Manager and also when I use the Query Analyzer):

Server: Msg 5149, Level 16, State 1, Line 1
MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.
Server: Msg 3140, Level 16, State 1, Line 1
Could not adjust the space allocation for file 'corporate'.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

The server has sufficient amount of space. Where data resides there is 13.5GB free and where the log resides there is 4.75BG free.
The backup file itself is 3.3GB.

When peaking into the backup file, it looks good:
data file being 4.4GB and the log 300MB

What could be the problem? The server is running Windows 2000 Server, RAID Solution and SQL Server 7.0 Service Pack #4I just want to also add, that on another server, where there is less space available, the backup restores fine. This is very strange.|||If you are restoring to the 'PRIMARY' filegroup, or another filegroup with lots of member files, it may be that the filegroup is 'full' (from the point of view of Sql Server).

Unfortunately this may correspond to different situations for which error messages are often not particularly helpful (the issue being due to some combination of the autogrowth algorithm, how auto growth may be constrained at a user level % / fixed increment, actual disk availability, etc., etc.). Such issues are more commonly seen in autogrow situations occurring during maintenence and / or production batching (but reasonably may occur in DB restore situations as well). Generally the error means that the disk(s) where the database(s) is / are located is / are full and /or the files of your database(s) cannot grow any more on their disk(s) (given the fixed increment or % setting constraint); or autogrowth of files may have been disabled. On DB creation (and hence mdf, ldf, sgf, etc. file creation) one either specifies or takes defaults for the initial size for each file. Similarly, one either specifies or takes defaults for how each file will increase via growth increment settings. Each time a file 'fills', it increases its size by the growth increment. When there are multiple files in a filegroup, (as is typically the case with the primary filegroup), filegroups do not autogrow until all the files are 'full'. Autogrowth then generally is initiated in a round robin type fashion. You may wish to manually check and increase the sizes of, and / or change the growth options of files that are members of the filegroup involved, as may be appropriate. If possible, I would be inclined to perform some experimentation on an identical (or very similarly configured) dev server to get a better idea of what the exact issue is in your case (before traumatizing the production server).|||Do you happen to be using fat32 as your file system (as opposed to ntfs) ? If so, fat32 has a 4 gb limit on file sizes.|||rnealejr>>FAT32 was the problem. I've checked and the drive was FAT32. The moment we converted to NTSF, the problem disappeared. Thanks a bunch.

database rename

Hi just wondering how to rename the database using enterprise manager. thanks.
Paul G
Software engineer.
I'd use Query Analyzer:
1. Get everyone out of the database.
2. Look up sp_rename_db
"Paul" wrote:

> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.
|||Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:

> Hi just wondering how to rename the database using enterprise manager. thanks.
Hi, you have to use the QueryAnalyzer with sp_rename.
Greetings
Rouven Hausner
|||ok thanks for the replies.
Paul G
Software engineer.
"Rouven Hausner" wrote:

> Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
>
> Hi, you have to use the QueryAnalyzer with sp_rename.
> Greetings
> Rouven Hausner
>
|||As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.
|||Hi,
You can use either ALTER DATABASE or sp_renamedb to rename a database.
Eg:-
EXEC sp_renamedb 'accounting', 'financial'
or
ALTER DATABASE Accounting MODIFY NAME = financial
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:enZ3lDq4FHA.3352@.TK2MSFTNGP10.phx.gbl...
> As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
>

database rename

Hi just wondering how to rename the database using enterprise manager. thank
s.
--
Paul G
Software engineer.I'd use Query Analyzer:
1. Get everyone out of the database.
2. Look up sp_rename_db
"Paul" wrote:

> Hi just wondering how to rename the database using enterprise manager. tha
nks.
> --
> Paul G
> Software engineer.|||Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
[vbcol=seagreen]
> Hi just wondering how to rename the database using enterprise manager. thanks.[/vb
col]
Hi, you have to use the QueryAnalyzer with sp_rename.
Greetings
Rouven Hausner|||ok thanks for the replies.
--
Paul G
Software engineer.
"Rouven Hausner" wrote:

> Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
>
> Hi, you have to use the QueryAnalyzer with sp_rename.
> Greetings
> Rouven Hausner
>|||As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
> Hi just wondering how to rename the database using enterprise manager. tha
nks.
> --
> Paul G
> Software engineer.|||Hi,
You can use either ALTER DATABASE or sp_renamedb to rename a database.
Eg:-
EXEC sp_renamedb 'accounting', 'financial'
or
ALTER DATABASE Accounting MODIFY NAME = financial
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:enZ3lDq4FHA.3352@.TK2MSFTNGP10.phx.gbl...
> As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
>

database rename

Hi just wondering how to rename the database using enterprise manager. thanks.
--
Paul G
Software engineer.I'd use Query Analyzer:
1. Get everyone out of the database.
2. Look up sp_rename_db
"Paul" wrote:
> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.|||Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
> Hi just wondering how to rename the database using enterprise manager. thanks.
Hi, you have to use the QueryAnalyzer with sp_rename.
Greetings
Rouven Hausner|||ok thanks for the replies.
--
Paul G
Software engineer.
"Rouven Hausner" wrote:
> Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
> > Hi just wondering how to rename the database using enterprise manager. thanks.
> Hi, you have to use the QueryAnalyzer with sp_rename.
> Greetings
> Rouven Hausner
>|||As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.|||Hi,
You can use either ALTER DATABASE or sp_renamedb to rename a database.
Eg:-
EXEC sp_renamedb 'accounting', 'financial'
or
ALTER DATABASE Accounting MODIFY NAME = financial
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:enZ3lDq4FHA.3352@.TK2MSFTNGP10.phx.gbl...
> As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
>> Hi just wondering how to rename the database using enterprise manager.
>> thanks.
>> --
>> Paul G
>> Software engineer.
>

Database Relationship

Hi all,
Where can I see the Table relationship in Enterprise Manager. I see all
Tables but to run some query using two or more tables I need to know which
table is connected with that particular table. Can someone help me.
Thanks,
Betre
Two ways, and they only work if there is actually a defined relationship.
1. Look in the diagrams, or create a new one with the tables in question.
Anything you do here as far as adding/deleting may affect the actual table.
Its not just a picture...
2. Create a new view and add the tables. If the relationship has been
defined, it will fill in automatically
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"betrek" <betrek@.discussions.microsoft.com> wrote in message
news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> Hi all,
> Where can I see the Table relationship in Enterprise Manager. I see all
> Tables but to run some query using two or more tables I need to know which
> table is connected with that particular table. Can someone help me.
> Thanks,
> Betre
|||Also, you can right click on tables and look at the design. Then right
click on columns and check out relationships, keys, indexes, etc.
Kevin3NF wrote:[vbcol=seagreen]
> Two ways, and they only work if there is actually a defined relationship.
> 1. Look in the diagrams, or create a new one with the tables in question.
> Anything you do here as far as adding/deleting may affect the actual table.
> Its not just a picture...
> 2. Create a new view and add the tables. If the relationship has been
> defined, it will fill in automatically
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "betrek" <betrek@.discussions.microsoft.com> wrote in message
> news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...

Database Relationship

Hi all,
Where can I see the Table relationship in Enterprise Manager. I see all
Tables but to run some query using two or more tables I need to know which
table is connected with that particular table. Can someone help me.
Thanks,
BetreTwo ways, and they only work if there is actually a defined relationship.
1. Look in the diagrams, or create a new one with the tables in question.
Anything you do here as far as adding/deleting may affect the actual table.
Its not just a picture...
2. Create a new view and add the tables. If the relationship has been
defined, it will fill in automatically
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"betrek" <betrek@.discussions.microsoft.com> wrote in message
news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> Hi all,
> Where can I see the Table relationship in Enterprise Manager. I see all
> Tables but to run some query using two or more tables I need to know which
> table is connected with that particular table. Can someone help me.
> Thanks,
> Betre|||Also, you can right click on tables and look at the design. Then right
click on columns and check out relationships, keys, indexes, etc.
Kevin3NF wrote:[vbcol=seagreen]
> Two ways, and they only work if there is actually a defined relationship.
> 1. Look in the diagrams, or create a new one with the tables in question.
> Anything you do here as far as adding/deleting may affect the actual table
.
> Its not just a picture...
> 2. Create a new view and add the tables. If the relationship has been
> defined, it will fill in automatically
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "betrek" <betrek@.discussions.microsoft.com> wrote in message
> news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...

Database Relationship

Hi all,
Where can I see the Table relationship in Enterprise Manager. I see all
Tables but to run some query using two or more tables I need to know which
table is connected with that particular table. Can someone help me.
Thanks,
BetreTwo ways, and they only work if there is actually a defined relationship.
1. Look in the diagrams, or create a new one with the tables in question.
Anything you do here as far as adding/deleting may affect the actual table.
Its not just a picture...
2. Create a new view and add the tables. If the relationship has been
defined, it will fill in automatically
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"betrek" <betrek@.discussions.microsoft.com> wrote in message
news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> Hi all,
> Where can I see the Table relationship in Enterprise Manager. I see all
> Tables but to run some query using two or more tables I need to know which
> table is connected with that particular table. Can someone help me.
> Thanks,
> Betre|||Also, you can right click on tables and look at the design. Then right
click on columns and check out relationships, keys, indexes, etc.
Kevin3NF wrote:
> Two ways, and they only work if there is actually a defined relationship.
> 1. Look in the diagrams, or create a new one with the tables in question.
> Anything you do here as far as adding/deleting may affect the actual table.
> Its not just a picture...
> 2. Create a new view and add the tables. If the relationship has been
> defined, it will fill in automatically
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "betrek" <betrek@.discussions.microsoft.com> wrote in message
> news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> > Hi all,
> >
> > Where can I see the Table relationship in Enterprise Manager. I see all
> > Tables but to run some query using two or more tables I need to know which
> > table is connected with that particular table. Can someone help me.
> >
> > Thanks,
> >
> > Betre

database protection?

I know SQL Server has a good security system for the enterprise manager.
But are SQL server 2005 databases password protected?
In other words, suppose I make a database, named DATA1, with all its tables
and data on SQL Server 2005 I.
Can any one who download SQL Server Express 2005 open DATA1 on such a server
?
Are databases password protected like MS Access databases?
Thank you.newbie in hell (newbieinhell@.discussions.microsoft.com) writes:
> I know SQL Server has a good security system for the enterprise manager.
> But are SQL server 2005 databases password protected?
> In other words, suppose I make a database, named DATA1, with all its
> tables and data on SQL Server 2005 I.
> Can any one who download SQL Server Express 2005 open DATA1 on such a
> server?
> Are databases password protected like MS Access databases?
No. If you have been able to get hold of database file for SQL Server,
you can attach it to a server do whatever you like with it. What you can
do is to use encryption, and protect the encryption keys with the service
master key. In that case, it's difficult to get hold of everything, if
you attach it a different server.
I don't know Access, but from what I've heard passwords for Access databases
are not much of a protection either. It stops the stray wanderer, but
anyone who is decided to get in, will do so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx