dcsimg
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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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