Able to back up and restore data from one DB to other DB by sqlcmd command

I am able to back up and restore data from one DB to other DB by sqlcmd command. But I need to restore data from .bak file to another DB which have DB name different. Servers are SQL and command I am using is SQLCMD command.
Vikrant KambojAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Use RESTORE command:
RESTORE [RestoredDatabaseName] FROM DISK = '...bak'

Open in new window

You can define any name for the restored database.
1
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The file names and locations of the original files (data and log) are kept with that simple command, which is unwanted most of the time. See the WITH clause of RESTORE in the online help for details.
0
Vikrant KambojAuthor Commented:
@ Máté Farkas- How i can address DB name and Server name. With out we cant transfer data.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Pawan KumarDatabase ExpertCommented:
Connect to your SQL Server. Open a new query window and execute below-

RESTORE DATABASE yourDBNAME FROM DISK = 'C:\BackupName.BAK'
GO
1
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you need to do that on a different server it is getting more complicated, because the MSSQL DBMS service usually runs under a protected account not having network access, and the DBMS server is the one having to execute backup and restore. The procedure hence looks similar to this:
sqlcmd -S server1 -E -Q "backup database db1 to disk='c:\temp\db1.backup' with copy_only, init"
copy \\server1\c$\temp\db1.backup \\server2\c$\temp\
sqlcmd -S server2 -E -Q "restore database db2 from disk='C:\temp\db1.backup' with move 'db1_data' to 'D:\MSSQL\Data\db2.mdf', 'db1_log' to 'D:\MSSQL\Log\db2.ldf'"

Open in new window

All names and locations are examples only, of course.
1
Pushpakumara MahagamageVPCommented:
Your question is not clear

1. Do you need to backup a Database1 and restore that backup to another server as Database2  

that is possible, if it is one time task that is ok.  but let me know your requirement, then i can suggest the best way to transfer data to one DB to other.
0
Pushpakumara MahagamageVPCommented:
One DB to another DB
0
Vikrant KambojAuthor Commented:
Hi Pushpakumara,

There are 2(G13(Prod) & G14(QA)) different SQL servers which have different DB names e.g Alexis and Bastian. I have taken back up of of Alexis in .bak file form and i am unable to restore in Bastian. Any help?
0
Pawan KumarDatabase ExpertCommented:
you need something like..

RESTORE DATABASE Bastian FROM DISK='c:\Alexis.bak'
WITH
   MOVE 'AlexisMDF' TO 'c:\Bastian.mdf',
   MOVE 'AlexisLDF' TO 'c:\Bastian_log.ldf'
1
Vikrant KambojAuthor Commented:
Hi Pawan...Thanks...Please let me know if its correct(Below). can i run this on CMD?

RESTORE DATABASE TestS15 FROM DISK='\\10.193.2.27\Production-DBDump-Automation\Test.BAK'
WITH
   MOVE 'TestMDF' TO '\\10.193.2.27\Production-DBDump-Automation\TestS15.mdf',
   MOVE 'TestLDF' TO '\\10.193.2.27\Production-DBDump-Automation\TestS15_log.ldf'
0
Pawan KumarDatabase ExpertCommented:
Take first backup of what we are going to overwrite.  After that we can run.
1
Vikrant KambojAuthor Commented:
I have taken that in form of Test.BAK file. Can we run on Command prompt?
0
Pawan KumarDatabase ExpertCommented:
you need to run that in ssms.
1
Vikrant KambojAuthor Commented:
Getting this below error.... I ran command on QA server.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'TestS15' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
Pawan KumarDatabase ExpertCommented:
1
Vikrant KambojAuthor Commented:
Executed this on QA Server
RESTORE DATABASE TestS15 FROM DISK='\\10.193.2.27\Production-DBDump-Automation\Test.BAK'
WITH MOVE 'TestMDF' TO '\\10.193.2.27\Production-DBDump-Automation\TestS15.mdf',
  MOVE 'TestLDF' TO '\\10.193.2.27\Production-DBDump-Automation\TestS15_log.ldf', REPLACE

Error
Msg 3234, Level 16, State 2, Line 1
Logical file 'TestMDF' is not part of database 'TestS15'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
Pawan KumarDatabase ExpertCommented:
Your filenames are incorrect ..run this..

RESTORE FILELISTONLY FROM DISK='\\10.193.2.27\Production-DBDump-Automation\Test.BAK'
1
Vikrant KambojAuthor Commented:
RESTORE FILELISTONLY TestS15 FROM DISK='\\10.193.2.27\Production-DBDump-Automation\Test.BAK'
WITH MOVE 'TestMDF' TO '\\10.193.2.27\Production-DBDump-Automation\TestS15.mdf',
  MOVE 'TestLDF' TO '\\10.193.2.27\Production-DBDump-Automation\TestS15_log.ldf'

Error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TestS15'.
0
Pawan KumarDatabase ExpertCommented:
Run this only.. ( do not add anything else )

RESTORE FILELISTONLY TestS15 FROM DISK='\\10.193.2.27\Production-DBDump-Automation\Test.BAK'
1
Vikrant KambojAuthor Commented:
Yo Bro............ I run this below Command.

RESTORE DATABASE TestS15 FROM DISK='\\10.193.2.27\Production-DBDump-Automation\Test.BAK' WITH REPLACE

It worked...You made my day....Thanks Bro!! Have a happy weekend!.:-).
0
Pawan KumarDatabase ExpertCommented:
welcome glad to help as always. :)
1
Pushpakumara MahagamageVPCommented:
You have done your task, I was stuck with some argent task so couldn't come to EE to help you.

 if it is a recursive task, [probably you may have to update QA DB frequently]

