October 10, 2007 at 10:08 am
We are using SQL Server 2005 for our application which was running on SQL default instance. Now I have named instance created. I have a backup file (Data.DAT) that I want to restore it into named instance only. Is there anyways I can do it. Please suggest. Any sample T-SQL code is much appreciated.
Additionally, I want to move all the existing databases from default instance to named instance. Can you provide me a sample script that I can refer to? Can I remove default instance once all the database moved to named instance
Please help!!!
October 10, 2007 at 10:23 am
After connecting to your SQL Server Named Instance run the following T-SQL
Please replace the drive and location with exact the location of your files.
RESTORE DATABASE DATA[DatabaseName] FROM DISK = 'Drive:\Data.bak'
WITH MOVE 'MDFFileLogicalName' TO 'D:\DataFile.mdf',
MOVE 'LDFFileLogicalName' TO 'D:\DataFile.mdf'
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 10:24 am
Check this article from Microsoft:
October 10, 2007 at 10:36 am
Thanks for your quick reply Prasad.
How can I connect to Named instance using T-SQL. Is there any parameter I will have to use inside T-SQL that will connect SQL Named Instance??
October 10, 2007 at 10:45 am
You can do it using sql query analyzer using the connection dialog option if you have credentials for the named instance database. You can also use New Query icon on the Management Studio tool bar and it will prompt you to select database engine, Server Name : Please mention your named instance and give the user name and password credentials.
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 11:04 am
I want to do it inside T-SQL script and then this script will run on user's machine. We have various users uses the application and every user have its own SQL databases. So for new users, the script needs to run and restore the database only in the named instance. What switch or command I will have to add in the script to connect to Named instance and restore the database into it.
October 10, 2007 at 1:43 pm
Yes, you can do it using sqlcmd.
Inside the script, you use the following syntax (an example from setting up database mirroring using such a script)
:SETVAR PrincipalServer (local)\SQLDev01
:SETVAR MirrorServer (local)\SQLDev02
:SETVAR WitnessServer (local)\SQLExpress
:SETVAR Database2Mirror TicketSalesDB
go
:ON ERROR EXIT
go
:CONNECT $(PrincipalServer)
-- do some stuff
:CONNECT $(MirrorServer)
-- do some more stuff
:CONNECT $(PrincipalServer)
-- back to the other server again
Get the idea?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 11, 2007 at 3:50 am
The Easy way to move you databases would be to deattach the datases and re- attach
just remember to note the location of your mdf file
Thanks
October 11, 2007 at 11:02 am
Below is my 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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply