Backup and Restore an SQL Database

Backup and Restore an SQL Database

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

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.

About Our Journalist