로컬서버에 backup받았던 .bak파일로..
다른 서버에서 복원하는 방법은.
1. 똑같은 디비명으로 디비를 생성하고
2. 풀백업을 한후
3. 똑 같은 파일명으로 엎어치면 된다.
하지만 이때 문제가 발생하는데..
사용자명이 같아도 서버가 틀리면 다르게 인식해서..
로그인이 안되는 문제가 발생한다.
이때에는..
일단 소유자를 dbo로 바꾼후..
특정 소유자를 다시 만들어 그 디비의 테이블들을 그 소유자로 매칭시켜주면
된다.
그때 쓸수 있는 프로시저를 아래에 적었놓았다.
유용하게 쓰시길
/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
This stored procedure can be used to run through all of a specific
database's objects owned by the 'oldowner' and change the old
owner with the new one.
You should pass the old owner name and the new owner name,
as in the example below:
EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex'
*/
IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL DROP PROC ChangeAllObjOwner
GO
CREATE PROCEDURE ChangeAllObjOwner (
@oldowner sysname,
@newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON
--check that the @oldowner exists in the database
IF USER_ID(@oldowner) IS NULL
BEGIN
RAISERROR ('The @oldowner passed does not exist in the database', 16, 1)
RETURN
END
--check that the @newowner exists in the database
IF USER_ID(@newowner) IS NULL
BEGIN
RAISERROR ('The @newowner passed does not exist in the database', 16, 1)
RETURN
END
DECLARE owner_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE uid = USER_ID(@oldowner)
OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
SET @objname = @oldowner + '.' + @objname
EXEC sp_changeobjectowner @objname, @newowner
FETCH NEXT FROM owner_cursor INTO @objname
END
CLOSE owner_cursor
DEALLOCATE owner_cursor
GO
==========================================
쓰는 명령어는
EXEC ChangeAllObjOwner @oldowner = 'dbo', @newowner = 'kdemo'
날짜: 2003-07-25 12:11:29,
조회수: 3202 |