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 |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2010-02-08 : 11:02:36
|
| I want to restore a db in another server.db files size .mdf 367MB, .ldf 194MB, backup size 323MB.DB size 547MB, Space avialable 50 MBIf I restore the db, the .LDF size will remain 194MB? or do i need to shrink it before taking backup? |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2010-02-08 : 11:08:25
|
quote:
Originally posted by laddu
I want to restore a db in another server.db files size .mdf 367MB, .ldf 194MB, backup size 323MB.DB size 547MB, Space avialable 50 MBIf I restore the db, the .LDF size will remain 194MB? or do i need to shrink it before taking backup?
I am restoring db as part of the migration |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-08 : 11:48:14
|
| The Restore will automatically resize the target database if necessary - provided you have enough DISK space. No need to expand the Target database, or Shrink the Source database beforehand (again, provided you have enough disk space on Target server) |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2010-02-09 : 10:34:21
|
| Thanks!One question :What happens to pending/active transactions once I restore db in another server. SQL server 2005 will rollforward the transactions? do we loose any data(5 to 10 KB)As part of the migration ..We will stop appln first then take full backup and restore in another server and reconnect |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 11:04:08
|
| A FULL BACKUP includes all completed (i.e. COMMITted) transactions as-of the end of the backup (I think that's right - it depends on the version you are using, but I'm pretty sure from SQL 2000 Full Backup includes all transaction committed as-of the time of the backup ending).So if you restore that's what you have got. Anything after that is not included.I do the following:Backup TLog (to reduce the size of the following TLog)Full BackupCopy Full Backup to the new machine, and restore using NORECOVERY (i.e. database is NOT ready for use)(For a large database this may take a while, this process is designed to acoomodate that)Now set the Source database to DBO-Only, Single User - or READ-ONLY - and put up a Holding Page on Web site, or whatever is necessary to alert users to the server being unavailable. (Note that DBO-Only, Single User / READ-ONLY is intended to stop any automated jobs from running and changing the data)Take a final TLog backupCopy the TLog backup (including any other TLog backups that may have been scheduled whilst you were copying / restoring the Full backup) to the new machine and restore them using RECOVERYBecause the Tlog is small this operation is very fast.New database is ready on new server for users to connect to.If you are also upgrading (e.g. from SQL 2005 to SQL 2008) at the same time see this thread for other steps you may want to consider:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230&SearchTerms=2008 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 11:07:54
|
| P.S. And after that change the original, source, database to OFFLINE to make sure that nothing can connect to it. |
|
|
|
|
|
|
|
|
Restoring database