Migrating database to new storage

SQL Server Administration
1

Dear Experts,
we are planning to move all the databases to new storage on the same server.
Is there a way to perform this activity without database down time?

Thanks

2

The way we have done that in the past is:

Full backup - restore (using WITH NORECOVERY) on target

Near time of cut-over:
Stop/Disable all Scheduled Jobs etc. on SOURCE server (except perhaps TLog backup)
make transaction backup (to reduce size of TLog included in the following backup)
Make DIFF backup - restore (using WITH NORECOVERY) on target

Put up holding page / prevent user access

Set SOURCE database to readonly (to prevent any unforeseen updates)
Make TLog backup - restore (using WITH NORECOVERY) on target
Also include in the restore any TLog backup made by scheduled task

Provided restore is OK then:

RESTORE DATABASE TargetDatabaseName WITH RECOVERY
ALTER DATABASE TargetDatabaseName SET READ_WRITE

swap over the hardware IP's, or change the APP to point to the new location

Remove the holding page.

When we have done this the holding page is up for less than a minute and users sessions are retained and they can carry on (holding page explains how long they need to wait / what they need to do in order to resume)

There's a further trick, on the SOURCE server, which puts the database into a state where you could just RESTORE TLog backups FROM the Target - e.g. if you needed to revert back. I've forgotten what that is :cry: but if you need it I'll look it up.

3

Further, more detailed albeit rather old, link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44537#145142

4

Thanks Kristen.
So the downtime depends on the database size for backup?
Our biggest db is around 300 GB.

5

Not directly. You can make the FULL Backup and DIFF and restore them whilst the system is in use. Its the time to make the final TLog backup, and restore that, which governs the cut-over time. If the Restore of the DIFF took "some time" you can make a TLog backup, and restore that, before putting up the holding page so that you final TLog backup, copy-to-target and Restore is as short as possible.

If you are upgrading to new version of SQL there is also an "Update" that SQL will do after the WITH RECOVERY command, and that can take "some time". It is also necessary to do full index rebuilds before queries will be optimal, but that can happen after the system is live. If you aren't changing SQL Version then you don't have to worry about that.

(This assumes you are using Full Recovery Model. There are other options using fail-over servers, but there is some complexity in that route, if you don't already have experience).

We cut-over a client's server to a Shiny New Super Fast machine with a holding page and no loss of sessions etc. when their pre-Christmas online orders skyrocketed one year (and they had ignored our advice that their server was inadequate for the expected sales ...) so it can be done during "heavy load" ... but obviously it would be better to do it in the middle of the night / whatever, when the load is light.