August 5, 2003 at 8:42 am
What is the easiest way to clone a database from one SQL server to another complete with logins and permissions, etc...
I have Win2K, SQL2K and I am tring to build a backup server to mirror its source.
Any info would greatly be appreciated.
SQLOK
August 5, 2003 at 9:08 am
Hi,
Add your backup as a linked server, then DTS the db across. Details in BOL. Or you can backup and restore, although you may find problems with logins this way.
Laters
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards
August 5, 2003 at 4:24 pm
Do you want to clone systems databases (master, msdb, etc) as well as all user databases? Or just one user database?
Cheers,
- Mark
Cheers,
- Mark
August 5, 2003 at 5:42 pm
Well I want to clone User as well as System database ? What do you suggest Mark ?
August 5, 2003 at 5:58 pm
1) install sql2k in corresponding drive/directory on backup server and bring to same service pack level as the live server (you've probably already done this)
2) backup master, msdb and all user database on live server, and transfer all backups to backup server
(the rest of these steps happen on the backup server)
3) disable sqlserveragent service - you maybe don't want sql agent running on backup server anyway
4) stop mssqlserver
5) start mssqlserver in single user mode (net start mssqlserver /m)
6) restore master database from the copied backup file (master is the only database that demands the server to be in single-user mode when a restore is attempted)
7) restart mssqlserver in multi-user mode
8) restore the rest of the databases from the copied backups
9) you'll notice on the backup server that SELECT @@SERVERNAME returns the same name as the live server. If you don't want this you can fix with sp_dropserver and sp_addserver, or a simple hack of the master..sysservers table.
Any questions or problems, just post them here.
Cheers,
- Mark
Cheers,
- Mark
August 6, 2003 at 6:23 am
Will this also take care of all permissions in databases; objects; views; stored procedures as well as logins?
Basically I am asking everything associated with a single database (clone) ?
1) install sql2k in corresponding drive/directory on backup server and bring to same service pack level as the live server (you've probably already done this)
2) backup master, msdb and all user database on live server, and transfer all backups to backup server
(the rest of these steps happen on the backup server)
3) disable sqlserveragent service - you maybe don't want sql agent running on backup server anyway
4) stop mssqlserver
5) start mssqlserver in single user mode (net start mssqlserver /m)
6) restore master database from the copied backup file (master is the only database that demands the server to be in single-user mode when a restore is attempted)
7) restart mssqlserver in multi-user mode
8) restore the rest of the databases from the copied backups
9) you'll notice on the backup server that SELECT @@SERVERNAME returns the same name as the live server. If you don't want this you can fix with sp_dropserver and sp_addserver, or a simple hack of the master..sysservers table.
Any questions or problems, just post them here.
Cheers,
- Mark
[/quote]
August 6, 2003 at 6:44 am
Look at Log shipping topic in BOL. This will sync your Main and backup servers continuously.
August 6, 2003 at 7:41 am
Question: I tried this using mssqlserver -c -m. The DOS information sais the server is running in single-user mode, but when I connected with Enterprise Manager, it still had the single-user grayed-out. How do you actually restore the Master DB?
regards,
August 6, 2003 at 8:17 pm
I rarely use EM to restore master. That's not to say it can't be done, but the command prompt process I follow is along the lines of:
net stop mssqlserver /y & net start mssqlserver /m & osql -E
1> restore database master from disk = 'c:\backups\master.bak'
2> go
Then you'll get a message along the lines of:
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
Then, again from the command prompt:
net start mssqlserver
Cheers,
- Mark
Cheers,
- Mark
August 7, 2003 at 6:39 am
Thanks, Mark. It seems to work. Now I can have the security and passwords back!!
regards,
August 7, 2003 at 3:26 pm
Restore a backup of the backup server's original master.
If you didn't realy want to clone the server, then a restore of master is not for you. You'll need to transfer selected data from live server's master to backup server's master.
Cheers,
- Mark
Cheers,
- Mark
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply