Simple SQL Server/MSDE Database Installation through osql

Here, a MSDE database is dropped, attached, and a user is given rights on the db
With minor changes to the osql parameters a Server name can be provided and this script will work for an SQL 2000 database.

–drop old db
osql -E -S -Q “DROP DATABASE [dbname]”

–copy the mdf to the target loc
copy “c:\installtemp\dbname*.?df” “C:\program Files\Microsoft SQL Server\MSSQL\Data”

–make sure the db file is not read only
attrib -r “C:\program Files\Microsoft SQL Server\MSSQL\Data\dbname*.?df”

–attach the db to the target instance/server
osql -E -S -Q “EXEC sp_attach_db @dbname = ‘dbname’, @filename1 = N’C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname_Log.LDF’, @filename2 = N’C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname_Data.MDF'”

use dbname
–add a user to access this db apart from default db admin user
EXEC sp_grantdbaccess “domain\user”, “domain\user”
GO

–grant read access to this user
exec sp_addrolemember N’db_datareader’, “domain\user”
GO

–grant write access to this user
exec sp_addrolemember N’db_datawriter’, “domain\user”
GO

–security script to make sure sps and fns have exec priv.
osql -E -S -Q -i “c:\installtemp\dbnameSecuritySetup.sql”

–Security script is below — dbnameSecuritySetup.sql
–recurively grant exec priv to all sps and fns in db.
USE dbname
DECLARE @sExecQry sysname
DECLARE EXEC_SPS CURSOR LOCAL FOR
select ‘grant exec on ‘ + QUOTENAME(name) + ‘ to “domain\user” ‘ from sysobjects where (type = ‘P’ or type=’FN’) and objectproperty(id,’IsMSShipped’)=0
OPEN EXEC_SPS
FETCH NEXT FROM EXEC_SPS INTO @sExecQry
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sExecQry)
–PRINT @sExecQry — debug only
FETCH NEXT FROM EXEC_SPS INTO @sExecQry
END
CLOSE EXEC_SPS
GO

–Perform checks on the database
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO

DBCC CHECKDB
GO

DBCC CHECKALLOC
GO

DBCC CONCURRENCYVIOLATION
GO

DBCC DROPCLEANBUFFERS
GO

DBCC FREEPROCCACHE
GO

DBCC UPDATEUSAGE(0)
GO

Advertisements

One Response to Simple SQL Server/MSDE Database Installation through osql

  1. Anonymous says:

    hi charles,
    this is madhusudan. thanks for this post. it was very useful for me in my project.

    regards,
    madhu

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: