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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Related Posts