DB restore fails saying it needs more free space

Hi,
I have run into this peculiar issue
* DB was set to 'suspect' status..
* checked for physical disk size constraints
- log file is 50 gigs
* so tried to reset status...failed...by this time...DB
was missing from list in SQL EM
* so tried to restore from backup
* restore says it needs 50 gigs of space
* actual capacity of disk is 32 gigs
* how to restore the db'
appreciate all the help
thanks
vishwasSQL Server need the same amount of space for the db as it took when you did the backup. I am a
bit confused: You say the log was 50 gig, and you say that you now have only 32 gig?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"vishwas" <vanbunty@.yahoo.com> wrote in message news:124201c3460e$856a2b80$a101280a@.phx.gbl...
> Hi,
> I have run into this peculiar issue
> * DB was set to 'suspect' status..
> * checked for physical disk size constraints
> - log file is 50 gigs
> * so tried to reset status...failed...by this time...DB
> was missing from list in SQL EM
> * so tried to restore from backup
> * restore says it needs 50 gigs of space
> * actual capacity of disk is 32 gigs
> * how to restore the db'
> appreciate all the help
> thanks
> vishwas
>

DB restore error

Hi all, I'm getting this error when trying to restore a database:

"Device activation error. The physical file name 'E:\program files\MS SQL server\MSSQL\data\DBname.mdf' may be incorrect. File 'DBname_dat' cannot be restored to 'E:\program files\MS SQL server\MSSQL\data\DBname.mdf'. Use WITH MOVE to identify a valid location for the file. Device activation error. The physical file name 'E:\program files\MS SQL server\MSSQL\data\DBname.ldf' may be incorrect. File 'DBname_log' cannot be restored to 'E:\program files\MS SQL server\MSSQL\data\DBname.ldf'. Use WITH MOVE to identify a valid location for the file. RESTORE DATABASE is terminating abnormally."

What I'm doing is, on another personal sql server a backup was created of this DB, then sent to me. Now I created a new DB named after the one that was backed up. Then I go to restore the DB into the one I created. But i get this error. How can I trouble-shoot this?From the BOL:
"Re-creating Database Files
Restoring a database automatically creates the files needed by the database backup to restore the backup into. The database files (hence the database) do not need to be created before restoring a backup. By default, the files created by SQL Server during the restoration process use the same name and path as the backup files from the original database on the source computer. Therefore, it is useful to know in advance the files that are created automatically by the restore operation, because:

The file names may already exist on the computer, causing an error.

The directory structure or drive mapping may not exist on the computer.
For example, the backup contains a file that it needs to restore to drive E, but the destination computer does not have a drive E.

If the database files are allowed to be replaced, any existing database and files with the same names as those in the backup are overwritten, unless those files belong to a different database."|||Ahhh, thank you. I found where I could change the path and successfully restore the DB.|||Your problem probably is that you don't have the path specified on your computer ('E:\program files\MS SQL server\MSSQL\data\') and sql server tries to restore db to its original location recorder in backup file. First, run this command
RESTORE FILELISTONLY FROM DISK='path to your backup_file' It shows you logical and physical file names present in your backup. Now you have two options: Either create on your server database of the same name and same logical file names, but different physical filename (on location that is valid on your computer) and perform restore or preferrably you can use RESTORE DATABASE with MOVE clause. Look up RESTORE DATABASE topic in Books Online for exact syntax but from your error message I assume something like

RESTORE DATABASE your_db_name --doesn't have to be the same as original
from DISK='path to your backup_file'
with move 'DBname_dat' to 'location_valid_on_your_computer\DBname.mdf',
move 'DBname_log' to 'location_valid_on_your_computer\DBname.ldf'

mojza

DB mirroring + load balancing

Is it compatible to set up SQL DB mirroring on 2

different physical servers (High availbility + FULL transaction safety +

Automatic failover with a third server as witness) and simultaneously set up

network load balancing between these 2 servers (option with Windows Server 2003

Enterprise) ?

If yes, which type of licenses do I need and how

