Skip to content

How to move database files from one drive to another or from one location to another in SQL Server?

February 28, 2013

There are times when due to database growth sometime the drives are not able to accommodate any more data growth or you may want to move one database files to another drive to fix performance issue or someone created database with default file locations and you want to transfer files to appropriate folders. It can easily be done in SQL Server. In this article I would cover some of the easy ways to do, considering that it’s not a database migration to another server, database is not part of replication or mirroring. Important Points :

  • Take appropriate downtime (database will not be available during movement). Time required depends on size of database files and how fast database files are getting copied from one location to another.
  • No active connections to database. Kill all the active connections to the database.

Method 1: Detach and Attach: Detach Database so that the files become independent, cut and paste the files from source to destination and attach again. This can be done from Management Studio via GUI or script. Before you proceed with database detach make sure you have physical location  and logical name of database files. You can do it either by [Sp_helpdb Database_name] or Right click database in management studio and select properties → Files.

Method 1 a) Using GUI:  Connect to instance in Management Studio and select database whose files you want to move → Right Click → Tasks → Detach (Note: if there are active connection you receive an error Please close the active connections before detaching the database. Refer Screenshot below). Kill any active connections and try again.

Movedatabasefiles1

Movedatabasefiles2

Once database is detached you will not be able to see it in Management studio so it’s important to have physical and logical names of database files handy. Now move the physical files from source location to destination by copy and pasting, once done delete the file from source location (I do not recommend cut and paste because if there is some issue while copying you still have the source file) . Once done go back to management studio and select databases → Right Click → Attach → Add (Add both Data and logfile from new locations) and click OK .

Movedatabasefiles3

Method 1 b) T-SQL Script to detach and attach database. Use below script to detach and attach database files:

-- Get physical and logical names of database files and keep it somewhere safely.
Sp_helpdb testdb -- Replace Testdb with you DB name.

USE [master]
go
-- Replace TestDB with database name you want to deatch
EXEC master.dbo.Sp_detach_db  @dbname = N'TestDB'
go

-- PHYSICALLY MOVE FILES FROM SOURCE AND DESTINATION AS DONE IN PREVIOUS STEP

-- Attach Database Script. Replace Testdb with your DB name and new physical locations of files.
Sp_attach_db 'testdb', N'C:\MSSQL\DATA\TestDB.mdf', N'C:\MSSQL\DATA\TestDB_log.ldf'
GO

Method 2: T-SQL script to take Database offline and move database files and then bring database online. This much is faster and convenient. Use below script.

-- Get physical and logical names of database files and keep it somewhere safely.
sp_helpdb TestDB -- Replace Testdb with you DB name.
GO

USE [master]
GO
-- Replace Testdb with you DB name.
ALTER DATABASE TestDB SET OFFLINE

-- PHYSICALLY MOVE FILES FROM SOURCE AND DESTINATION AS DONE IN STEP 1a ONCE DONE EXECUTE BELOW SCRIPT TO UPDATE SYSTEM
CATALOGS WITH NEW FILE LOCATIONS.

USE master
GO
-- Replace Testdb with you DB name and locations with new file locations.
ALTER DATABASE TESTDB MODIFY FILE (NAME = TestDB, FILENAME = N'C:\DATA\TestDB\TestDB.mdf')
GO
ALTER DATABASE TESTDB MODIFY FILE (NAME = TestDB_log, FILENAME = N'C:\DATA\TestDB\TestDB_log.ldf')
GO

USE [master]
GO ALTER DATABASE TestDB SET ONLINE
Advertisements

How to create a new Error log file in SQL Server? Or how do I recycle my Error log file without SQL Server Service restart?

February 26, 2013

If your log file is getting too bulky and taking lot of time to load and searching errors from it is becoming a problem it’s time to recycle error log file, although a new error log file is created every time SQL Server services are restarted but it does not mean you have to recycle SQL Server services every time you want error log file to recycle. You can use below command to recycle error log file without service restart.


EXEC sp_cycle_errorlog

Now, next step is to check newly created error log file or How to check when was the last time my Error log file was created? You can do it either using command or directly in SSMS or checking physical log file creation date.

Method 1: Using below command you can easily check when was the latest log file is created. Check the date and time of first line, this is the time when log file was created.


EXEC xp_readerrorlog

Screenshot:

Recycle error log1

Method 2: Using SSMS: Open Error log file SQL Server Agent  → Error log → Current → Scroll down to the bottom of file and check for date on the last line. This is date when this error log file was created.

Screenshot:

Recycle error log2

