Backup and Restore all DB's question

[sqlcasey]

Hi,

We are building out 2 new instances of SQL Server 2005 on a test machine. I will be copying existing user databases from another test server and restoring them to the new instances.

Since I have never done it this way before, can I simply copy ALL db's, including master and msdb, so that the instances are identical to the existing test server's dbs? That way I don't have to script anything out - jobs, logins, etc.

Can someone please advise me of the correct order of restoration and anything else I may need to know about this process. I'm sure it's fairly straightforward, but I don't want to mess it up w/ lack of knowledge.

Thanks much

 

[ptheriault]

You don't want to restore your master database as that contains specific info about the server.
This is how I would do it.
1 install new instance.
2. run sp_help_revlogin (this will script out all users and include their password) on old server and run script on new server.) (delete logins that are not needed)
3. restore msdb This will restore all your jobs.
4. if you can detach the old db's from the target server just detach them and copy the mdf and ldf. Use the attach method on the new server. If not then copy the bak files and restore them.

Here is a copy of sp_help_revlogin. To run this you also need sp_hexadecimal.

USE master
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO



IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary varbinary (256)

DECLARE @PWD_string varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)



IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin

FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr



IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'

END

ELSE BEGIN -- SQL Server authentication

-- obtain password and sid

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT



-- obtain password policy state

SELECT @is_policy_checked =

CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

FROM sys.sql_logins WHERE name = @name

SELECT @is_expiration_checked =

CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

FROM sys.sql_logins WHERE name = @name



SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )

+ ' WITH PASSWORD = ' + @PWD_string

+ ' HASHED, SID = ' + @SID_string



IF ( @is_policy_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

END

IF ( @is_expiration_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

END

END



IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login has exists but does not have access

SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END



IF (@is_disabled = 1)

BEGIN -- login is disabled

SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

END



PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

END

CLOSE login_curs

DEALLOCATE login_curs



RETURN 0

GO

CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

GO




- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

Thanks paul, yes I am familiar with sp_help_revlogin. So I guess there's no way around this script? I guess so because login info resides in master.

As far as attaching and detaching db's, we can do this since it's just a test machine, but how does detaching the db affect the source server? We want to leave the db's on the source server intact and functional.

I guess what I need is the steps for attaching and detaching the dbs, source to target.

I'm sure it's simple, just never done it myself. I will look in BOL.

Thanks

 

[Catadmin]

When you detach a DB, it is no longer accessible by any applications, linked servers, etc. that are pointing to it. Once you reattach it (on the same server), things will work fine. This means if one of these DBs is a Production "must be up 24/7" db, you can't detach it without letting your users know.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

First run this command
sp_detach_db 'dbname'
Then copy the mdf and ldf files to your new server.
After your copy completes run the following command on both old and new server. obviously nobody can have access to the databases while they are detached.
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

another way to detach and attach databases it through EM.
Right click on the database and select tasks --> detach.
It will ask you if you want to update stats. Select no if this is a large db, it will take a long time to detach it.
You can attach the db the same way.
Right click on databases and select attach.
Make sure you have the correct db owner select.


- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

Thanks paul - that was the part i was missing - attaching back to *both* servers - makes sense