Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gravyface
Starting Member
16 Posts |
 Posted - 2006-11-10 : 12:18:58
|
| Hello,I have a .BAK backup file I created using Enterpise Manager (SQL 2000/SP3a) that I'm trying to restore to a different database server running the same version of SQL 2000. This new database server has the same database, with its own .bak/backup -- its empty as the software was just reinstalled and only created an empty database. To restore, I'm choosing "from device", "add" device, and selecting the .BAK file from the C: drive.The restore works fine, however, its only the data structure -- none of the data is there. In fact, the .BAK file is 309MB in size on disk but the restored equivalent (under the database properties) is only 38 MB.What gives? Is the existing empty database (and its own .BAK file) conflicting? Should I blow away the existing database first before trying to restore the 309 one?TIA |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-10 : 16:00:47
|
| A Backup File only stores the structure + data from the original database - so may be smaller than the database's physical files (as they may contain some slack).When you restore a Backup file the restored database will be the same size as the original and contain both the structure and database - there is no route to selectively restore part of the backup, so its all-or-nothing.Here a link to the restore syntax - in case it helpshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20exampleKristen |
 |
|
|
gravyface
Starting Member
16 Posts |
Posted - 2006-11-10 : 16:11:36
|
| Umm. I'm not really sure what you're getting at.The .BAK file is 309 MB and the original database is 309MB. When I restore the .BAK file on a totally different server, the database is only 38 MB and alot of data is missing. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-11 : 01:39:15
|
Yup, I hear what you are saying, but it doesn't make sense to me:You say your database is 309MB - how are you measuring that? Size of MDF, LDF, or a figure that (for example) Enterprise Manager gave you for the "used size of the database?Either way its not particularly relevant, a BAK file does NOT store the "empty" space in a database, so it CAN be smaller than a database, but nothing wrong with it being the same size.However, when you restore a database what you get is a copy of the original, so you should be getting the exact same 309MB database you backed up.If you aren't then something is wrong.Is it possible that you didn't restore the database at all (i.e. it went thought the motions but didn;t touch the database you are looking at)? Was the "structure" there before you did the restore?If so I reckon the restore didn't work, and maybe any error message was not displayed."restore, I'm choosing "from device", "add" device, and selecting the .BAK file from the C: drive."Sounds like you are using Enterprise Manager to make the restore. There are a bunch of pitfalls to the GUI interface IMHO - there is a second tab with some questions about overwriting the database, and moving the Physical files, as well as changing the logical names of stuff. Although it may seem a bit daunting using the RESTORE command directly gives a lot more control, and understanding I think, of what SQL Server is actually going to do with the files!Has a different, new database, of size 309MB appeared on your server? Maybe it has restored it with a different name?What doesRESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'say? That will tell you the size of the backup, which would help to confirm that it contains 309MBAlso it would help to have the (relevant) results from this query (adjust the TOP 100 as appropriate to include the Restore you made, they are in date order)SELECT DISTINCT TOP 100 RH.destination_database_name, RF.file_number, RH.restore_date, RF.destination_phys_drive, RF.destination_phys_name, [Backup Set Name]=BS.name, BS.description, BS.database_creation_date, BS.backup_start_date, BS.database_name, BS.server_name, RH.restore_type, RH.replaceFROM msdb.dbo.restorehistory AS RH LEFT OUTER JOIN msdb.dbo.restorefile AS RF ON RF.restore_history_id = RH.restore_history_id LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG ON RG.restore_history_id = RH.restore_history_id LEFT OUTER JOIN msdb.dbo.backupset AS BS ON BS.backup_set_id = RH.backup_set_idORDER BY RH.restore_date DESC, RF.file_number DESC Kristen |
|
|
|
|
|
|
|
|
Restore from backup not full-size