restore database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

restore database

Author

Topic

andrija11
Starting Member

2 Posts

Posted - 2007-01-17 : 04:49:26

Hi all,

during my development I have to restore database more then few times, so I'm using script to do it... but, I always have to delete user, then add its access to database again from 'Login' node, because of the restore before.
Does anyone have some quick script to delete user from database, and add it again with roles 'public' and 'db_owner'?

thx

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-17 : 08:48:01

see
sp_dropuser
sp_adduser
sp_addrolemember

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

andrija11
Starting Member

2 Posts

Posted - 2007-01-17 : 10:38:25

THX!

whole script looks like this now:

use master
go

DECLARE @dbid int, @spid int, @execstr varchar(15)

--Getting the database_id for the specified database
SET @dbid = DB_ID('MyDatabase')

--Get the lowest spid
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid)

WHILE @spid IS NOT NULL
BEGIN
IF @spid <> @@SPID --To avoid the KILL attempt on own connection
BEGIN
SET @execstr = 'KILL ' + LTRIM(STR(@spid))
EXEC(@execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
END

RESTORE DATABASE [MyDatabase] FROM DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDatabase.bak' WITH FILE = 1, NOUNLOAD, STATS = 10

PRINT 'Database restored at : ' + CAST(GETDATE() AS VARCHAR(20))

GO

print 'restore user'

go

use MyDatabase
go
sp_dropuser 'mydbuser'
go
sp_adduser 'mydbuser'
go
sp_addrolemember 'db_owner', 'mydbuser'

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-17 : 15:56:05

Also have a look at sp_change_users_login

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources