Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Apr 27, 2016

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 objects\Backup Devices\new\Specify Backup DB Information
-- Backup
BACKUP DATABASE TestDataBase1
TO TestDataBase2 ;
-- Get Logical Names
RESTORE FILELISTONLY
FROM TestDataBase1 ;
-- Set single user access, otherwise 'In Use' error
alter database TestDataBase1
set single_user with rollback immediate
-- Restore
RESTORE DATABASE TestDataBase1 FROM DISK = 'C:\Backups\TestDataBase1.bak'
WITH MOVE 'TestDataBase2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.TEST\MSSQL\Backup\TestDataBase2.mdf',
MOVE 'TestDataBase2_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.TEST\MSSQL\Backup\TestDataBase2.ldf'
-- reset DB to MultiUser
alter database TestDataBase1
set multi_user
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date