how to copy system databases from one server to another server

I have installed a new sql 2008 R2 standart server and I will close the sql server 2008R2 Enterprise  and I would like to copy the master, model, and msdb database files of sql server 2008R2 Enterprise (both mdf and ldf), not moving from old server(Enterprise) to new server(standart) so how can I copy these system databases to new server?
TRocexAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy DownsOWNERCommented:
Try this
http://technet.microsoft.com/en-us/library/ms188664(v=sql.120).aspx

Limitations and Restrictions
The Copy Database Wizard cannot be used to copy or move the following databases.
System databases
Databases marked for replication.
Databases marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.

-------------

In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

From the Select a Source Server page, specify the server with the database to move or copy, and to enter login information. After you select the authentication method and enter login information, click Next to establish the connection to the source server. This connection remains open throughout the session.

Source server
Select the name of the server on which the database or databases you want to move or copy are located, or click the browse (...) button to locate the server you want. The server must be at least SQL Server 2005.

Use Windows Authentication
Allow a user to connect through a Microsoft Windows user account.

Use SQL Server Authentication
Allow a user to connect by providing a SQL Server Authentication user name and password.

User name
Enter the user name to connect with. This option is only available if you have selected to connect using SQL Server Authentication .

Password
Enter the password for the login. This option is only available if you have selected to connect using SQL Server Authentication.

Next
Connect to the server and validate the user. This process checks whether the user is a member of the sysadmin fixed server role on the selected computer.
0
TRocexAuthor Commented:
but this method is not working with system databases..I need to test system databases on the new server which are sql server 2008r2 standart so I have to copy them from old server to new server to see whether they work with new sql server or not and if everytying is ok, I will remove the sql server 2008R2 Enterprise and and I will install sql server 2008R2 standart and move the system databases to new server (my purpose is to downgrade)
0
Randy DownsOWNERCommented:
You can use backup/restore for system databases.

http://technet.microsoft.com/en-us/library/ms190190.aspx

To restore any database, the instance of SQL Server must be running. Startup of an instance of SQL Server requires that the master database is accessible and at least partly usable. If master becomes unusable, you can return the database to a usable state in either of the following ways:

Restore master from a current database backup.

If you can start the server instance, you should be able to restore master from a full database backup.

Rebuild master completely.

If severe damage to master prevents you from starting SQL Server, you must rebuild master. For more information, see Rebuild System Databases.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Scott PletcherSenior DBACommented:
Back them up on the existing server and restore them on the new server.

The servers should be exactly the same version, service packs and hot-fixes.  But Enterprise vs Standard should not matter -- you should be able to restore Enterprise master db to Standard master db without a problem.
0
TRocexAuthor Commented:
should I take stop the sql service when backuping the system databases(master,msdb)? I dont make sure that it will say whether they are in use  so you can not backuping am I right?

and then Should I make any changes on system databases after restoring? are they working immediately after restoring on standart edition? except for same version or service patch and hot-fixes
0
Scott PletcherSenior DBACommented:
No, you don't stop the SQL service to do system db backups -- in fact, the SQL service has to be running, since it is what is processing the BACKUP DATABASE command :-) .  You don't also don't need to "quiesce" the instance in any way.  Just run the backup at any time.


They should work immediately, IF they are the same version and service path and hot-fix level.  IF they are not, they likely won't work, ever, particularly master.  The system dbs really need to be the exact same version, etc., to do a system db restore.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.