How to safely replace SP old MDF file with the updated one on SQL 2005

1

HI Team,

I am a server Maintenance guy and i have given a task to work on restore Sharepoint missing data.
Its a tier 2 enviornment where in SQL Server 2005 is hosted on Server2008 and Sharepoint 2010 RTF version is on Server2008 as well.
We setup a mirror of SQL and Sharepoint server on Azure and the machines are in Stopped days 4 days back, since the Onprem Machines were running.

But due to a disaster onprem SQL and SP servers Machines are gone today. We have turn on the Azure SQL and APP server and connected via Site to site VPN. All portals are running , but the data on Sharepoint is missing since the MDF files on SQL server are 4 days old.

We have the latest MDF files on our backup solutions,So I am planing to Detach the DB file for all portals > Stop the MSSQL Service and Replace the old MDF files on the c drive with the update ones> then Start MSSQL service and Attach the MDF files.
Please suggest if the above plan looks safe or let me know the right way to do this

2

How did you get the latest MDF files? If you just copied them from the source system then they may not work - as SQL Server locks the files and they cannot be copied directly.

If you used some sort of SAN backup that integrates with SQL Server so that SQL Server is frozen - then the drive snapped - then unfrozen then your copy of the MDF file will be valid at that point in time.

Generally - the best way to insure you have a good copy is to perform a database backup. That is a SQL Native backup that creates a .BAK file and is schedule to run daily - with frequent transaction log backups where you can then apply the full backup and transaction log backups to recover the data to a point in time.

If you copied the MDF/LDF files after SQL Server was stopped on the source system - then you can detach the current databases and attach the existing files. But it all depends on how you got those files and if they are valid.

1 Like
3

Thanks Jeff for replying on this.

We have backup solution called Solarwinds Max backup which take file and folder backup + MS SQL (VSS) backup seperately.
So i have restored the MDF files from the MS SQL (VSS) recent backup taken at 1
Please clarify to get everything up to date,

  1. Stop all share point services.
  2. First i need to Detach all the databases
  3. Replace the old MDF files with new one
  4. Attach the databases again
4

Your plan looks good

1 Like
5

Thanks you Jeff :slight_smile:

6

Depending on "how big" the database is we tend to take a slightly different approach, because of the time that the database is Detached (in your solution). If you have available downtime then your solution is fine of course, so this applies more to 24/7 systems. This assumes that the database is in FULL Recovery Model (hopefully that's the case for anything mission critical!!)

  1. Take a full backup from Source
  2. Restore on Destination using WITH NORECOVERY (database available for more restores)
  3. If that took "a long time" then repeat with a DIFF backup
  4. Restore all TLog backups (after FULL / DIFF) to bring you close to real time
  5. Now stop all services / disable all connections / disable all scheduled jobs (or prevent access) on Source
  6. Take a final TLog backup from Source and restore using WITH RECOVERY so that the database is Live
  7. Re-start everything on Destination

Downtime for this approach can be as short as a couple of minutes