How to change the SQL Express sa password from a command prompt

To change the SQL sa password from a command prompt:

Start a command prompt by typing Start – Run – cmd

Enter the following commands, pressing Enter after each line

OSQL -S Localhost\SQLExpress -E
1> EXEC sp_password NULL, ‘New password’, ‘sa’
2> GO

 

Try to connect SQL with new password

sqlcmd -U sa -P New_Password

or

sqlcmd -S Localhost\SQLExpress -U sa -P New_Password

Advertisements

SQL Server 2008: Forgot to add an administrator account?

How to recover SA password on Microsoft SQL Server 2008 R2

First start SQL service in a Single User Mode.

  • Stop the SQL services

  • Edit the properties of the SQL Service

  • Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

    • Start the SQL services. These are now running in Single User Mode.
    • Start CMD on the SQL server

 

Start the SQLCMD by using below command.

sqlcmd -S Computername\SQLEXPRESS -E

 

Now we create a new user. Enter following commands

CREATE LOGIN vijay WITH PASSWORD = ‘vijay’
go

Now we grant the user a SYSADMIN roles using the same SQLCMD window.

exec sp_addsrvrolemember ‘vijay’, ‘sysadmin’
go

  • Stop the SQL service again
  • Change the SQL service properties back to the default settings

  • Start the SQL service again and  login with newly created user(vijay in my example) with SQL Server Authentication.
  • Go to security panel and access properties of sa and change the password.

Now Change SA password.

Find Last Restart of SQL Server

Here are a few different ways that you can tell when SQL Server was last started.


Method 1: sys.dm_os_sys_info

This DMV, sys.dm_os_sys_info, contains a column that holds the startup time for SQL Server. You can run the following command to find the start time.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

sys.dm_os_sys_info


Method 2: SQL Server Error Log

If you open the current SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log you can see when the server was started. Note the highlighted text below “SQL Server is starting”.  You will want to look for this to make sure the error log was not cycled and give you a false impression of the start time.

sql server error log start up time

Here is an example of a cycled error log and note the highlighted text.   If you see this you will need to look at a previous error log until you find the log that contains the information in the image above.

sql server log initialized

Note: if you are running the Express edition of SQL Server you cannot read the error logs directly from SSMS, but you can use xp_readerrorlog or navigate to the folder where the log files are stored and use a text editor to read the SQL Server error log files.


Method 3: Dashboard Reports

Another option is to use the SQL Server Dashboard Report.  Right click on the server name in SSMS and select Reports > Server Reports > Server Dashboard and you will get a report similar to the following.

sql server dashboard report


Method 4: Windows Event Viewer

Another option is to use Windows Event Viewer to look for the startup time.  If you open Windows Event Viewer and filter your Event Sources (in our case MSSQL$SQL2008) for the SQL Server instance and Event ID 17162 you can find all of the startup times that are still in the Event Viewer log.

windows event viewer start time

Here is a partial listing of the startup times for this instance.

windows event viewer sql startup log

SQL Database Mirroring Steps

1. Delete if any endpoint exist for mirroring from principal and Mirror server
a. Select endpoint
i. SELECT * FROM sys.tcp_endpoints
b. Delete Endpoint
i. Drop endpoint <endpoint name>
2. Create Firewall rule
a. Principal database server
i. Create outbound rule for that particular port
1. Rule type: Port
2. Protocol type: TCP, Specific remote port: <Port number>
3. All The connection
4. Enable all (Domain, private, public)
5. Name the rule.
ii. Create Inbound rule for that particular port
b. Mirror database server
i. Inbound Rule
ii. Outbound Rule
3. Create endpoint in both server
CREATE ENDPOINT <Endpoint Name>
STATE = STARTED
AS TCP (LISTENER_PORT = 5555)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
4. All port number must be same and confirm that no any port is in use.
5. Set recovery model to Full in principal database
a. Property – > option -> Recovery model -> Full.
6. Generate full backup from Principal database.
7. Restore database from backup in Mirror server.
a. Make sure that database name and path will be same.
8. Create mirror from principal database
a. Principal database -> property -> mirror -> Configure security -> next -> Service account (Leave it blank). -> start mirr