Database SQL SERVER

SQL Databases Migration with Command Line

SQL Databases Migration with Command Line

What if you’ve got dozens of SQL information’s and manually backing up/restoring every database is just too long for your project? No problem! we are able to script out a way which will export and import all databases quickly without having manual intervention. For facilitate with transferring SQL Logins and keep Procedures & Views take a glance at our MSSQL Migration with SSMS article

Backing Up Databases on the Source Server

Step 1: Open SSMS (Microsoft SQL Server Management Studio) on the source server, log in to the SQL instance. Open a New Query window. Run the following query:

SELECT name FROM master.sys.databases

This command can output a listing of all MSSQL databases on your server. To repeat this list out, click anyplace within the results and use the keyboard road CTRL+A (Command + A for waterproof users) to pick out all databases. When lightness all the databases right click and choose copy

Step 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:

  • master
  • tempdb
  • model
  • msdb

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

Step 3: Save this result on the computer as C:\databases.txt.

Step 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" )

Step 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:

cd C:\

And hit enter. Afterward, type db-backup.bat and hit enter once again.

See also  Queue in Data Structure

At this point, your databases have begun exporting and you will see the percentage progress of each databases export (pictured below).

sql-adminstrator-command-prompt

Take note of any unsuccessful databases, as you’ll re-run the batch file once it’s done, victimization solely the databases which will have unsuccessful. If the databases are failing to keep a copy, observe of the error message displayed within the prompt, address the error by modifying the present C:\databases.txt file to incorporate solely the unsuccessful databases and re-run db-backup.bat till all databases are with success exported.

Restoring Databases to the Destination Server

By currently you’ve got the folder C:\dbbackups\ that contains .bak files for every info you would like to migrate. You’ll ought to copy the folder and your C:\databases.txt file to the destination server. There are varied ways in which to maneuver your information to the destination server; you’ll use USB, Robocopy or FTP. The folder on the C drive of the destination server ought to be known as C:\dbbackups . It’s necessary to accurately name the file as our script are searching for the .bak files here. Make sure that the destination server has your C:\databases.txt file still, as our script are searching for the info 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"
)
  1. Save the file as C:\db-restore.bat
  2. Navigate to the Start menu and type cmd.
  3. Right click on Command Prompt and select Run as Administrator. Type the following command:
cd C:\

Your databases have currently begun importation. you may see the share of every info’s restoration and also the message “RESTORE info with success processed” for every database has been with success processed. Take note of any failing databases, as you’ll re-run the batch file once it’s done, victimization solely the databases that have failing.

If the databases square measure failing to keep a copy, observe of the error message displayed within the electronic communication, address the error (you will modification the batch file as necessary), modify C:\databases.txt to incorporate solely the failing databases and run db-restore.bat till all databases square measure success exported.

See also  [ Solved 100% Working] SNMP service security tab is missing - Windows Server 2012 R2 - DC

Conclusion:

Congratulations, you’ve got currently secured and remodeled all of your databases to the new server. If you’ve got any login problems whereas testing the SQL connections on the destination server, sit down with the Migrating Microsoft SQL Logins (anchor link) section of this text and follow the steps in that. To migrate views or keep procedures please sit down with the Migrating Views and keep Procedures section. Each SQL server can have its own configurations and obstacles to face however we have a tendency to hope this text has given you a powerful foundation for your Microsoft SQL Server Migration.

About the author

author

Wikitechy Founder, Author, International Speaker, and Job Consultant. My role as the CEO of Wikitechy, I help businesses build their next generation digital platforms and help with their product innovation and growth strategy. I'm a frequent speaker at tech conferences and events.

Add Comment

Click here to post a comment