I’ll be speaking at the New Jersey SQL Server Users Group next week

Published On: 2008-08-14By:

Why Entry-Level Certs Aren’t Enough to Get You a Job

Published On: 2008-08-13By:

Don’t Consolidate Yourself Into Performance Problems

Published On: By:

Consolidated too many databases or too many instances onto a single server?  Got users screaming for their own servers back?

Join myself and David Swanson from Quest Software (and frequent poster on SQLServerPedia.com) as we show you how to identify problematic applications so that they don’t take down all of the other applications on the server at the webcast “Don’t Consolidate Yourself Into Performance Problems“. Attend this webcast to save your consolidation project and to keep your users happy.

The webcast will be on Thursday, August 21, 2008 at 8 a.m. Pacific / 11 a.m. Eastern / 4 p.m. United Kingdom / 5 p.m. Central Europe.  After the presentation there will be a Q&A session where David and I will answer as many questions as possible.

Denny

Contact the Author | Contact DCAC

Back To Basics: The RESTORE DATABASE Command

Published On: 2008-08-11By:

The restore database command is what is used to recover a database which has been backed up using the BACKUP DATABASE command.  The syntax of the RESTORE DATABASE command is very similar to the BACKUP DATABASE command in many respects.

 You start with where you are restoring the database from.

RESTORE DATABASE MyDatabase FROM DISK='E:BackupsMyDatabase.bak'

Then if you need to move the physical files to another location because your disks are laid out differently, or because your folder layout is different you can add a MOVE command for each file you want to move.  For each MOVE command you specify the local file name, and the new physical file name.

RESTORE DATABASE MyDatabase FROM DISK='E:BackupsMyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Data.mdf',
  MOVE 'MyDatabase_Log' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Log.ldf'

When restoring the database, many people think that you have to create the database first.  You do not.  When restoring a database through the UI (Enterprise Manager, or SQL Server Management Studio) if you create the database first, it will then be selectable in the drop down menu.  Even with using the UI, creating the database first is optional as you can simply type in the name of the new database in the UI.

If you are restoring from a striped database backup then you will need to specify the name of all the members of the strip.

RESTORE DATABASE MyDatabase FROM DISK='E:BackupsMyDatabase1.bak',
  DISK='E:BackupsMyDatabase2.bak'
WITH MOVE 'MyDatabase_Data' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Data.mdf',
  MOVE 'MyDatabase_Log' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Log.ldf'

If you need to restore a differential or log backups after you restore your full backup you will want to place you backup with the NORECOVERY flag. This will tell SQL Server not to complete the recovery process, and to leave the database in an unusable state. This will allow you to continue the restore process. Once the database has been switched into a writable state you won’t be able to restore any transaction logs to the database without restoring from the full backup again.

RESTORE DATABASE MyDatabase FROM DISK='E:BackupsMyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Data.mdf',
  MOVE 'MyDatabase_Log' TO 'D:MSSQLMSSQL.1MSSQLDataMyDatabase_Log.ldf',
  NO RECOVERY

Denny

Contact the Author | Contact DCAC
1 416 417 418 419 420 452

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link