When it comes to moving databases there a number of different methods that you can use. The best method will depend on the situation, but most comments/guides/blogs online concentrate on moving individual databases, either by importing/exporting/copying the data between servers using SSMS, backing up and restoring the databases, or detaching and attaching them.
The method described here is a way to move all of the MS SQL databases from one server to another. The basic idea is to stop all of the MS SQL Server services on both servers, copy all of the data files including the system database files (eg the .mdf, and .ldf files) to the same location(s) on the new server, and then start SQL Server on the new server.
There are a few key points to note about this method when considering if it is suitable to your situation :
The new and old servers must have the same version and build of SQL Server installed.
The file paths for your data files must be the same on both servers.
It doesn't matter if the two servers have different names.
The new server must have enough storage space to hold the data files in their current location.
Depending on the amount of data this method can involve a long window of downtime while the data copies over the network
Before stopping SQL Server and starting the move it's worth considering any housekeeping tasks that might need doing. A couple of things I've done before starting a move :
Remove any old / unwanted databases. This process moves all of them, so if they're no longer required why waste time copying a database you don't need.
Consider shrinking database files. Keeping your transaction log files large can avoid situations where the file has to grow, and shrinking isn't generally recommended, however, copying gigs of empty space over a network will needlessly increase the time this process takes. Therefore it may be worth shrinking the file initially, and then manually increasing it again once it's running on the new server.
The steps to the move are :
Install SQL Server on the new server, ensuring it's the same version and build as the old server, and with the same options installed. Ensure that your system database location is configured to be the same as what you have on the old server.
Stop all of the SQL Server services on both servers.
Copy all of the database mdf, ldf and ndf files from the old server to the new server, including all of the system database files. Each file must be copied to the same location on the new server as it was on the old server, otherwise SQL won't be able to find them when when it starts.
Copy any other related files that will be required, eg if any jobs are configured to access or use files on the server then copy them as well.
Start the SQL Server services on the new server.
Open SSMS and confirm that all of the databases are being shown and are accessible.
Either update your application(s) or site(s) to use the new server, or change the server name and network details to match that of the old server so they can connect with the existing details.
Test and confirm that everything is working correctly.
Notes:
I've successfully used this method on SQL Server 2008 R2, however it should also work on all other versions and in fact other people have confirmed it to work with SQL 7 and 2000.
The installations were fairly straight forward setups, with no replication, clustering, Analysis or Reporting services configured, so while I doubt they would stop this from working I can't confirm that for certain.
If you're unsure of the exact setup of your existing installation, check the "Server Server Installation Center" on the old server, go to Tools, and click on "Installed SQL Server features discovery report". This will generate a report showing all of the components installed and what build that are currently.
I recently had an interesting challenge at work. A 3rd party app we use had stopped working, and was generating an error when it tried to start. The error suggested a problem with its MSDE database, though unfortunately it didn't provide a lot of detail. As the resident SQL "expert" I was asked to look at it, and see if I could find a way to repair the database, since the alternative would be a re-installation, and a lot of work re-configuring everything.
Now I've never really played with MSDE before, just full fat SQL, but I soldiered on to see what I could come up with. After trying the obvious things using osql I'd quickly exhausted all the ideas I could think of, and found every article, post or blog relating to database repair made reference to using Enterprise Manager... not much use to me you might think! Well with a little searching I found that you can connect to an MSDE instance using EM, but it's not that straight forward, especially if you're connecting from a different computer. All the information is out there, but even after the fact I can't find anywhere that pulls it all together... so here goes!
There are two or three steps required, depending on whether you are connecting from a local or remote EM installation.
1) Enable TCP/IP on the MSDE database.
Run 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe' on the MSDE machine, and then select tcp/ip and move it to enabled protocol.
2) Create a sysadmin user in the database to allow you to login.
on the MSDE machine. Once connected enter the following commands (nothing will happen in osql until after the 'go' statement) :
1> use master 2> EXEC sp_addlogin 'YourSysAdminUserName','YourPasswd' 3> EXEC sp_addsrvrolemember 'YourSysAdminUserName', 'sysadmin' 4> go
Now on a local installation this may be enough, however on a remote installation you will likely receive an error :
"Login failed for user 'YourSysAdminUserName'. Reason: Not associated with a trusted SQL Server connection."
in which case you will need to change the authentication mode of your MSDE instance to Mixed Mode to allow you to connect with your new SQL login, so...
3) Change MSDE authentication mode from Windows Integrated to Mixed.
(The following is taken direct from MS KB285097)
--- To set this key to mixed-mode, follow these steps:
1. Stop SQL Server and all related services, such as SQLAgent, from the Services control panel. 2. Click Start, click Run, type regedt32, and then click OK. 3. Find the HKEY_LOCAL_MACHINE window on the local computer. 4. Navigate to the following registry key for the default instance: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer 5. Navigate to the following registry key for a named instance: HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode 6. On the right-hand pane, find the value LoginMode and double-click it. 7. In the DWORD Editor window, set the value to 0 or 2. Also, make sure that the Hex radio button is selected. 8. Click OK.
Restart SQL Server and SQL Server Agent services for this change to take effect.
Note When you change the default login authentication mode from Windows NT authentication to SQL authentication, we recommend that you change the sa password to a strong password. ---
So that was an interesting day in the office! Annoyingly after all that and many hours of fiddling I still manage to repair the problem myself. I was able to repair it with a 3rd party recovery tool, but it would have been so much more satisfying to have managed it myself.
Something occured to me the other day... the collective wisdom of the internet isn't always a good thing, especially when dealing with something that has many people confused!
I had a moment of self doubt recently, brought on by the need to explain SQL backups to someone. I wanted to make sure that what I thought was correct really was, rather than simply soldier on and risk passing on a poor understanding to an unknowing recipient. What I found though were a lot of people who think they understand SQL backups, but who clearly don't!
So what is it they don't get you ask?
Well generally speaking the connection between different type of backup, and their effect on the transaction log. I saw people thinking that a full backup would cause old transaction log records to be cleared, and other people thinking that differential backups use the transaction log for its data... both of which are wrong. So to be clear...
Full Backups - Only the database (.mdf) itself is backed up*, the transaction log isn't touched, and it will continue to grow as always.
Differential Backups - Only the database (.mdf) itself is backed up, by backing up those extents within the database which are marked as having changed since the last Full backup. The transaction log isn't touched.
Transaction Log Backups - Finally the transaction log file is used, and the completed transactions marked as such so the space can be re-used / cleared, or more accurately, the inactive portion of the log is truncated.
So the critical thing here is that if you have a database in Full or Bulk-logged recovery mode, you must have a transaction log backup done at some point, even if you don't plan to use it. Otherwise the transaction log will never be truncated, and it will just continue to grow until it runs out of space. Or of course, consider simply changing the recover mode to simple.
* yes I know that the transactions made during the process of the backup are then backed up from the transaction log at the end, but lets not confuse matters here.