Method 3: Checking Physical file: How to get physical location of Error log file? You can easily get the location of physical location of error log file form Start up parameters (SQL Server Configuration Manager→ Right Click SQL Server Service → Properties → Advanced → Startup Parameters → Look for location of error log file.

Screenshot:

Recycle error log3

Once the location of Error log file is traced it, it’s really easy to know when the latest file was created. Sort files by date modified and check create date of last modified file or sort files by date created.

Important Points:

  • Be very careful while checking Error log file location, make sure you do not change or delete any start up parameters.
  • This task can easily be automated and scheduled using SQL Agent job.

How to get my SQL Server Database Restore history? Or when was my SQL Server database last restored?

February 26, 2013

There can be situation when your developer calls and say he doesn’t see production data restored to lower environments although a database restore to lower environments is scheduled to happen every night. How to check when was the last time database(s) was restored? You can use below script to get the info.

1: Script to get restore history of database(s). With a little modification as indicated you can get below details as well:

  • Restore History of only one/all database(s).
  • Restore history of one/ all the database(s) between or on a particular date.
  • When was last time database restored from full/diff/log backup.

SELECT destination_database_name AS 'Database Name',
restore_date AS 'Date Restored',
CASE restore_type
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Diffrential backup'
WHEN 'L' THEN 'Log Backup'
END AS 'Restored From',
CASE recovery
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS 'With Recovery',
backup_finish_date AS 'Backup taken',
Cast(backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,
physical_device_name AS 'Backup Location'
FROM msdb..restorehistory,
msdb..backupset,
msdb..backupmediafamily
WHERE msdb..restorehistory.backup_set_id= msdb..backupset.backup_set_id
AND msdb..backupset.media_set_id=msdb..backupmediafamily.media_set_id

-- Uncomment below line and Replace <Database name> with DB you want to check backup history
--AND database_name='Db_name'

-- Uncomment below line and replace start and end dates with dates you want to check history
--AND restore_date>='2013-01-29 00:00:00' And  restore_date<='2013-01-29 23:59:59'

--Uncomment below line to see only the restore from full backups, replace with 'I' to check diffrential and 'L' to check only Log backups.
--And restore_type ='D'

ORDER BY destination_database_name,
restore_date DESC 

How to get SQL Server Database backup history? Or when was my SQL Server database last backed up?

February 26, 2013

It’s important to keep track of all the backups happening to make sure we have a latest backup available and all the required databases are backed up regularly. Use below scripts to check backup history and other backup related details.

1: Script to get backup history of all the databases. With a little modification as indicated in script you can easily get below details:

  • Script for Backup History of only one database.
  • Script for Backup history of one/ all the databases between particular dates
  • Script for only Full/Diff/Log backups of all/ Particular database, complete history or between particular dates.
SELECT p.database_name AS DatabaseName,
p.backup_start_date AS 'Backup Start Time',
p.backup_finish_date AS 'Backup Finish Time',
CAST((DATEDIFF(MINUTE, p.backup_start_date, p.backup_finish_date)) AS varchar)+ ' min  '+ CAST((DATEDIFF(ss, p.backup_start_date, p.backup_finish_date)) AS varchar) + ' sec ' AS [Total Time] ,
CASE p.type
WHEN 'D' THEN 'Full '
WHEN 'I' THEN 'Diffrential'
WHEN 'L' THEN 'Log'
END AS 'Backup Type',
Cast(p.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,
a.physical_device_name AS 'Physical File location'
FROM msdb..backupmediafamily a,
msdb..backupset p
WHERE a.media_set_id=p.media_set_id

-- Uncomment below line and Replace <Database name> with DB you want to check backup history
--and p.database_name='Database name'

-- Uncomment below line and replace start and end dates with dates you want to check history
--and p.backup_start_date>'2013-01-20' and p.backup_start_date<'2013-01-25 23:59:59'

--Uncomment below line to see only the full backups, replace with 'I' to check diffrential and 'L' to check only Log backups.
--and p.type='D'

ORDER BY p.backup_start_date DESC 

2: Script to get name of databases are never backed up:

SELECT name AS 'Database Name',
       NULL AS [last backup]
FROM sys.sysdatabases
WHERE name NOT IN
    (SELECT database_name
     FROM msdb..backupset)
  AND name <> 'tempdb'

3: Latest Full/Differential/Log backup of one/all databases with duration:

SELECT Database_name,
       MAX(Backup_finish_date) AS 'latest Backup Date',
       CAST((DATEDIFF(hh, MAX(Backup_finish_date), GETDATE())) AS varchar) AS [Total Time]
FROM msdb..backupset

--Replace 'D' with 'I' to check latet Diff backup and 'L' to check latest full backup
WHERE TYPE='D'

--Uncomment below line and replace database name you want to check latest full backup
 --and Database_name='master'

GROUP BY database_name

Important Points:

These scripts can esaily be used in a scheduled job and scheduled to run so that you always know which databases are missing from backup schedules and if all of them are backed up properly.

How to audit login to my SQL Server (both failed and Successful)?

February 25, 2013

Solution: One day my manager called me and asked me to get him details of the people who logged into one of instances a week back between 4-5 PM. I knew this day was coming and thank fully I already setup login auditing on all our SQL Server instances. It always a good practice as getting login details real time is easy but what if you want to know if you are investigating an issue happened earlier and need to have list of all login attempts during a particular time. In this article I will go over some of the quick setup that you can do to enable login auditing.

Method 1: Using SQL Server management Studio: Connect instance in Management Studio → Right Click and select  server Properties → Security → Under Login Auditing select both failed and Successful logins and click OK.  This will audit all the login attempts in Error log.

Note: An important point to be considered is, for this change to come into effect you need to restart your SQL Server Service.  So plan your downtime accordingly.

If you want to search for a particular login attempts, you can always search it in error log. If you can closely look at below screenshot I am only searching for login attempts for User ‘AuditLoginTest1’ so are the results. You can even define timeline or a specific computer name from where login request should be coming. SQL Server is awesome 🙂

Screenshot:

AuditingLogin1

You can also read the error log file in Management Studio by using below command.

xp_readerrorlog 0,1,”AuditLoginTest1″ – Replace AuditLoginTest1 with your login name you want to search.

0 – Current Error log.

1 – Error log (Use 2,3… )

<AuditLoginTest1> – text you want to search.

Screenshot:

AuditingLogin2

Method 2: Using SQL Server Trace: You can setup a trace from same server or a different server to audit all the login attempts to SQL Server and save it safely in a file.  You can open Profiler from SQL Server management Studio → Tools → SQL Server Profiler or you can login to server  All programs → SQL Server → Performance Tools → SQL Server Profiler. Connect to correct SQL Server instance → Change trace file name → Select save to file (You can save it as SQL table by selecting save to table option, provide appropriate database name and Table name) → Select location where you want to put your trace file → Go to tab Event Selection at the top → Check box Show all events → Choose “Audit Login” and “Audit Login Failed” (Refer screenshot below) → Once done click on run.

  • Note: To setup this monitoring you don’t have to restart SQL Server Services.
  • If you have logged into server to start the trace make sure you do not logoff or the trace will be stopped. Keep your login as an active login to server.

Screenshot:

AuditingLogin3

AuditingLogin4

Method 3: Using SQL Server Auditing: With SQL Server 2008 and subsequent versions you can enable SQL Server auditing to audit login attempts. It’s easy to setup and gives accurate results.

Step 1: Connect instance in management studio and under → Security Dropdown → Select Audits → Right Click and select new Audit… → provide Audit name and select location where Audit files will be saved → Click Ok to create Audit.

AuditingLogin5

Step 2: Now right click newly created Audit and Select enable → After that Right Click on Server Audit Specification → Select new Server Audit Specification → Provide appropriate name and → from Audit drop down select LoginAudit (remember we just created it in previous step) and Audit Auction types as “Failed Login Group” and “Successful_Login_Group” and click ok → Right Click on new created group and select enable. DONE !

AuditingLogin6

Now right click Login Audit enable in Step 1 and select view Audit logs. Bingo! All the login attempts along with server name and user Id is there.

Screenshot:

AuditingLogin7

Important Points:

  • Using above steps you can easily keep track of all the users logging into your SQL Server.
  • One more important is you can check if a user is complaining that he cannot log into the database, you can easily check the logs to see what is the issue.
  • If the users is coming from an application and intern application is connecting to the database using a different login then it would not be possible to know which user connected to database.

How to Restart SQL Server Services

February 14, 2013

Solution: There are cases when you will have to restart your SQL Server Services example after adding a trace flag, changing configuration settings etc. there are different methods that you can use to restart services. I will cover some of them in this article.

Option 1:  Stop Start SQL Server Services from Configuration manager: The most used and recommended method is to restart Services using SQL Server Configuration manager, you can find SQL Server configuration manager under All programs → Microsoft SQL Server → SQL Server Configuration Manager → SQL Server Services → Right Click service and select restart.

Refer Screenshot below:

RestartSQLServices.1

Option 2: Restart Services from Service Console: Open service control manger either by typing services.msc on run prompt or using services Console shortcut under control panel → Administrative tools → Services. All the SQL server services will start with SQL Server → Find the service right click and restart.

Refer Screenshot below:

RestartSQLServices.2

Option 3: We can restart SQL Server Services from management Studio using “xp_servicecontrol”

Syntax: xp_servicecontrol @Action = ‘<Action>’ @ServiceName = ‘<Service Name>’

Action: Start (Will start the service if stopped), stop (will stop the service if started), querystate (returns the current state of the service.). Service Name (name of the service, to get service name open services console shown in step 2 → Right click service → properties → Check Service name). Note: There is no action as “restart” so in order to restart a service you have to stop and start it.

Example:

xp_servicecontrol @Action ='querystate',@ServiceName ='SQLSERVERAGENT'
go
xp_servicecontrol @Action = 'querystate', @ServiceName = 'MSSQLSERVER'

Note: Do not restart MSSQL Service using above command, there are couple of reasons for not doing it as mentioned below:

  • SQL Server agent is dependent on SQL Services so before stopping MSSQL Server agent service needs to be stopped. If you directly try to stop MSSQLServer service it will give an error.
  • If you stop SQL Server service services you will not be able to execute start command as no query can be executed on an instance whose MSSQLServer service is down. So you will have to then manually start the service using setp 1 or setp 2.

This command is more relevant to quickly checking status of any service like the reporting service, Browser service, MSDTC etc.

Option 4: Stop Start SQL Server Services using command line:

Login to server → Open Command prompt (Where is command prompt? You can open it by typing cmd on run prompt or you can find command prompt under accessories), use below command to stop start SQL Server Services.

To Stop Service:

C:\>net stop <Servcie name>

Example: C:\>net stop MSSQLSERVER – will stop SQL Server Service on server

———————

To Start Service:

C:\>net start <Servcie name>

Ex: C:\>net start MSSQLSERVER – will start SQL Server Service on server

Important Points:

  • To stop and disable a service(Ex: during server Decommission) use Service console or configuration manager.
  • If you want to change start up parameters for any service then you have to use configuration manager.

How to identify version and Edition of SQL Server ?

February 14, 2013

In this article I have covered some of the key methods of detecting SQL Server version and Edition.

Method1 : An easy way to find out SQL version is to use version command.
Use below query

Select @@VERSION
go
SELECT SERVERPROPERTY('productversion')

Screenshot:

SQL SERVER Version1

 

 

 

Method 2: Version can easily be found in SQL Server Agent logs, simply search for version in current log file. Whenever SQL Server Services are restarted an entry of version is written into logs.

Screenshot:

SQL SERVER Version2

Method 3: SQL Server Discovery report: Starting SQL Server 2008 you can use this report to determine version of SQL Server. To open this report follows below steps:

All programs  SQL Server → Configuration Tools →  SQL Server Installation  Centre → Under Tools → Select Installed SQL Server feature discovery report.

SQL SERVER Version3

 

 

 

Based on version number you can determine Edition and Service pack installed. The following table lists the major SQL Server releases.

SQL Server 2012 version information

Release

Product Version

SQL Server 2012 Service Pack 1

11.00.3000.00

SQL Server 2012 RTM

11.00.2100.60

SQL Server 2008 R2 version information

Release

Product version

SQL Server 2008 R2 Service Pack 2

10.50.4000.0

SQL Server 2008 R2 Service Pack 1

10.50.2500.0

SQL Server 2008 R2 RTM

10.50.1600.1

SQL Server 2008 version information

Release

Product version

SQL Server 2008 Service Pack 3

10.00.5500.00

SQL Server 2008 Service Pack 2

10.00.4000.00

SQL Server 2008 Service Pack 1

10.00.2531.00

SQL Server 2008 RTM

10.00.1600.22

SQL Server 2005 version information

Release

Product version

SQL Server 2005 Service Pack 4

9.00.5000.00

SQL Server 2005 Service Pack 3

9.00.4035

SQL Server 2005 Service Pack 2

9.00.3042

SQL Server 2005 Service Pack 1

9.00.2047

SQL Server 2005 RTM

9.00.1399

SQL Server 2000 version information

Release

Product version

SQL Server 2000 Service Pack 4

8.00.2039

SQL Server 2000 Service Pack 3

8.00.760

SQL Server 2000 Service Pack 3

8.00.760

SQL Server 2000 Service Pack 2

8.00.534

SQL Server 2000 Service Pack 1

8.00.384

SQL Server 2000 RTM

8.00.194