SATOHOST
No Result
View All Result
Thứ Hai, Tháng Một 25, 2021
  • Login
  • vps giá rẻ
  • cpanel nulled
  • bitcoin exchange script
  • Home 1
Subscribe
SATOHOST
  • vps giá rẻ
  • cpanel nulled
  • bitcoin exchange script
  • Home 1
No Result
View All Result
SATOHOST
No Result
View All Result
Home Blog

SQL Databases Migration with Command Line

admin by admin
Tháng Tám 12, 2019
in Blog
5 min read
0
152
SHARES
1.9k
VIEWS
Share on FacebookShare on Twitter

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.

Backing Up Databases on the Source Server

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:

  • 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

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. 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.

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.

 

Restoring Databases to the Destination Server

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:

cd C:

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 issu
es 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.

 

Tags: blogcliCommand-linedata managementdatabaseiisMicrosoftmigrationmigrationsmssqltransferwindows
admin

admin

Related Posts

Blog

LINUX BACKUP & RESTORE FULL OS

Tháng Mười Hai 16, 2020
Blog

VPS Ram 4GB Chỉ 199k/tháng Miễn Phí Gsuite,

Tháng Tám 29, 2020
Blog

Tài Khoản Google Drive Unlimited 2020 50k – Google Drive Không Giới Hạn – Google drive unlimited 2020

Tháng Bảy 11, 2020
Bán tài khoản MOVO CASH USA ACCOUNT (VCC+VBA)
Blog

Bán tài khoản MOVO CASH USA ACCOUNT (VCC+VBA)

Tháng Bảy 11, 2020
Blog

Hướng dẫn kích hoạt bản quyền Windows Server 2012 R2 không cần crack

Tháng Sáu 10, 2020
Hướng dẫn cài đặt và cấu hình Openstack toàn tập từ A-Z
Blog

Hướng dẫn cài đặt và cấu hình Openstack toàn tập từ A-Z

Tháng Sáu 4, 2020
Hướng dẫn Tự Tạo Ứng Dụng Họp Online Phòng Học Online Miễn Phí với Jitsi
Blog

Hướng dẫn Tự Tạo Ứng Dụng Họp Online Phòng Học Online Miễn Phí với Jitsi

Tháng Tư 13, 2020
Hướng dẫn sử dụng Remote Desktop để truy cập VPS
Blog

Hướng dẫn sử dụng Remote Desktop để truy cập VPS

Tháng Tư 12, 2020
Download Kali Linux 2020.1
Blog

Download Kali Linux 2020.1

Tháng Tư 12, 2020
vps giá rẻ
  • Trending
  • Comments
  • Latest
Hướng dẫn cài DirectAdmin Nulled mới nhất 2020

Hướng dẫn cài DirectAdmin Nulled mới nhất 2020

Tháng Sáu 10, 2020
Wowza Streaming Engine 4.7.7 Crack Linux/Windows

Wowza Streaming Engine 4.7.7 Crack Linux/Windows

Tháng Mười Một 15, 2020
Nén và giải nén trong linux centos 6 centos 7 nén zip, tar.gz và tar.bz2.

Khuyến mãi vps 50k/tháng SSD 50Gb

Tháng Sáu 15, 2019
Bán tài khoản MOVO CASH USA ACCOUNT (VCC+VBA)

Bán tài khoản MOVO CASH USA ACCOUNT (VCC+VBA)

Tháng Bảy 11, 2020

Cpanel Nulled Cpanel/whm Nulled on Centos VPS/Dedicated Servers

Tháng Mười 8, 2020
Hướng dẫn cài đặt và cấu hình Openstack toàn tập từ A-Z

Hướng dẫn cài đặt và cấu hình Openstack toàn tập từ A-Z

Tháng Sáu 4, 2020

LINUX BACKUP & RESTORE FULL OS

0

Milestones & Timeline

0

The Economics of Websites

0

The Landing Page Guide

0

The Next Big Thing

0

UX Design Is Easy

0

LINUX BACKUP & RESTORE FULL OS

Tháng Mười Hai 16, 2020

VPS Ram 4GB Chỉ 199k/tháng Miễn Phí Gsuite,

Tháng Tám 29, 2020

Tài Khoản Google Drive Unlimited 2020 50k – Google Drive Không Giới Hạn – Google drive unlimited 2020

Tháng Bảy 11, 2020
Bán tài khoản MOVO CASH USA ACCOUNT (VCC+VBA)

Bán tài khoản MOVO CASH USA ACCOUNT (VCC+VBA)

Tháng Bảy 11, 2020

Hướng dẫn kích hoạt bản quyền Windows Server 2012 R2 không cần crack

Tháng Sáu 10, 2020
Hướng dẫn cài đặt và cấu hình Openstack toàn tập từ A-Z

Hướng dẫn cài đặt và cấu hình Openstack toàn tập từ A-Z

Tháng Sáu 4, 2020
  • vps giá rẻ
  • cpanel nulled
  • bitcoin exchange script
  • Home 1
Call us: 0975757375

© 2020

No Result
View All Result
  • vps giá rẻ
  • cpanel nulled
  • bitcoin exchange script
  • Home 1

© 2020

Welcome Back!

Login to your account below

Forgotten Password?

Create New Account!

Fill the forms bellow to register

All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In