[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Backing up and Restoring SQL 2008 DB

Posted on 2011-04-19
8
Medium Priority
?
921 Views
Last Modified: 2012-05-11
I am trying to do the following in a batch file:

1.  Backup an existing DB to disk
2.  Stop a particular IIS instance
3.  Take that backup, and overwrite an existing DB of a different name
4.  xcopy a directory to overwrite another directory
5.  Start the IIS instance again.

I was able to get #1 using this command:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
    -E -Q "BACKUP DATABASE OriginalDB TO DISK='C:\tmp\db.bak' WITH FORMAT"

#2 is Easy, skip that
#3 is really where I am stuck.  I tried using the following command:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
    -E -Q "RESTORE DATABASE NewDB FROM DISK='C:\tmp\db.bak' WITH REPLACE"

But it bombs out with the following errors:

The file 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OriginalDB.mdf' cannot be
overwritten.  It is being used by database 'OriginalDB'.
Msg 3156, Level 16, State 4, Server WEB01, Line 1
File 'OriginalDB_Data' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OriginalDB.mdf'. Use WITH MOVE to
identify a valid location for the file.
Msg 1834, Level 16, State 1, Server WEB01, Line 1
The file 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OriginalDB_log.ldf' cannot be
overwritten.  It is being used by database 'OriginalDB'.
Msg 3156, Level 16, State 4, Server WEB01, Line 1
File 'OriginalDB_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\OriginalDB_log.ldf'. Use WITH MOVE to
identify a valid location for the file.
Msg 3119, Level 16, State 1, Server WEB01, Line 1
Problems were identified while planning for the RESTORE statement. Previous
messages provide details.
Msg 3013, Level 16, State 1, Server WEB01, Line 1
RESTORE DATABASE is terminating abnormally.

I am ok doing this another way if someone has a better idea, but I need to be able to call it from a command line.

Thanks!
0
Comment
Question by:charvett
8 Comments
 
LVL 8

Expert Comment

by:dba2dba
ID: 35426012
You have to restore the database with MOVE option as the physical files with the same names are already being used by the existing database.

Below link has the information needed to do this:

http://remidian.com/mssql/restore-sql-server-database-with-tsql.html

Thanks,
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35426030
you have to "MOVE" the files to other names:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" 
    -E -Q "RESTORE DATABASE NewDB FROM DISK='C:\tmp\db.bak' WITH REPLACE, MOVE OriginalDB_Data TO  'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB.mdf'' ,  MOVE  OriginalDB_LOG TO  'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB_log.ldf' " 

Open in new window


I presume you see what is going on, there
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35426070
Try:
 
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
    -E -Q "RESTORE DATABASE NewDB FROM DISK='C:\tmp\db.bak' 
    WITH MOVE 'OriginalDB_Data'
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB.mdf',
    MOVE 'OriginalDB_Log' 
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDB_log.ldf"

Open in new window


You cannot use REPLACE option restoring to new database, it works only for original database from which backeup was taken.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35426080
Oh, sorry I didn't spot your answer angelIII.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35426111
WITH REPLACE will work also if the newDB does not exyists and/or the restore is not "from the same db".
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 1000 total points
ID: 35426159
Yes, maybe I made myself not clear. In this particular case it won't work.When database doesn't exist REPLACE will be ignored - nothing to replace :)
When database exists you cannot replace existing database files for new database use. Sorry for misleading answer.
0
 
LVL 1

Author Comment

by:charvett
ID: 35467578
Ok, so I am really close on this.  All works the first time, but I am running this batch more than once.  So, here is what I have going on ...

Backup:
osql.exe -E -Q "BACKUP DATABASE Company_Web_Prod TO DISK='C:\tmp\company_web_prod.bak' WITH FORMAT"

Restore:
osql.exe -E -Q "RESTORE DATABASE Company_Web_LiveBackup FROM DISK='C:\tmp\holtcat_web_prod.bak' WITH MOVE 'Company_Web_Prod_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Company_Web_LiveBackup.mdf', MOVE 'Company_Web_prod_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Company_Web_LiveBackup.ldf'"

This seemed to work the first time, then after that, I get an error message when it tries to restore:

Msg 3234, Level 16, Sate 2, Server Server01, Line 1
Logical file 'Company_Web_Prod_data' is not part of database 'HoltCat_Web_LiveBackup'.  Use RESTORE FILELISTONLY to list the logical file names

?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35467609
you the logical names are only "guessed" from here, so you might try without "_Data", or better, run:

osql.exe -E -Q "RESTORE FILELISTONLY FROM DISK='C:\tmp\holtcat_web_prod.bak' "

to see what the output is in regards to the
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question