Sql-server – SQL Server Express – Move by Copying Data Folder

databasemigrationsql serversql-server-2008

We are moving our SQL Server to new hardware. Initially we were going to do this simply via backup and restore, but noticed we'd have to recreate all the logins. We don't want to do that 🙂

Downtime is not an issue, so would it be possible to simply stop the old and new server and copying all of the mdf files between the two? This would copy the master database which has the logins as well as all of the databases. I just don't know if it is supported.

We are using SQL Express so don't have the fancy bells and whistles, but on the flip side downtime is not an issue.

Thanks!

Best Answer

The location of every database file for every database is stored in master, in absolute full path. That means that you would have to copy all the files at exactly the same full path on the new server (including drive letter). If this is possible, then you can copy the files. After the copy the @@SERVERNAME will return the old server name, and this can be corrected by following the procedure described in KB818334. Another thing to watch out for is if you have anything encryption related that may have been saved using the DPAPI machine or service key (eg. linked server credentials, proxies, database master keys etc), they won't be accessible after the copy because the new host DPAPI won't be able to decrypt them.

Overall, copying the files (specially master) is not a supported procedure and may result in a unusable server. Copying logins is a well know procedure and supported (there is even an SSIS task for it).

Related Solutions

Sql-server – Fastest way to move a SQL Server database between systems and SQL Server versions

65 GB really is trivial, but may not be over in copying over a slow LAN, or if you can't afford a few minutes of down-time.

The fastest way to cut-over (and keep it simple) is to do logshipping (no recovery mode) between your your existing db and your new db.

You should be able to copy logins & agent jobs before the cut-over & leave them disabled till you move your live traffic over.

Not really a mistake to switch servers and version of sql server at the same time, depends on your requirements. Just remember that bringing the db online on sql server 2008 R2 is a one way process that will upgrade the data files the first time the db does a recovery & there is no going back!

Your other not so simple option is to do the following:

on your new server, install the same version of sql server as your existing server
setup database mirroring btwn existing & new server
do the same prep in copying logins/agent jobs/ other dependencies
failover the mirror to new server making it the primary (assuming you have support from your app to do this)
update existing server to 2008 r2, then fail back the mirror upgrade new server to 2008 r2 then failover again remove the mirror, shutdown old server

There is probably many variations of the above approaches. The moral of the story is that you have to consider the cost of the cutover vs. the cost of down-time. It's a trade-off.

Sql-server – Make a copy of database with SQL Server Management Studio Express 2005

Backup and restore is the quickest way to do it. With RESTORE sql command you specify the name of new database:

RESTORE DATABASE db_new_name
  FROM DISK = 'C:\MSSQL\BACKUP\old_db.BAK'
  WITH MOVE 'old_db_Data' TO 'c:\MSSQL\DATA\new_db.mdf',
       MOVE 'old_db_Log' TO 'c:\MSSQL\DATA\new_db.ldf',
  RECOVERY

The sql command above can help you:

RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\BACKUP\old_db.BAK'

Best Answer

Related Solutions

Sql-server – Fastest way to move a SQL Server database between systems and SQL Server versions

Sql-server – Make a copy of database with SQL Server Management Studio Express 2005

Related Topic