Moving domains or accounts between servers is just another necessity of server ownership. It may be necessary to upgrade your operating system, consolidate or decentralize your domains, get onto a different platform type (dedicated or cloud), or refresh your hardware to newer specifications. WHM makes it easy to move all or a portion of your cPanel accounts from an existing cPanel server using a variety of easy-to-use tools. Continue reading “Migrating a cPanel Account”
Thank you for choosing Managed WordPress at Liquid Web! We hope this guide will help you get started in making the most of your experience with the Managed WordPress Portal. There are some great features in the portal, and we’ve worked hard to make sure site maintenance is a cinch. Continue reading “A Beginner’s Guide to Managed WordPress”
What if you have dozens of SQL databases and manually backing up/restoring each database is too time-consuming for your project? No problem! We can script out a method that will export and import all databases at once without needing manual intervention. For help with transferring SQL Logins and Stored Procedures & Views take a look at our MSSQL Migration with SSMS article.
1. Open SSMS (Microsoft SQL Server Management Studio) on the source server, log in to the SQL instance and open a New Query window. Run the following query:
SELECT name FROM master.sys.databases
This command will output a list of all MSSQL databases on your server. To copy this list out, click anywhere in the results and use the keyboard shortcut CTRL+A (Command + A for Mac users) to select all databases. After highlighting all the databases right click and select copy.
2. Open Notepad, paste in your results and delete all databases (in the newly copied notepad text) you do NOT wish to migrate, as well as deleting the following entries:
These entries are the system’s databases, and copying them is not necessary. Make sure to delete everything except explicitly the databases you need to migrate. You should now have a list of all required databases separated by a line. i.e.
3. Save this result on the computer as C:\databases.txt.
4. Create a new Notepad window, copy/paste the following into the document and save it as C:\db-backup.bat
for /F "tokens=*" %%a in (databases.txt) do ( sqlcmd.exe -Slocalhost -Q"BACKUP DATABASE %%a TO DISK ='%systemdrive%\dbbackups\%%a.bak' WITH STATS" )
5. Now that you’ve saved the file as C:\db-backup.bat, navigate to the Start menu and type cmd and right click on Command Prompt to select Run as Administrator.Type the following command:
And hit enter. Afterward, type db-backup.bat and hit enter once again.
At this point, your databases have begun exporting and you will see the percentage progress of each databases export (pictured below).
Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that may have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error by modifying the existing C:\databases.txt file to include only the failed databases and re-run db-backup.bat until all databases are successfully exported.
By now you have the folder C:\dbbackups\ that contains .bak files for each database you want to migrate. You will need to copy the folder and your C:\databases.txt file to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. The folder on the C drive of the destination server should be called C:\dbbackups . It’s important to accurately name the file as our script will be looking for the .bak files here. Be sure that the destination server has your C:\databases.txt file as well, as our script will be looking for the database names here.
1. Open a Notepad and copy/paste the following into the document and save it as C:\db-restore.bat
for /F "tokens=*" %%a in (C:\databases.txt) do (
sqlcmd.exe -E -Slocalhost -Q"RESTORE DATABASE %%a FROM DISK='%systemdrive%\dbbackups\%%a.bak' WITH RECOVERY"
2. Save the file as C:\db-restore.bat
3. Navigate to the Start menu and type cmd.
4. Right click on Command Prompt and select Run as Administrator. Type the following command:
and hit Enter. Now type db-restore.bat and hit Enter.
Your databases have now begun importing. You will see the percentage of each databases restoration and the message “RESTORE DATABASE successfully processed” for each database that has been successfully processed.
Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error (you can change the batch file as necessary), modify C:\databases.txt to include only the failed databases and re-run db-restore.bat until all databases are successfully exported.
Congratulations, you have now backed up and restored all of your databases to the new server. If you have any login issues while testing the SQL connections on the destination server, refer to the Migrating Microsoft SQL Logins (anchor link) section of this article and follow the steps therein. To migrate views or stored procedures please refer to the Migrating Views and Stored Procedures section. Every SQL server will have it’s own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.
Migrating MSSQL between servers can be challenging without the proper guidelines to keep you on track. In this article, I will be outlining the various ways to migrate Microsoft SQL Server databases between servers or instances. Whether you need to move a single database, many databases, logins or stored procedures and views we have you covered!
There are many circumstances where you will need to move a database or restore databases. The most common reasons are:
- Moving to an entirely new server.
- Moving to a different instance of SQL.
- Creating a development server or going live to a production server.
- Restoring databases from a backup.
There are two main ways to move SQL databases. Manually with Microsoft SQL Server Management Studio (SSMS) or with the command line. The method you choose depends on what you need to accomplish. If you are moving a single database or just a few, manually backing up and restoring the databases with SSMS will be the easiest approach. If you are moving a lot of databases (think more than 10) then using the command line method will speed up the process. The command line method takes more prep work beforehand, but if you are transferring dozens of databases, then it is well worth the time spent configuring the script instead of migrating each database individually. If you aren’t sure which method to use, try the manual approach first while you get comfortable with the process. I recommend reading all the way through for a deeper understanding of the methodology.
Useful References for Terminology
SSMS – An acronym for Microsoft SQL Server Management Studio.
Source Server – The server or instance you are moving databases from or off.
Destination Server – The server or instance you are moving databases to.
Moving SQL databases with the manual method can be very easy. It is the preferred process for transferring a few or smaller databases. To follow this part of the guide, you must have MSSQL, and Microsoft SQL Server Management Studio (SSMS) installed.
1. Begin by logging into the Source server (the server you are moving databases from or off of). You will want to open Microsoft SQL Server Management Studio by selecting Start > Microsoft SQL Server > Microsoft SQL Server Management Studio.
2.Log into the SQL server using Windows Authentication or SQL Authentication.
3. Expand the server(in our case SQL01), expand Databases, select the first database you want to move (pictured below).
4. Right click on your database and select Tasks then click Back Up.
5. From here you are now at the Back Up Database screen. You can choose a Backup Type such as Full or Differential, make sure the correct database is selected, and set the destination for the SQL backup. For our example, we can leave the Backup Type as Full.
6. Under Backup Type, check the box for “Copy-only backup.” If you are running DPM or another form of server backup, backing up without the Copy-Only flag will cause a break in the backup log chain.
7. You will see a location under Destination for the path of the new backup. Typically you will Remove this entry then Add a new one to select a folder that SQL has read/write access. Adding a new Backup Destination shows a path similar to the following:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\
This C:\ path is where your stored database backup is. Note this location for later reference, as this is the default path to stored backups and will have to have proper read/write access for SQL services.
8. Next, append a filename to the end of this path such as AdventureWorks2012-081418.bak – Be sure to end the filename with the extension .bak and select OK
10. Once you have pressed OK on the Select Backup Destination prompt, you are ready to back up the database! All you need to do now is hit OK, and the database will begin backing up. You will see a progress bar in the bottom left-hand corner, and when the backup is complete, a window will appear saying ‘The backup of database ‘AdventureWorks2012’ completed successfully.‘
Navigate to the destination path, noted earlier, (in this case C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\) you will see your newly created file (in this case AdventureWorks2012-081418.bak) – Congratulations! This file is the full export of your database and is ready to be imported to the new server. If you have more databases, then repeat the steps above for each database you are moving. After copying all database process to the next step of restoring databases to the destination server.
You should now have a .bak file of all your databases on the source server. These database files need to be transferred to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. After copying a database you can store it on your destination server, for our example, we have stored it on the C drive in a folder named C:\dbbackups .
1. Open Microsoft SQL Server Management Studio.
2. Log in to the SQL server using Windows Authentication or SQL Authentication.
3. Expand the server and right click on Databases and select Restore Database.
4. The Restore Database screen looks very similar to the Back Up Database screen.Under Source, you will want to select Device instead of Database. Selecting Device allows you to restore directly from a file. Once you’ve chosen Device, click the browse icon […]
5. Select Add, then navigate to the folder in which your .bak files lives. (In this case, C:\dbbackups).
6. Select the first database .bak you would like to restore and click OK.
7. Click OK and now you are ready to import the database. Before importing, let’s take a look at the Options section on the left-hand side. Under Options, you will see other configurations for restoring databases such as Overwrite the Existing Database, Preserve the Replication Settings and Restrict Access to the Restored Database. In this case, we are not replacing an existing database so I will leave all these options unchecked. If you wanted to replace an existing database (for example, the backed up database has newer data than on the destination server or you are replacing a development or production database) then simply select Overwrite the Existing Database.
8. Clicking OK begins the restore process as indicated by the popup window that reads ‘Database ‘AdventureWorks2012′ restored successfully.’ You have migrated your database from the source to the destination server.
Repeat this process for each database that you are migrating. You can then update path references in your scripts/application to point to the new server, verify that the migration was successful.
After importing your databases if you are unable to connect using your SQL login, you may receive the error ‘Login failed for user ‘example.’ (Microsoft SQL Server, Error: 18456).‘ Because the database is in the Traditional Login and User Model, logins are stored separately in the source server and credentials are not contained within the database itself. From this point on, the destination server can be configured to use the Contained Database User Model which keeps the logins in your database and out of the source server. (You can read more about this here.)Until then, we will have to move and interact with the users as part of the Traditional model. Continue below to proceed with the migration of your SQL users.
Backing up and restoring the databases did move your SQL logins relation to the databases (your logins are still associated with the correct databases with the correct permissions) but the actual logins itself did not transfer to the new server. You can verify this by opening SSMS on the destination server and navigating to Server > Security > Logins. You will notice that any custom SQL logins you created on the previous server did not transfer over here, but if you go to Server > Databases > Your Database (AdventureWorks2012 in this case) > Security > Users you’ll see the correct login associated with the database.
If you have one or two SQL users, you can just delete the user’s association to the database in Servers > Databases > AdventureWorks2012 > Security > Users, re-create the user in Server > Security > Logins and map it to the proper database.
If you have a lot of logins, you will have to follow an additional process outlined below. To migrate all SQL users, open a New Query window on the source server and run the following script:
This script creates two stored procedures in the source database which helps with migrating these logins. Open a New Query window and run the following:
This query outputs a script that creates new logins for the destination server. Copy the output of this query and save it for later. You will need to run this on the destination server.
Once you’ve copied the output of this query, login to SSMS on the destination server and open a New Query window. Paste the contents from the previous script (it should have a series of lines that look similar to — Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]) and hit Execute.
You have now successfully imported all SQL logins and can now verify that the databases have been migrated to the destination server by using your previous credentials.
Views and stored procedures will migrate with the database if you are using the typical SQL Tape backups. Follow the instructions below if you need to migrate views and stored procedures independently.
- Open Microsoft SQL Management Studio on the Source server.
- Log in to your SQL server.
- Expand the server and as well as Databases.
- Right click on the name of your database and go to Tasks > Generate Scripts.
- Click Next.
- We will change Script entire database and all database objects to Select specific database objects and only check Views and Stored Procedures.
- Click Next, notice the Save to File option. Take note of the file path listed. In my case, it is C:\Users\Administrator\Documents\script.sql – The path of saved views and stored procedures.
- Click Next >> Next >>Finish, and select C:\Users\Administrator\Documents\script.sql and copy it to the destination server.
- Go to the destination server, open SSMS and log in to the SQL server.
- Go to File > Open > File or use the keyboard shortcut CTRL+O to open the SQL script. Select the file C:\Users\Administrator\Documents\script.sql to open it.
- You will see the script generated from the source server containing all views and stored procedures. Click Execute or use the keyboard shortcut F5 and run the script.
You have now migrated the views and stored procedures to your destination server! Repeat this process for each database you are migrating. A little guidance goes a long way in database administration. Every SQL server will have it’s own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.
Looking for a High Availability, platform-independent SQL service that is easily scalable and can grow with your business? Check out our SQL as a Service product offered at Liquid Web. Speak with one of our amazing Hosting Advisers to find the perfect solution for you!
Liquid Web is here to support your migration needs into our Managed WooCommerce Hosting platform. Whether you are migrating from an external or internal source, our in-house team of migration experts transforms the data migration process into a simple task. To ensure the smoothest and best possible data transfer, we have a quick overview and a few points for your consideration.
Our first step includes taking a copy of your live site (known as the origin site) and migrating it over to our Managed WooCommerce Hosting platform. Rest assured, when performing the migration, the only changes made to the site will be to assist in the movement. Within this timeframe, it is advised to avoid making changes or updates to the site as it will extend the migration timeline and could result in data loss. Changes and updates are included but not limited to themes, designs, contents, products, blog posts or WordPress versions. The initial sync process should result in no downtime for your live site. Once the initial sync is complete, our Migration Specialists perform a series of basic tests to the site. During this time, our team will send information on ways to test out your new site to ensure that all aspects have carried over correctly and are in working order. Before going live, it is essential to take the time to thoroughly review your site and if at any point you do find a discrepancy our specialist is there to assist. The third and most exciting step is the push to go live. We will coordinate the best date and time for the final sync of your site. This last sync will ensure the latest data on orders, products, and customers transfers to your new server. Upon completion of the final sync, you will be asked to update the staging domain’s name and DNS record. With a little DNS propagation time, you will begin to see the new site populate!
With the updating of DNS and the site name, you are now entirely done with the migration process. In subsequent steps, we will create a ticket with our Product Team to connect your store to our partnered applications, Glew and Jilt. Credentials to these valued applications will be sent out in an email, after which, our product team can suggest performance optimization methods to get the most out of your eCommerce store.
Knowing the details behind the migration process aligns us with our next step in creating a migration request from your Liquid Web control panel! Once completed, our Migration Specialists will be in touch to schedule the migration and answer any questions you may have.
8 Tips to a Smooth Migration
A recent Liquid Web survey revealed that businesses are often held back from choosing a better hosting partner by the “what-if” situation when a migration presents. Nearly a quarter of consumers who decide not to switch to a new provider cited fear of the migration as the biggest reason for maintaining the status quo. Even if they believe that the new hosting provider would be better. Continue reading “The 8 Step Checklist to a Better Migration”
If you ever need to upgrade the hardware on your Windows server, Liquid Web’s Windows team is happy to help you through the migration process. While it is not the most simple process, keeping in communication with our Heroic Support teams will help make things go smoothly. Before beginning a Windows server migration it’s a good idea to familiarize yourself with the overall process so you know what to expect.
Things to Consider
There are a few things to keep in mind when starting a migration. Review the items listed here to make sure you’re prepared for the process. Continue reading “What to expect for a Windows Server Migration?”
Common Site Migration FAQ
Is There a Cost?
All Linux migrations which are able to be performed by our internal migration team are included at no cost with your Fully Managed support. Fully supported migrations include cPanel to cPanel or Plesk to Plesk migrations with root access on both servers.
Some other types of migrations fall under our “beyond scope support” umbrella and may have a more limited scope, meaning we may not have access to or be able to collect certain information related to your site. We will discuss with you the breadth of migration we are able to offer with your request, based on the specific circumstances and any special considerations related to your servers.
In all instances, if you would like additional information for your particular case, please open a support request, and we would be happy to discuss your migration.
How Long Does it Take?
Most migrations can be completed in a few days, but every migration is different and there are a number of factors that can either add to or reduce the amount of time involved. Among them:
- Server size and disk usage: The data on the affected account(s) must be copied over at the beginning of the process and later synced after you have had a chance to test the sites. Obviously, a small partition that’s only partially utilized could require only a few minutes to duplicate, while copying a full, extremely large partition could take a number of hours.
- Your availability: Once the data is initially copied over, you will need to edit your local computer’s hosts file and test the sites on the new server. We will respond to any issues you report, and work with you to resolve them as quickly as possible. There is no time limit on this phase, and the pace is entirely up to you. We will not proceed until you have signed off on the sites as they exist on the new server, and once you have, we then will schedule a final sync if possible and schedule the DNS switchover at a time of your choosing.
- DNS propagation: We do lower the Time To Live (TTL) on the sites early in the migration process (assuming we have access to the DNS records) in an effort to speed up propagation, and typically sites will be visible on the new server within a few hours, but it still can take up to 24-48 hours for DNS changes to fully propagate globally.
As you might expect, most support requests on managed cPanel servers fall into a few basic categories. What you might be surprised to discover is that many common problems can be resolved by following a few simple steps.
None of the common cPanel support requests listed here are server-critical issues that require an experienced system administrator to troubleshoot and resolve, and we recognize that many of our customers are curious about their servers and actively engaged in learning more about cPanel server administration. Continue reading “Most Common Support Requests”
If you ever need to upgrade the hardware on your Windows server with the Plesk interface, Liquid Web’s Windows team is happy to help you through the migration. While it is not the most simple process you will ever be a part of, keeping in communication with our Heroic Support will make things go smoothly.