December 16, 2008 at 9:43 am
All,
I am still fairly new to SQL Server so please pardon the simple questions I may ask on these forums.
As a junior dba I feel it is necessary to have a thorough understanding of t-sql especially for critical procedures such as backups and restores.
I have been recently practicing t-sql backup and restore command scenarios and was curious about the necessity of the Recovery option with the Restore command.
If I have perform a full database restore do I need the WITH RECOVERY option, even though I am not restoring transaction or differential backups?
Also how can I run the RESTORE command and recreate the database I am restore with a different name so that the corrupted database and restored database can sit side by side and both can be queried? Thanks.
Keith
December 16, 2008 at 9:50 am
with recovery is the default, so you do not need to explicitly include it.
To restore to a new name :
restore database newname from disk = 'backup file old database'
with move
(datafile and log file)
check out RESTORE in BOL. And carry on practicing. If u r not sure what happems in a certain scenario, test it out.
---------------------------------------------------------------------
December 16, 2008 at 9:55 am
Let's say you have a backup of DB_NotGood at e:\db_notgood.bak.
If you perform a select name, filename from DB_NotGood.dbo.sysfiles it shows
name filename
NotGood_Data d:\mssqlotgood.mdf
NotGood_Log d:\mssqlotgood.ldf
You can restore the database to another name with the restore command for example
restore database DB_CopyOfNotGood from disk = 'e:\db_notgood.bak'
/*we use with move to change the name of the files so we don't try to overwrite the old*/
with move 'NotGood_Data' to d:\mssql\CopyOfNotGood.mdf',
move 'NotGood_Log' to 'd:\mssql\CopyOfNotGood_Log.ldf'
with stats = 5 /*I like to know it's actually working*/
December 16, 2008 at 9:57 am
kwoznica (12/16/2008)
All,I am still fairly new to SQL Server so please pardon the simple questions I may ask on these forums.
As a junior dba I feel it is necessary to have a thorough understanding of t-sql especially for critical procedures such as backups and restores.
I have been recently practicing t-sql backup and restore command scenarios and was curious about the necessity of the Recovery option with the Restore command.
If I have perform a full database restore do I need the WITH RECOVERY option, even though I am not restoring transaction or differential backups?
Also how can I run the RESTORE command and recreate the database I am restore with a different name so that the corrupted database and restored database can sit side by side and both can be queried? Thanks.
Keith
Your most valuable resource should be "Books Online" (BOL).
If is very good for sql2005.
It describes all options for the commands or features you requier.
With a critical statement like restore, it is better to not trust the defaults.
Defaults may change, you know ....
/* restore db to newdbname */
-- what logical names are on the db ?
restore filelistonly
from DISK = 'x:\SQL\BACKUP\olddbnameFull.BAK' ;
-- provide new locations for the datafiles !
Restore database XXX
from DISK = 'x:\SQL\BACKUP\olddbnameFull.BAK'
with MOVE N'olddbname_data' TO N'x:\SQL\Data\XXX.mdf'
, MOVE N'olddbname_log' TO N'x:\SQL\data\XXX_log.ldf'
, RECOVERY
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2008 at 10:00 am
I've never really used it, but I recently read a book that recommended it and I agreed. I'll use it from here on out.
It's a good habit to get into to specify norecovery and recovery. If you are restoring dbs and logs, you want to choose when the database comes online, so you always use norecovery or recovery, depending on the item you are restoring. I thought that was good advice and a good habit to get into.
January 8, 2009 at 2:50 pm
All,
Thank you for your suggestions on how to execute such a basic yet crucial command.
I am testing this scenario now and have come up with a few different questions which pertain to this thread.
I have constructed the statement below and receive error messages from ssms.
RESTORE DATABASE NAV5LIVE08
FROM DISK = 'E:\NAV50LIVE_backup_200901072000.bak'
WITH MOVE 'NAV5LIVE_DATA.MDF' TO 'E:\COPYDB\NAV5LIVE08_DATA.MDF',
MOVE 'NAV5LIVE_1_DATA.NDF' TO 'E:\COPYDB\NAV5LIVE08_1_DATA.NDF',
MOVE 'NAV5LIVE_LOG.LDF' TO 'E:\COPYDB\NAV5LIVE08_LOG.LDF',
RECOVERY,
STATS = 5;
My production database is named NAV5LIVE
I am just appending the 08 to differentiate between the restored and production db.
After executing this command I receive the following message in SSMS.
Msg 3234, Level 16, State 2, Line 7
Logical file 'NAV5LIVE_DATA.MDF' is not part of database 'NAV5LIVE08'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
So my question then is should I just create the database in ssms with the appropriate file names and then restore over that database or is the easier way using RESTORE FILELISTONLY?
Or better yet should I create the database in sql using the CREATE DATABASE statement and then restore over that database?
Thanks for the help.
Keith
January 8, 2009 at 2:57 pm
kwoznica (1/8/2009)
WITH MOVE 'NAV5LIVE_DATA.MDF' TO 'E:\COPYDB\NAV5LIVE08_DATA.MDF',MOVE 'NAV5LIVE_1_DATA.NDF' TO 'E:\COPYDB\NAV5LIVE08_1_DATA.NDF',
MOVE 'NAV5LIVE_LOG.LDF' TO 'E:\COPYDB\NAV5LIVE08_LOG.LDF',
RECOVERY,
STATS = 5;
I believe the highlighted name should be the logical file names, not the physical file names. You can use the command that the error suggested to see the logical names that are in the backup (that's all that RESTORE ... WITH FILELIST does), or you can query sys.database_files to see them for an online database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2009 at 8:42 am
This is an awesome thread. Thank you everyone for your input. I got to put these suggestions to valuable use just recently with one of our software developers standing over my shoulder. I think I impressed my viewer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply