I’d like to think I’ve done the proper research to ensure that I’m going about this the correct way, but to be completely honest I was told to do it this way, and found enough other guides to convince myself this was the right method. The fine folks on twitter have helped to convince me through the #SQLHelp Twitter hashtag.
So there I was, alone, in the cold dark world of upgrading SQL Server from 2008 R2 to 2012. This was my first major project as then company’s DBA and I would be lying if I didn’t say I was nervous. I’ve tried to document each step I took for two reasons. One reason is for growth. I’m hoping the procedure will be analyzed by more senior veterans and missteps corrected for future upgrades. Second is for more junior DBAs to have something to guide them as they grow. I still consider myself as the Junior DBA, the only downside is there really is no Senior DBA here, so I’m the THE DBA.
My task: To upgrade our 2008 R2 production server to 2012 during a 2-day maintenance window over Thanksgiving.
Conditions: Brand new blade with a new Windows 2012 server.
- Restore current DBs to new Server
- Test new server using Kubisys virtual environment
- Use SQL Server 2012’s enhancements
- Improve Performance
My company purchased a new blade to house all things SQL Server, which sounded great to me as I was responsible for the Production, Test, and Development servers. Little did I know the amount of SQL Server we were using which I was not responsible for!
Instances to Facilitate: 10
Virtual Servers: 5
As this will only pertain to setting up the Production Server I’ll limit the details to that one.Specs:
Blade: (24 Cores, 500 GB)
Old Server: (1 Core, 50 GB)
New Server: (10 Cores, 200 GB)
So Day 1: I set out to set up the server & SQL Server instance using best practices and guides found on the internet. Having just returned from the SQLPass Summit 2013 I knew which blogs / guides to follow. I loved Glen Berry’s (B|T) Pluralsight course on Installing SQL Server 2012.
Day 2: Restoring Databases to the new server. It’s simple, I’ve been practicing my restore strategy from our current production to test environments for months! So I run this:
(Note: As I am not restoring anything other than the full backup I’ve chosen to use “WITH RECOVERY”)
BACKUP DATABASE [DATABASE] TO DISK = N'FILESHARELATEST_BACKUPSSERVERDATABASEDATABASE_FULL.BAK' WITH NOFORMAT, NOINIT, NAME = N'DATABASE_FULL', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Which ran successfully and I saw this:
Processed 11 pages for database 'DATABASE', file DATABASE_log' on file 1. BACKUP DATABASE successfully processed 1128787 pages in 105.936 seconds (83.245 MB/sec).
Sweet! Everything’s going in sequence. Now it’s time to restore to the new server!
RESTORE DATABASE DATABASE FROM DISK = 'FILESHARELATEST_BACKUPSSERVERDATABASEDATABASE_FULL.BAK' WITH RECOVERY
Results in the following Errors:
Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "E:SQLDataDATABASE.mdf" failed with the operating system error 3(The system cannot find the path specified.). Msg 3156, Level 16, State 3, Line 1 File 'DATABASE' cannot be restored to 'E:SQLDataDATABASE.mdf'. Use WITH MOVE to identify a valid location for the file. Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "D:SQLDataDATABASE_LOG.ldf" failed with the operating system error 3(The system cannot find the path specified.). Msg 3156, Level 16, State 3, Line 1 File 'DATABASE_log' cannot be restored to 'D:SQLDataDATABASE_LOG.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
There is no E: or D: on the new server.
Looks like the original creator didn’t follow Glenn Berry’s advice!
Current server has these drives:
New server has the following drives:
To find out which volumes exist so that you can move them to the correct folders use:
RESTORE FILELISTONLY FROM DISK = 'FILESHARELATEST_BACKUPSSERVERDATABASEDATABASE_FULL.BAK' WITH RECOVERY
Which will give you this output:
So now we add WITH MOVE to the restore script.
RESTORE DATABASE DATABASE FROM DISK = 'FILESHARELATEST_BACKUPSSERVERDATABASEDATABASE_FULL.BAK' WITH MOVE 'DATABASE' TO 'P:SQLDATADATABASE.MDF', MOVE 'DATABASE_LOG' TO 'L:SQLLOGSDATABASE_LOG.LDF', RECOVERY GO
This will move the DATABASE.MDF to the P: drive as we wanted, and the logs to the L: drive during the restore. I repeated this for a few databases and then my next post will continue where I’ve left off today.
Please let me know if you have any suggestions or corrections to this method, I’m still in the planning / testing phase before we go live so all input is welcome!