Commonly Used Restore Commands

Restore is the generic T-sql command we use to restore database backup files. We can also use restore command for other purposes such as validating the backups, find the database files in the backupset etc. Below are the commonly used restore functions other than the obvious restore database.

1.Validating the Backup :

Restore VerifyOnly From Disk= 'E:\BackupFilePath\BackupFile.Bak'

2.Find the Backup Files in the BackupSet

Restore HeaderOnly From Disk = 'E:\BackupFilePath\BackupFile.Bak'

3.Find the Database Files in the Backupset

Restore FileListOnly From Disk = 'E:\BackupFilePath\BackupFile.Bak'

There are also other restore command syntaxes we can use but I refer these more often.

When’s the last time DBCC CheckDB ran without any errors??

There’s an undocumented DBCC command ‘DBCC DBINFO’ that gives us lot of information about the database.
One of the extremely useful info this command returns is the last time DBCC CheckDB ran without any errors.

To find this info, run

DBCC TRACEON (3604)
DBCC DBINFO('MYDATABASE')
DBCC TRACEOFF (3604)

Scroll down to find “dbi_dbccLastKnownGood” value. When you do DBCC DBINFO, along with lots of other information, you will get dbi_dbccLastKnownGood result. It is a date time value, which essentially means, the last time the CHECKDB went well without any corruption. This could be particularly useful piece of information, when your database is corrupted and you can find out the last time your database is corruption free(this information will be up-to-date only if you run DBCC CHECKDB regularly).

How to find database internal version number

When you try to attach a higher version database to lower version sql server(attaching sql2008R2 database to sql SQL2008 Instance),you will get an error message similar to “The database is of version 661. The server will only support databases of version 655 and earlier”. This is error simply means that you are trying to attach higher version database to a lower version sql server.This is not suppported by SQL Server.Remember, we also cannot restore higher version databases to lower version sql server.Below are reference internal numbers for each of version of SQL Server:

Internal Version Number = 611 == SQL2005
Internal Version Number= 655 ==SQL2008
Internal Version Number= 661 ==SQL2008R2
Internal Version Number= 706 == SQL 2012

If you want to find out the database Internal version, run the below
DBCC TRACEON(3604)
GO
DBCC DBINFO('DatabaseName')
GO
DBCC TRACEOFF(3604)

In the results, look for dbi_version.There is also dbi_createVersion and this shows the actual version it was created on.
Example:  if the database was created on SQL 2005 and later migrated to sql 2008R2).
In that case, you would see, dbi_createVersion = Internal version number the database was intially created which is sql 2005 Internal Version Number as per example and dbi_version = shows the present Internal version number which is sql 2008R2 Internal Version Number

Find SQL Server Version and Edition Information

There are several ways to find out the sql server edition/version and service pack levels. Some of the common methods are

1. Select ServerProperty('ProductVersion'),ServerProperty('Edition'),
ServerProperty('ProductLevel')

2.Select @@Version

3.Open the SQL Server Error  Log file and you would find this information there.

4. Go to Run–>type ‘RegEdit’–> HKEY_LOCAL_MACHINE–>SOFTWARE–>MICROSOFT–>MICROSOFT SQL SERVER–>MSSQL10.50.MSSQLSERVER–> SET UP.

Methods 3  and 4 are useful when you cannot connect to sql server but you want to find Version/Edition details of your sql server.

http://www.SQLServerbuilds.blogspot.com is a very handy website when you want to find information on sql server builds, latest hotfixes and patches.

Moving system database files to new location

Sometimes, we need to move system databases to a different location for various reasons. Moving system databases is little different compared to user databases. TempDB is the usual suspect as it can fill up the disk space and if it is not on a dedicated disk, it can cause more problems. In that case, we can either add a new file or move the TempDB to different drive.

To add new file to TempDB, you can do as below.
USE [MASTER]
GO
Alter database TempDB
Add File (Name= 'TempDev2', FileName = 'E:\TempDBData\TempDev2.ndf',SIZE = 4096MB , FILEGROW =500MB)

Note: TempDB cannot have user defined filegroups. Also, Sql Server uses Proportional Fill algorithm to split the data across multiple files. So, use caution on how you allocate the disk space.

If you could afford some downtime and prefer to move the TempDB files to different location, you can do as below and RESTART the sql server.
USE [MASTER]
GO
Alter database TempDB
MODIFY File (Name= 'TempDev', FileName = 'E:\TempDBData\TempDev.mdf',SIZE = 4096MB , FILEGROW =500MB)
GO
Alter database TempDB
MODIFY File (Name= 'TempLog', FileName = 'E:\TempDBLog\TempLog.ldf',SIZE = 1024MB , FILEGROW =250MB)

NOTE: one of the best practice suggestion is to assign one tempdb file for every 4 logical processors, i.e, if you have 16 logical processors, it is best to have maximum of 4 TempDB files.

MSDB: I recently had to move MSDB to different drive and below are steps I followed to accomplish the same :
1. Use Alter Database .. Modify File .. command to make the sys.database_files point to the new location.
2. We cannot set the MSDB database to offline. So, we need to stop the SQL Server.
3. Copy over the files to new location.
4. Restart the SQL Server.

How to move user database files to a different location

Sometimes, we need to move the user database files to a different location. There are several ways to do it,

One way is to use sp_detach_db and copy the files and use sp_attach_db.

sp_detach_db cannot be used, if the database is being replicated. Also,sp_attach_db is not recommended anymore by Microsoft.

One other way to move database files to new location is:

USE [TESTA]
GO
Select * from sys.database_files--This gives the current location of the files
GO

USE [master]
GO
Alter database TestA
Modify File (Name = 'TestA',filename= N'E:\Data\Data\TestA.mdf')
GO
Alter Database TestA
Modify File (Name = 'TestA_Log',FileName = N'E:\Data\Data\TestA_Log.Ldf')
GO

ALTER DATABASE TESTA Set OFFLNE

---Copy the physical files to the new location

ALTER DATABASE TestA Set ONLINE

If you are moving the files to a new location, the path should already exists(I.e.I moved the files to new location,”E:Data\Data”. This path along with the folder structure should already exists).

Also, This method works for replicated databases as well. It is better to stop the replication jobs while doing this operation and enable them again.

TempDB Actual Current Size

Sometimes, the actual physical size of TempDB is not always shown correctly when you run the below query.

Select Database_id,((size*8)/1024) as SIZE_IN_MB, Name,Type_desc,Physical_Name from sys.master_files where database_id=2

Or when you right click on the TempDB database and look in the properties.

It only shows the last configured\start up size of the TempDB. This happens when the TempDB grows beyond the initial configured size.So, to get the actual size of the TempDB, we need to run

sp_helpdb 'TempDB'

You can also use below query to find the actual current size of the TempDB.
select Name,((size*8)/1024) as SIZE_IN_MB from Tempdb.sys.database_files