October 9, 2007 at 11:29 am
I am using SQL Server 2005. I have two instance running (default and named). I have a backup file (Data.DAT) that I want to restore into Named instance. Is there anyways I can use it. Please suggest.
Additionally, I want to move all the existing databases from default instance to named instance. Can you provide me the script to do it. Can I remove default instance once all the database moved to named instance. Please advise.
October 9, 2007 at 3:36 pm
Restore a backup in a named instance the same way you would in a default instance: either right-click on "Databases" in Management Studio and select "Restore Database..." or use T-SQL Restore Database in a query window. I'm assuming that Data.DAT really is a backup file, created by backing up a SQL Server database.
I prefer to move database between instances by using Backup and Restore. Other options are the Copy Database Wizard and attach/detach wizard. See for information about the various methods.
Greg
Greg
October 9, 2007 at 3:42 pm
Use the above advice to restore the db.
You could remove, or uninstall, the default instance, but you might run into issues. They should be separate, but you never know. If you don't need it, turn off the services that are running for the default instance. Look in Books Online for the default service names.
October 9, 2007 at 3:56 pm
Thansk for your reply Greg. Yes, Data.DAT is a SQL back up file. Acutally, I used to have only default instance but due to business requirement, I will be creating a named instance and then restore the SQL backup into the named instance. I wants to do it via T-SQL script becuase this script will the be part of applicaiton installations. So my quesitons is how to specify in T-SQL to restore database in named instance only.
I will be using attach/detach to move database from default to named. Do you have any sample code that I can refer to.
October 10, 2007 at 10:54 am
As I said, open a SSMS query window for the named instance and run this
(substituting the name or your database and files):
RESTORE DATABASE MyDatabase
FROM DISK = '[backup file]'
WITH RECOVERY,
MOVE 'MyDatabase_data' TO 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase_data.mdf',
MOVE 'MyDatabase_log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase_log.ldf'
There are good examples of attach and detach in BooksOnLine. Search for sp_detach_db and sp_attach_db.
Greg
Greg
October 10, 2007 at 1:07 pm
Syed,
After reading your other thread in this forum, I think I understand what want to do. Do you want to put the RESTORE commands (which are T-SQL) in a batch script which will be run on a workstation?
If so, look up the sqlcmd utility in BooksOnLine. That explains the arguements to include to connect to a specific instance. You could also search for sqlcmd or osql (this is what sqlcmd replaces) on this site for specific examples.
Greg
Greg
October 11, 2007 at 11:00 am
Greg,
That is exactly I am trying to do. Below is my sample script:
@echo off
REM - Set the DATAABSETEMP variable to the
SET DATABASEFOLDER=C:\My Documents\TIMS\Databases
SET DATABASETEMP=%DATABASEFOLDER%
echo Restoring EZSalesOrder Database
echo Restoring EZSalesOrder Database >> ezso_db_restore_output.txt
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql" -E -Q -S (local)\HES "RESTORE DATABASE EZSalesOrder FROM DISK = N'%DATABASETEMP%\EZSODB.DAT' WITH FILE = 1, NOUNLOAD, STATS = 10"
@echo on
But it is not working. It is not recoginizing -S switch with (local)\HES. Any thoughts??
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply