Restoring or Creating Mapped Drive Sql Server Database

OK, so our staging environment has been eclipsed in drive space by our production database. This creates a problem, because the database is too big for the Staging server, so now we can't restore. SO, in the interim, I restored it to a mapped drive on a different server on our network. What? Isn't that going to cause problems? Isn't it going to be slow?
Well, maybe.. but our staging environment doesn't get a lot of hits, it's just internal. No outside access, so we figured it's OK for now while we are going to re-build the box soon anyway. DON'T do this for a production environment.
So, here is how you do it.
1. Get the drive mapped and visible in Explorer (my computer)

2. Ok, drive is visible to the user. Now, can Sql server see that drive? Probably not, most likely NOT. Here is how I got the drive visible to sql server...

a. first test to see if sql server can see the drive.. run this in query analyzer
exec master..xp_cmdshell "dir P:" (whatever your drive letter is)
You will probably get an error like this : "The system cannot find the path specified."

b. Turn on the drive visibility for Sql server. Run this..
exec master..xp_cmdshell "net use P: \\ServerName\Directory"

if things went right, you get this message:
"The command completed successfully." Now, move onto the next step.

if they didn't go right.. Then, well, check the documentation for the "net use" command, and make sure you are in accordance.
ALSO, make sure that you are running the command while you are ON the server where the mapped drive is to be USED from in other words, don't do it remotely.


c. Test Connection to mapped drive.
exec master..xp_cmdshell "dir P:"
Hopefully tested out just fine..

3. Restore database to mapped drive.
Congrats, you have now gotten you sql server able to see the mapped drive, now you need to restore your database to the drive. But you get this error (Or something similar when you try to restore):

5110 "File 'file_name' is on a network device not supported for database files."

This is expected. Microsoft by default, doesn't want you to restore a database (or even create for that matter) on a mapped drive, "Don't do it" that's what they say. By default, you cannot create a SQL Server database on a network file share.
So, with all the work you have done .. MSFT won't let you finish the job.. Sorry. Well, that was fun! Who loves wasting time? I know I do!
OK, stop crying. Here is your solution. You ever played with trace flags? I know I have :). If you haven't, I guess you can sort of think of them as ports on a firewall (it's a stretch) . They are either on or they're off. You have to turn them on, or they are on by default and you have to turn them off.
For this instance, you need to turn on trace flag 1807. Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. (Yes!)
OK, do this in Query Analyzer:

--BEGIN SCRIPT
use master
go

dbcc traceon(1807)

--END SCRIPT

Alright, now check to make sure that it's turned on:


--BEGIN SCRIPT

use master
go

dbcc tracestatus(1807)

--END SCRIPT

OK, the above should return a row that will show a status of "1". If the Status is "0". Then, maybe you somehow ran the first script incorrectly.

That's it.. You can now restore, or create your database as you normally would . Enjoy!

Changing in Mass the Default database for Users

Alright, this little quick and dirty script will mass assign all the users (with an exception of some listed below) to a specified default database. I ran this on SQL Server 2000 but it should work on SQL 2005.



--BEGIN SCRIPT--

declare @user varchar(30)

--create the cursor.. but take out of the list of users that I don't want to change the default
--database for. Hopefully you can fine tune this list down.
declare c_users cursor for
select name from syslogins
where name NOT IN('sa', ' 'readonly', 'BUILTIN\Administrators', 'QA\Administrator' )


open c_users
fetch next from c_users into
@user

while @@fetch_Status = 0
begin
print 'defaulting user '+@user
--PUT IN Default Database here below!
exec sp_defaultdb @user, MY_Default_Database -- <--put your default database right here
fetch next from c_users into
@user

end
close c_users
deallocate c_users



--END SCRIPT--

Transferring Logins/Users between Sql Server 2005 Servers.

OK, as a follow-up from my previous post. Sql Server 2005 is slightly different to transfer users. Same steps, you just need different scripts.

Again, the order of events that you should have completed prior to running this is as follows:
1. you have restored the backup to your destination server
2. You haven't messed with, played with, altered in any way the logins/users on the destination server(don't even think about).

Here they are:

1. Run this on the 2005 server you want to transfer the logins/users FROM

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)  DECLARE @defaultdb sysname  IF (@login_name IS NULL)  DECLARE login_curs CURSOR FOR       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, 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, p.default_database_name, 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, @defaultdb, @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 WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
  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 + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

      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 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, @defaultdb, @hasaccess, @denylogin
 END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO



2.  Execute the above compiled SPROC. (you must be connected to the master database)
EXEC sp_help_revlogin
go


3.  COPY THE RESULTS from above SPROC

Copy results from the sp_help_revlogin and run them in the MASTER database in the destination server.
All done! You will want to
Look through the script and take out the usernames that you don't want to transfer across.. For example,
it will have you administrator accounts in there or something
Now, you might have to reassign the default database for these users.
If you want to do this, see this post.

Moving users to and from SQL Servers

OK, I have been moving users around a lot lately. Posting this should save me some time on having it done in the future. I know there several articles out there on how to do it.. I am posting this, for ease of use on my part and maybe others.. now we don't have to swim through all the MSFT documents to get to the "nitty-gritty" ( I loved Nacho Libre.. "Chancho! I need to borrow your sweats")

You would use this information if you just restored a database to a new server and then realized "What the crap! These users restored with the database, but their logins are not here! That's STUPID!!
Jeez, chill out.. Freak!
No fear, follow the steps below for some serious logins transferring fun!

Sequential time line here:
1. Restore database to new server
2. then run these below steps.. DON'T try and mess with the logins in the master database .. don't try and manually add them in EM or Query Analyzer That's DUMB.. they have their own internal Id assigned them in the DB, these scripts bring them down as tough they are the same.


Alright, the following script is run on the master db. and will create the SPROC that will spit out all the usernames with their current Passwords! THIS APPLIES TO TRANSFERRING LOGINS FOR 2000 AND 7.0
1. Run this on the Source server ( the server you want to transfer the Users FROM)

----- Begin Script, Create sp_help_revlogin procedure -----

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(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

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 @xstatus int DECLARE @binpwd  varbinary (256) DECLARE @txtpwd  sysname DECLARE @tmpstr  varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256)  IF (@login_name IS NULL)  DECLARE login_curs CURSOR FOR    SELECT sid, name, xstatus, password FROM master..sysxlogins    WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
  SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
  PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
  SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
  PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
  EXEC sp_hexadecimal @binpwd, @txtpwd OUT
  IF (@xstatus & 2048) = 2048
    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
  ELSE
    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
  PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
  -- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
  -- login upgraded from 6.5
  SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
  SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
----- End Script -----

OK, now step 2

2. Run this on the SOURCE server

EXEC master..sp_help_revlogin

3. Copy the results from step 2 and run them on the DESTINATION Server. (The server you want the new users to be transfered to).... That's it.. the new logins/users are there and their passwords are the same from the previous server. Now, you might need to run some default database changes. I think this script they default to the master db. No fear, this is easily remedied with a little script, and a cursor.. Bing! Your done..