Solved

SQL2008 New Database

Posted on 2013-01-04
34
483 Views
Last Modified: 2013-01-07
SQL2008 - Fresh Install

I have a current SQL2008 server on a Windows 2003 Sever.
I have now installoed a new SQL2008 Server on a Windows Server 2008.

My plan is to move several of the old server SQL Database from the old to the new.

I have opened up my "MS SQL Server Managment Studio" and it has asked me to login in.

There is currently no SQL Server Name and in the drop down there is just local and nothing else.

SQL1
Do I just need to create a new one and if so how?
0
Comment
Question by:Bransby-IT
  • 19
  • 14
34 Comments
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743400
When I say do I, I want to create a New Database Engine and place it in the drive I created for this.
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743402
if you are running sql management studio on the same machine of the db, just click on server name, then browse for more, and local server.

If you have choosen mixed authentication can authenticate with windows authentication
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743406
You have to connect to the sql server engine, so you have to enter the machine where the sql server engine has been installed and the name of instance of the sql server.

is sql server 2008 express or the  sql server 2008 ?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743418
OK but it has not setup a new Server Engine.   I thought the install would of.

So on the drop down there is just local, and this is on the C Drive I want a new Engine named "SQL_XXX01" stored on my D:\SQL2008_DATA" which is what I setup on the install.
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743423
The Server is SQL 2008 Standard SP3
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743426
The DB will be on the same Server as the Studio Manager.
I just want to create a New Server Engine instance.
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743437
Just to understand ... you need 2 instances of sql server or just need to move db from C: to D: to save space on C: ?

2 instances of sql server uses twice ram ... twice system resources ... etc

are you really sure that you want this ?
0
 
LVL 15

Accepted Solution

by:
Anuj earned 100 total points
ID: 38743441
Before moving the database, Run SQL Server upgrade Adviser 2008 on your old server to check the compatibility issues, this gives you a report about potential issues after migration, Once you are good to start, then...

When you installed SQL Server 2008, it must have asked you to specify password for "sa" account. Type the server name there, or browse the server name and type sa password in password box.

Once you connected, If the MDF and LDF (they are database files) of your old SQL Server Instance are located remote location then copy them to the new server. If the SQL service is running on your Old server system will not allow you to move the files, so stop the SQL Server service of your old server and move. Once you moved use attach method to attach the database to your new server
http://msdn.microsoft.com/en-us/library/ms190209.aspx
http://support.microsoft.com/kb/224071

Once you attached all your user database, you need to recreate\transfer logins, Jobs and other SQL Server objects from your old server to new server. Also, There are few maintenance task to be performed after moving your databases like DBCC CHECKDB, Index defragment, UPDATE STATISTICS etc.
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743458
Sognoct

No I had 1 SQL Server which has 1 "SQL Server Engine" this has around 20 Database on it.

Now I have a second SQL Server

I want to keep the first SQL Server for some time, keep it running along side the new one.

After I have created the SQL Engine on the new server my plan is to move a couple of the database from the old to the new server.   Not all of them.

So I will end up with 2 x SQL Servers running 1 SQL Engine on each server.
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743462
Anujnb - I can move one database at a time yes?
0
 
LVL 9

Assisted Solution

by:sognoct
sognoct earned 400 total points
ID: 38743465
Bransby-IT yes you can move one a time , but as anujnb told first stop the old sql server

or detach the database that you want to move before copying it (in this case you don't have to stop service)
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743475
I will be detaching one at a time.
Got that bit just not to sure of the initial SQL Engine?
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743486
if it is on the same machine (so you followed the standard sql server installation)

click server name -> browse for more -> database engine -> "server\instancename"
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743493
The Browse for Servers is empty?

sql
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743495
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743497
open
control panel->administrative tools->services
check if service
SQL server is installed and running ...
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743501
Yep running.
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743502
Is there a way I can create a new server engine and place it in the data folder I setup on the install?
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743503
put in servername:localhost\SQL_XXX01
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743511
OK this has created the Database Engine but the location is on the C Drive.   How can I now move this to the D drive?

Thanks for your assistance.
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743512
for having the same data folder and engine folder douring install you have to choose
"data root directory" equal to "user database directory"  ... but now you have to uninstall and re-install ... .. ....

if it is essential ... do it, but if you think it is not to crucial for you, it will work great also with different folders. Engine does not eat space, and if you format C: and reinstall everything you have to install engine the same, also if it is on the D: drive
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743519
please see screenshots of my install.

SQL
and

SQL
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743524
OK was it the "Data root directory" I should of changed too?

When I move the database will they go into my D drive?

What would you do?
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743525
set instance root directory to "D:\SQL2008_DATA\"
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743529
OK as this is a long term server should I uninstall and reinstall?
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743531
also data root directory to d:\sq2008_data
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743533
Or does this not really matter?   I would like to set it up correctly.
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743537
first uninstall then reinstall everything ... then if the instance name is the same just have to put in servername:localhost\SQL_XXX01
for accessing the db

in the other machine detach db and copy them one by one on the
 d:\sq2008_data

then attach them
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743545
no, it does not matter ... as much as I know ...
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743546
Thanks for the  advise, so in a SQL new installs would it always be better to set the both so the database enginee and the database are located in the same directory?
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38743562
according to me no ... (that's why I always asked why you need them on the same dir)

it does not matter. This are the best configurations according to microsoft for sql 2008 :

C:\      Engine, Windows, Backups, Batch Processing, Full Text Catalogs
D:\      System Databases (TempDB), Transaction Logs
E:\      Large User Defined Database

I've read somewhere that there were better performances using dbf file and ldf file on different drives so I use ldf in c:\database and mdf in d:\database ... but never noticed so much differences.
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743617
ldf? Logs?
0
 
LVL 3

Author Comment

by:Bransby-IT
ID: 38743622
System Database and Large Database?

Are we talking like BES as System and inhouse ones as user?

Thanks for your advise its been great.
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38744014
by  System Databases it means :
master
model
msdb
tempd

they are the databases of the engine

Large database are the ldf / mdf files
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 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