Create a New Sql Server from another
-
I create a New SQL2000 server with better hardware capacity.
I want to copy everything the same as the current SQL2000 server.
What is the best way to perform this?
Thank you in advance.
Johnny...
-
sp_detach_db, copy database files, and sp_attach_db
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
You can also use DTS or copy database wizard to transfer the logins and database.
-
The only problem with this... I have to disconnect the databases. Rigth?
It's another way?
Tks for reply
Johnny...
-
Another way is to do a full database backup on the source server, and a database restore on the target server. Remember to add all the logins, on the new target server, regardless of how you copy the databases. Although if you restore the master database on the new server, then you will not need to add all the logins. Hope this helps.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Thanks for you help Greg!!
This is my first time doing this, how can I perform a full database backup, because in the sql just let me to backup one at the time.
Johnny
-
One option would be to set up a maintenance plan. The other option would be to write a script that contains a series of "backup database" commands one for each database. See books online for syntax of "backup database".
Hope this helps.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Quick way as long as you have capcity for the files is to do
EXEC sp_MSForEachDB '
BACKUP DATABASE
TO DISK = ''D:\Backups\?Full.bak'' --Set you drive and path here.
WITH
INIT,
NAME = ''? Full Backup''
'
This will backup all databases including system databases if you don't want a particular DB(s) to be baced up add an if inside like so.
EXEC sp_MSForEachDB '
if ''?'' NOT IN (''master'',''model'',''msdb'')
BEGIN
BACKUP DATABASE
TO DISK = ''D:\Backups\?Full.bak'' --Set you drive and path here.
WITH
INIT,
NAME = ''? Full Backup''
END
'
This will prevent master, model and msdb DBs from being backed up.
If you need to move the master, model, and msdb databases there are several things that you need to do. See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071 for the most help.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply