How to Migrate SQL Databases with Command Line. Today we want to experience working with SQL database via command line. I find it very enjoyable to run some queries to manipulate a table or create a database or view the information in a table via the command line. The purpose of this article is to provide a way to export and import all databases without the need for manual intervention.
Buy Windows VPS Hosting, Cheap Windows VPS
How to Back-Up Databases on the Source Server
1. In the first step, open SSMS (Microsoft SQL Server Management Studio) on the source server.
a. Log in to the SQL instance and open a New Query window.
b. Run the following query. This command will output a list of all MSSQL databases on your server.
SELECT name FROM master.sys.databasesc. You can click anywhere in the results and use the keyboard shortcut CTRL+A (Command + A for Mac users) to select all databases to copy this list out.
d. Right-click and select copy after highlighting all the databases.
2. Open the Notepad text editor.
a. 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:
master
tempdb
model
msdb
b. 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.
AdventureWorks2012
AdventureWorks2014
AdventureWorks2016
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
mkdir %systemdrive%\dbbackups for /F "tokens=*" %%a in (databases.txt) do ( sqlcmd.exe -Slocalhost -Q"BACKUP DATABASE %%a TO DISK ='%systemdrive%\dbbackups\%%a.bak' WITH STATS" )
5. After you save 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:
cd C:\
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 database export similar to the image 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.
How to Restore Databases to the Destination Server
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 many ways to move your data to the destination server. For example, 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.
How to Migrate SQL Databases with Command Line.
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 text editor again.
Then 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.
5.Type the following command and hit Enter:
cd C:\6. Type db-restore.bat and hit Enter.
Your databases have now begun importing.
Now you will see the percentage of each database’s 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.
Also, see:
Tutorial Install SQL Server 2019 Developer Edition on Windows Server
Dear user, we hope you would enjoy this tutorial, you can ask questions about this training in the comments section, or to solve other problems in the field of Eldernode training, refer to the Ask page section and raise your problem in it as soon as possible. Make time for other users and experts to answer your questions.
Goodluck.
