Moving a SQL Server Database

Should your SQL server need to be decommission, it will be necessary to move the databases.

Two method: backup or detach.
Backing up requires an intermediate BAK file, so I prefer the detach method.

1) Shrink the datafile. You can truncate the log by:

    BACKUP LOG dbname WITH TRUNCATE_ONLY

2) Then use the GUI to shrink the log file, or if you know the log file name use: dbcc shrinkfile (file_name)
3) Detach

 sp_detach_db 'dbname'

4) Copy the database to the new server
5) On the new server attach the database:

 sp_attach_db 'dbname','D:\Sqldata\mydbdata.mdf','L:\Sqllog\mydblog.ldf'

6) Repair/fix SQL logins. If new server already has the same named logins, use the below command:

 EXEC sp_change_users_login 'auto_fix', ''

To compliment step 6, you can determine which logins need repair by running a discrepancy check:


select 'EXEC sp_change_users_login ''auto_fix'', ''' + name + ''''
from sysusers su
where hasdbAccess = 1
and  not exists ( SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid )

Comments

VB.Net code to control mouse movement and click

VB.Net code to perform mouse movements and clicks. Include references at the top of the class code file to Windows interface libraries: Public Declare Auto Function SetCursorPos Lib "User32.dll" (ByVal X As Integer, ByVal Y As Integer) As Integer Public Declare Auto Function GetCursorPos Lib "User32.dll" (ByRef lpPoint As Point) As Integer Public Declare Sub mouse_event Lib "user32" Alias "mouse_event" (ByVal dwFlags As Integer, ByVal dx As Integer, ByVal dy As Integer, ByVal cButtons As Integer, ByVal dwExtraInfo As Integer) Some fixed constant values will be needed, so include these as basic names: Public Const MOUSEEVENTF_LEFTDOWN = &H2 Public Const MOUSEEVENTF_LEFTUP = &H4 Public Const MOUSEEVENTF_MIDDLEDOWN = &H20 Public Const MOUSEEVENTF_MIDDLEUP = &H40 Public Const MOUSEEVENTF_RIGHTDOWN = &H8 Public Const MOUSEEVENTF_RIGHTUP = &H10 Public Const MOUSEEVENTF_MOVE = &H1 This ...

Windows Firewall can not run because another program or service is running that might use the Network Address Translation component (IPNat.sys)

Windows Networking Firewall failure Error Upon trying to open and configure the Windows built-in Firewall, you receive the error: "Windows Firewall can not run because another program or service is running that might use the Network Address Translation component (IPNat.sys)" Cause is due to settings left in by "Routing and Remote Access" service. Even if the service is stopped, Windows will still report this error because the network card bindings are still being held by RRAS. Disable RRAS by opening the MMC for it and "Disable Remote Access and Routing". This can also be found by Right -clicking "My Computer", opening the Service and Application node. By Disabling RRAS in this way, the network protocol interface bindings are removed allowing for the Windows Firewall and Connection Sharing service to take over.

Google's Automated Search Query Capture

It's known that Google takes preventative measures to reduce automated use of their search engine. In fact, Googles terms of service restrict the use of automated queries. Normally human users with real browsers will not be suspect of such use and thereby should not trigger firewall rules that detect queries that appear to be automated. However I found myself in just that position. After running several varied queries, I came back to running the repeating a past query (through the browser drop down query history) and received the following : HTML Source Interesting to note is that the page response header is a 503 error code . I suspect this was triggered by my complex query, retrieving multiple pages or results, and repeated usage in a short period. Google knowledge base on this topic suggests that users that have this problem may also have a virus or other spyware on their computer or another in the network.

Dave's Techytips

Search This Blog