many (for SQL server 2005 and Windows Server 2003) with such a configuration

?

- 4 physical servers in total : A, B, C and

D

- load balancing between A and B (same application)

- load balancing between C and D (same application)

- A hosts principal DB X, B hosts mirror of DB

X

- C hosts principal DB Y, D hosts mirror of DB

Y

- D is witness for A and B

- B is witness for C and D

- A and B : only internal clients <

25

- C and D : both internal and external clients

(internal clients <25, number of external clients is unknown (>25)

)

Hoping this description will be helpful enough for

you...

(I am looking for a very high availability

system)

Thank you in advance for your support.

This licensing question is pretty much urgent (for

a bid) : a quick answer would be very appreciated...

Not going in details of your design.

There is one thing I know for sure. MS is considering a "mirror" as 1 license. Confirmed by MS "presales and licensing division". So, whatever license you will bye (Enterprise or Standard depending on hardware you are going to use) - you need two licenses for MS SQL for 4 servers listed in your design.

|||

The database on mirror server is always in the "Restoring" state, and you can't connect to this database. I didn't get how you plan to achieve load balancing with this.

|||

The load balancing is set up for automatic total load transfer on one server when the other one is lost (high availability, no time to activate the back-up server).

When 100% load is taken by one server, I need this server to access (read & write) to the back-up SQL database that shall be a real mirror of the one located on the lost server.

I am not familiar at all with such systems that's why I need validation of the concept I imagined.

According to your comment, mirror database is only here to be able to restore the lost database but, in any case, it could be used as a replacement of the lost one. Am I right ?

Could you recommend me a redundant system architecture maximizing availability ?

Thanks in advance for your expertise.

Yann

|||

Database mirroring allows you to maintain a hot standby of your database.

The mirror database is always in restoring "state", and not available for user connections. When your principal server (the primary server serving the application) fails, you use the mirror to failover. Please note that you don't do a "restore" with a mirror as you mentioned, but failover to the mirror so that the database becomes available to your application.

I suggest reading the following:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

SQL Server Books Online for Database Mirroring.

|||

Tom Rizzo has a chapter in his book "Pro SQL Server 2005" from Apress on database mirroring.

He discusses the ability to use High Availability Mirroring (i.e. automated failover) while using the second server for queries. A quick synopsis is:

A Mirror solution only runs as fast as the slowest database server. So, when you write to the primary database, and the mirror is working, then the time to execute that write is the slowest time of writing to the primary database or the mirror database. So, if you are taxing the resources of your mirror database with other kinds of activity, it can slow down your overall application because the primary server won't commit a transaction until the mirror has also committed the same transaction.

That being said, you can still use the mirror for read only access of the data being mirrored throught the use of Snapshots. As I recall, snapshots are only available in the Enterprise version of SQL Server.

Even though Tom only has one chapter on Mirroring, I found it VERY helpful, and a lot easier to wade through than the MS documentation; at lease for establishing a base understanding of the technology.

Cheers,

Ben

|||

When the database mirror is only used for a standby in the case of emergencies, it does NOT need to be separately licensed. If the mirror is used for other purposes, including reporting, then it must be licensed.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

Thanks for your reply.

I have then others questions :
* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?

Thanks in advance for you additional information.

Yann

DB mirroring + load balancing

Is it compatible to set up SQL DB mirroring on 2

different physical servers (High availbility + FULL transaction safety +

Automatic failover with a third server as witness) and simultaneously set up

network load balancing between these 2 servers (option with Windows Server 2003

Enterprise) ?

If yes, which type of licenses do I need and how

many (for SQL server 2005 and Windows Server 2003) with such a configuration

?

- 4 physical servers in total : A, B, C and

D

- load balancing between A and B (same application)

- load balancing between C and D (same application)

- A hosts principal DB X, B hosts mirror of DB

X

- C hosts principal DB Y, D hosts mirror of DB

Y

- D is witness for A and B

- B is witness for C and D

- A and B : only internal clients <

25

- C and D : both internal and external clients

(internal clients <25, number of external clients is unknown (>25)

)

