devxlogo

Backup and Restore an SQL Database

Why Trust Us

Sometimes when trying to backup and restore a database from another database SQL Server throws errors saying that the database is in use. To get around this quite frustrating error, follow these steps:

-- First Create New Backup device, By clicking server objectsBackup Devices
ewSpecify Backup DB Information-- BackupBACKUP DATABASE TestDataBase1TO TestDataBase2 ;-- Get Logical NamesRESTORE FILELISTONLYFROM TestDataBase1 ;-- Set single user access, otherwise 'In Use' erroralter database TestDataBase1set single_user with rollback immediate-- RestoreRESTORE DATABASE TestDataBase1 FROM DISK = 'C:BackupsTestDataBase1.bak'WITH MOVE 'TestDataBase2' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.TESTMSSQLBackupTestDataBase2.mdf',MOVE 'TestDataBase2_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.TESTMSSQLBackupTestDataBase2.ldf'-- reset DB to MultiUseralter database TestDataBase1set multi_user

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.

Disclosure

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

Show More