create a job to backup prod DB to network share on Prod DB Server
"BACKUP DATABASE [DBName] TO DISK = '\\QASERVER\DBBackup\backup.bak' WITH INIT"       -------INIT will overwrite the backup file.
and create another job with your restore statement to restore it to QA Server

then you can execute jobs when you need to update QA DB with Prod. even you can schedule those job to automate QA DB update.
1
Vikrant KambojAuthor Commented:
Getting this error :(.
RESTORE cannot process database 'DevDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Command Used
RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE
0
Pawan KumarDatabase ExpertCommented:
You need use like below as a single batch..

USE [Master]
GO
RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE
GO

Open in new window

1
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Or, if using sqlcmd as you told us, provide -d master as sqlcmd parameter to switch immediately to master db.
1
Vikrant KambojAuthor Commented:
USE DEVDB
GO
RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE
GO

Msg 3102, Level 16, State 1, Line 4
RESTORE cannot process database 'DevDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally
0
Pawan KumarDatabase ExpertCommented:
You need to use MASTER HERE. You cannot use the DB which you are restoring.

USE MASTER
GO
RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE
GO
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vikrant KambojAuthor Commented:
Yo Man Pawan!!...Thanks ;).
0
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help as always :)
0
Pushpakumara MahagamageVPCommented:
Drop existing QA DB before restoring
0
Pushpakumara MahagamageVPCommented:
ALTER DATABASE nortwindReport
 SET SINGLE_USER WITH
 ROLLBACK IMMEDIATE
DROP DATABASE nortwindReport
RESTORE DATABASE nortwindReport FROM DISK='D:\DMDBBackup\nortwind.bak'
 WITH MOVE 'nortwind' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\nortwindReport.MDF',
 MOVE 'nortwind_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\nortwindReport.LDF'
0
Pushpakumara MahagamageVPCommented:
Edit the Data File, Log File path and Execute following query @ SQL Server management studio,

ALTER DATABASE DevDB
 SET SINGLE_USER WITH
 ROLLBACK IMMEDIATE
DROP DATABASE DevDB
RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK'
 WITH MOVE 'DevDB' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\DevDB.MDF',
 MOVE 'DevDB_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\DevDB.LDF'



Note - Change "D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\" with your Data File path
1
Vikrant KambojAuthor Commented:
I need a write-back of the PROD data (database and assets) to the QA and INT server and needs to be fully automated.
I should be able to use to trigger it via Jenkins and  able to include it as a step into the QA deployment later on.
The solution should include the possibility to exclude specified tables. Also post processing scripts need to make sure that noting refers to the PROD system after write-back. Everything needs to refer to the own environment (QA or TEST).

Currently I  am going ahead with three jenkins jobs as below
DBSync_Prod_Data_Backup – Which will take back up of Production data into the tool server.
DBSync_INT_With_Prod_Data -  Which will first  back up the Int db before restoring the production data.
DBSync_QA_With_Prod_Data -  Which will first back up the QA DB before restoring the production data.

Restored it manually.....as mentioned.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You cannot exclude specific tables with a restore.

And you cannot extend the description of your task all the time. You've got the steps to restore a DB to another one using sqlcmd, and that is what this question is about.
0
Vikrant KambojAuthor Commented:
Via SQLCMD I am able to take back and restore the DB by .bak file from one of the shared location.
0
Vikrant KambojAuthor Commented:
No Issues @Qlemo...i am about to accomplish it. Thanks
0
Vikrant KambojAuthor Commented:
@Pawan - can we use this command in Jenkins.....
USE MASTER
GO
SQLCMD -S DB13 -U DevDBUser -P DB45 -Q "RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE"
GO
0
Pawan KumarDatabase ExpertCommented:
Hi Vikrant, Sorry I dont have idea about Jenkins.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
No. USE is T-SQL and has to run inside of a MSSQL session.
All you need to do is to run
SQLCMD -S DB13 -U DevDBUser -P DB45 -d master -Q "RESTORE DATABASE DevDB FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE" 

Open in new window

as I've already stated.
0
Vikrant KambojAuthor Commented:
Thats fine Qlemo.....It was not working on command prompt. Though it will work on MSSQL session.
0
Vikrant KambojAuthor Commented:
@ Pawan...I used this command....
RESTORE DATABASE Zooker FROM DISK='\\10.193.2.27\Production-DBDump-Automation\ProdDBBackup.BAK' WITH REPLACE

Msg 3101, Level 16, State 1, Server DB14, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server DB14, Line 1
RESTORE DATABASE is terminating abnormally.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to make sure the DB is not in use. E.g by running
ALTER DATABASE Zooker SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Open in new window

(as posted by Pushpakumara Mahagamage) prior to the restore.
0
Vikrant KambojAuthor Commented:
We have already stopped services of this server....and then we executed this command.....
0
Vikrant KambojAuthor Commented:
Msg 3110, Level 14, State 1, Line 3
User does not have permission to RESTORE database 'zooker'.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
 can i give permissions??
0
Pushpakumara MahagamageVPCommented:
Hey Vikrant.

Read following article. then you can understand the process. and then you can do the task

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases
1
Vikrant KambojAuthor Commented:
I tried this.....
GRANT CREATE DATABASE TO Zooker

Error
CREATE DATABASE permission can only be granted in the master database.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to switch to the master db first (again), then you should be able to grant privileges.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.