Hoping this description will be helpful enough for

you...

(I am looking for a very high availability

system)

Thank you in advance for your support.

This licensing question is pretty much urgent (for

a bid) : a quick answer would be very appreciated...

Not going in details of your design.

There is one thing I know for sure. MS is considering a "mirror" as 1 license. Confirmed by MS "presales and licensing division". So, whatever license you will bye (Enterprise or Standard depending on hardware you are going to use) - you need two licenses for MS SQL for 4 servers listed in your design.

|||

The database on mirror server is always in the "Restoring" state, and you can't connect to this database. I didn't get how you plan to achieve load balancing with this.

|||

The load balancing is set up for automatic total load transfer on one server when the other one is lost (high availability, no time to activate the back-up server).

When 100% load is taken by one server, I need this server to access (read & write) to the back-up SQL database that shall be a real mirror of the one located on the lost server.

I am not familiar at all with such systems that's why I need validation of the concept I imagined.

According to your comment, mirror database is only here to be able to restore the lost database but, in any case, it could be used as a replacement of the lost one. Am I right ?

Could you recommend me a redundant system architecture maximizing availability ?

Thanks in advance for your expertise.

Yann

|||

Database mirroring allows you to maintain a hot standby of your database.

The mirror database is always in restoring "state", and not available for user connections. When your principal server (the primary server serving the application) fails, you use the mirror to failover. Please note that you don't do a "restore" with a mirror as you mentioned, but failover to the mirror so that the database becomes available to your application.

I suggest reading the following:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

SQL Server Books Online for Database Mirroring.

|||

Tom Rizzo has a chapter in his book "Pro SQL Server 2005" from Apress on database mirroring.

He discusses the ability to use High Availability Mirroring (i.e. automated failover) while using the second server for queries. A quick synopsis is:

A Mirror solution only runs as fast as the slowest database server. So, when you write to the primary database, and the mirror is working, then the time to execute that write is the slowest time of writing to the primary database or the mirror database. So, if you are taxing the resources of your mirror database with other kinds of activity, it can slow down your overall application because the primary server won't commit a transaction until the mirror has also committed the same transaction.

That being said, you can still use the mirror for read only access of the data being mirrored throught the use of Snapshots. As I recall, snapshots are only available in the Enterprise version of SQL Server.

Even though Tom only has one chapter on Mirroring, I found it VERY helpful, and a lot easier to wade through than the MS documentation; at lease for establishing a base understanding of the technology.

Cheers,

Ben

|||

When the database mirror is only used for a standby in the case of emergencies, it does NOT need to be separately licensed. If the mirror is used for other purposes, including reporting, then it must be licensed.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

Thanks for your reply.

I have then others questions :
* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?

Thanks in advance for you additional information.

Yann

DB Maint plan not shrinking files - why? Please help

Does anyone know why, when the database maintenance plan runs, that the physical data files are not shrunk to 10% as indicated in the plan? I have a 10 G data file (as part of about 10 data files in this db), 5 G used and 5 G unused, that doesn't seem to
release any of the allocated but unused portion of the file back to the OS. (SQL Server 2000)
because GUI doesnt really work. Try to write a script about it. The same way
a big transaction log wont shrink at all using GUI...
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:2824D45B-FFDE-42D9-BBB7-9EC05F070A3B@.microsoft.com...
> Does anyone know why, when the database maintenance plan runs, that the
physical data files are not shrunk to 10% as indicated in the plan? I have
a 10 G data file (as part of about 10 data files in this db), 5 G used and 5
G unused, that doesn't seem to release any of the allocated but unused
portion of the file back to the OS. (SQL Server 2000)
|||in BOL look at DBCC SHRINKDATABASE
You could use a job instead of MP !
"Stephanie" wrote:

> Does anyone know why, when the database maintenance plan runs, that the physical data files are not shrunk to 10% as indicated in the plan? I have a 10 G data file (as part of about 10 data files in this db), 5 G used and 5 G unused, that doesn't seem
to release any of the allocated but unused portion of the file back to the OS. (SQL Server 2000)