Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,644,765 hits
  • Select GETDATE()

    July 2019
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  
  • Advertisements

Archive for the ‘Tips and Tricks’ Category

Tips and Tricks : Move database files to new location – various approaches

Posted by blakhani on August 14, 2014


Recently someone ask this question to me – How would you move database files to new location with no downtime. Based on my knowledge, the downtime is needed as we need physical movement of the database files and they can’t be moved unless the handle is released by a process, which is sqlservr.exe in our case. So I provided various options to them and decided to choose the best one. Here is the database create script, in case you want to follow along the blog.

CREATE DATABASE [SQLServerHelp]
 ON  PRIMARY 
( NAME = N'SQLServerHelp', FILENAME = N'C:\OldLocation\SQLServerHelp.mdf' ) LOG ON 
( NAME = N'SQLServerHelp_log', FILENAME = N'C:\OldLocation\SQLServerHelp_log.ldf')
GO

 

1. Detach/Attach

This is straight forward approach where we detach the database, move physical files to new location and attach database from there. When we detach, the database would no longer be available. Here are the steps:

--Step 1) detach the database (DOWNTIME STARTS)
USE master
go
sp_detach_db 'SQLServerHelp'
go


-- Step 2)- move files physically to new location
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp.mdf” “C:\NewLocation\SQLServerHelp.mdf”'
GO
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp_log.ldf” “C:\NewLocation\SQLServerHelp_log.ldf”'
GO

-- If xp_cmdshell is not enabled or you can't enable then do operating system copy and paste using Ctrl+C and Ctrl+V


-- Step 3) attach the database (DOWNTIME COMPLETES)
CREATE DATABASE SQLServerHelp 
ON (FILENAME = 'C:\NewLocation\SQLServerHelp.mdf'), 
   (FILENAME = 'C:\NewLocation\SQLServerHelp_log.ldf') 
FOR ATTACH; 


Downtime: Starts at Detach and ends at attach. if it’s a big database then copying them is the actual unavailability of the database.

2. Alter Database

This is similar to detach/attach method but instead of detaching, we take database offline. Then we use ALTER DATABASE command to modify location in system catalog. Before you attempt to use this trick, save the output of below as it would be needed in step 3 

use <database_name>
go
sp_helpfile
go

Here are the steps

--Step 1) set database offline (DOWNTIME STARTS)
 
ALTER DATABASE [SQLServerHelp]
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO

-- Step 2) move files physically to new location
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp_log.ldf” “C:\NewLocation\SQLServerHelp_log.ldf”'
GO
EXEC xp_cmdshell 'COPY “C:\OldLocation\SQLServerHelp.mdf” “C:\NewLocation\SQLServerHelp.mdf”'
GO
-- If xp_cmdshell is not enabled or you can't enable then do operating system copy and paste using Ctrl+C and Ctrl+V
-- Step 3)- alter database to reflect the new location
-- select * from sys.sysaltfiles where dbid = db_id('SQLServerHelp') can be used to get logical name used below

ALTER DATABASE [SQLServerHelp] MODIFY FILE (
    NAME = 'SQLServerHelp' ,FILENAME = 'C:\NewLocation\SQLServerHelp.mdf'
    )
GO
ALTER DATABASE [SQLServerHelp] MODIFY FILE (
    NAME = 'SQLServerHelp_log' ,FILENAME = 'C:\NewLocation\SQLServerHelp_log.ldf'
    )
GO

-- Step 4) set database online (DOWNTIME COMPLETES)
ALTER DATABASE [SQLServerHelp]
SET ONLINE
GO

Downtime: Starts at offline and ends at online. if it’s a big database then copying them is the actual unavailability of the database.

3. Backup/Restore

This is little untraditional approach but you can notice that If the database size is big then above approaches (1 & 2) would need more downtime as we need to move all files to new location. This approach of backup/restore would help in files movement with less downtime. One big disadvantage of this approach is that we need additional disk space than earlier approaches. In this approach we need to have a full backup of the database which needs an additional space. Also note that we are going to have two copies of all data and log files as we don’t have choice to do Cut+Paste. Here comes the compromise between business downtime vs. storage requirement. Here are the steps. We need database to be in full recovery model because we are going to use log backup capability.

--Step 1) take a full backup of the database 
USE master
go
BACKUP DATABASE [SQLServerHelp] TO  
DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp.bak' 
WITH FORMAT, INIT
GO

-- Step 2)- Restore backup as different database with files at new location with norecovery
USE [master]
RESTORE DATABASE [SQLServerHelp_New] FROM  
DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp.bak' 
WITH NORECOVERY,
MOVE N'SQLServerHelp' TO N'C:\NewLocation\SQLServerHelp.mdf',  
MOVE N'SQLServerHelp_log' TO N'C:\NewLocation\SQLServerHelp_log.ldf' 
GO


-- Step 3) Take log backup with NORECOVERY (DOWNTIME STARTS)
BACKUP LOG [SQLServerHelp] 
TO  DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp_Tail.bak' 
WITH  NO_TRUNCATE , FORMAT, INIT,  
NORECOVERY 
GO

-- Step 4) Restore tail log backup with RECOVERY 
RESTORE LOG [SQLServerHelp_New] 
FROM  DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\SQLServerHelp_Tail.bak' 
WITH RECOVERY
GO

-- Step 5) Drop the old database and rename the new one (DOWNTIME ENDS)
Drop Database SQLServerHelp
go
sp_renamedb 'SQLServerHelp_New', 'SQLServerHelp'


Downtime: Starts with last log backup performed using “with norecovery” and end after rename of new database to original name.

Now you can choose option based on downtime and space availability. 

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Tips and Tricks | Tagged: , , , , | Leave a Comment »

    Tips and Tricks : Error: 5171 – tempdb.mdf is not a primary database file

    Posted by blakhani on August 12, 2014


    If you are getting same error for database other than tempdb then there is a serious issue with the file. Primary file is a database file which contains information about database itself like location, size of other files and other information about the database. Error 5171 means that SQL Server is attempting to get the information for a database from a file that is not the primary file.

    While doing some testing with TempDB database I started getting below errors in ERRORLOG and SQL Server was not getting started.

    2014-08-12 05:08:24.91 spid9s      Clearing tempdb database.

    2014-08-12 05:08:28.20 spid9s      Error: 5171, Severity: 16, State: 1.

    2014-08-12 05:08:28.20 spid9s      F:\TEMPDB\tempdb.mdf is not a primary database file.

    2014-08-12 05:08:28.26 spid9s      Error: 1802, Severity: 16, State: 4.

    2014-08-12 05:08:28.26 spid9s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    2014-08-12 05:08:28.26 spid9s      Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

    2014-08-12 05:08:28.29 spid9s      SQL Server shutdown has been initiated

     

    This started happening after I moved TempDB to new location using my own earlier blog. Here is the command which I have run

    USE master; 
    GO 
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = tempdev, FILENAME = 'F:\TEMPDB\tempdb.mdf'); 
    GO 
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = templog, FILENAME = 'F:\TEMPDB\tempdb.mdf'); 
    GO 
    
    

    If you notice closely, I have made mistake in extension of the files and due to which both files are same. This can easily be corrected by starting SQL in minimal configuration using parameter f and correcting the path.

    When I tried the same in SQL Server 2014, I got below error message, which is amazing.

    Msg 12106, Level 16, State 1, Line 6

    The path name ‘F:\TEMPDB\tempdb.mdf’ is already used by another database file. Change to another valid and UNUSED name.

    If this is happening for database other than TempDB after moving then you may want to check if move command was proper or not. You need to check logical name and the physical file path. If this is after some crash then you may need to restore from a last known good backup. If you don’t have backup then … you need to find a new assignment! Take this as a new lesson and move on. There are data recovery tools available but I have not worked with them and can’t recommend anyone.

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks, Troubleshooting | Tagged: , , , | 1 Comment »

    Tips and Tricks : Database restore will fail in STANDBY mode

    Posted by blakhani on August 5, 2014


    One of the various methods to move user database from lower version to higher version is backup restore. I have seen various upgrade scenarios using log-shipping and database mirroring as well. In past few days, I have seen similar question from at least three friend and I was surprised. Below are the questions:

    • We are planning a move, our production database from server A (which is SQL2005) to our new server B (which is SQL2008 R2). We are thinking and testing setting up logshipping between these servers in standby mode. This is because few applications need only to read the data.
    • If my primary server is 2008 (standard enterprise edition) and 2008 r2 (standard edition), then wil it possible to restore 2008 databse back-up files on 2008r2 database using standby mode recovery model

    When we try to restore in standby mode to higher version, we would get below error message

    Msg 3180, Level 16, State 1, Line 2
    This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    This is applicable to any lower to higher version restore. The standby mode restore is not allowed because the system level information (table, columns) might get change across version. If we perform backup from SQL 2012 and restore on SQL 2014 (using “WITH RECOVERY”, we can see below messages.

    64 percent processed.
    100 percent processed.
    Processed 0 pages for database ‘MyDatabase’, file ‘M’ on file 2.
    Processed 13 pages for database ‘MyDatabase’, file ‘M_log’ on file 2.

    Converting database ‘MyDatabase’ from version 706 to the current version 782.

    Database ‘MyDatabase’ running the upgrade step from version 706 to version 770.
    Database ‘MyDatabase’ running the upgrade step from version 770 to version 771.
    Database ‘MyDatabase’ running the upgrade step from version 771 to version 772.
    Database ‘MyDatabase’ running the upgrade step from version 772 to version 773.
    Database ‘MyDatabase’ running the upgrade step from version 773 to version 774.
    Database ‘MyDatabase’ running the upgrade step from version 774 to version 775.
    Database ‘MyDatabase’ running the upgrade step from version 775 to version 776.
    Database ‘MyDatabase’ running the upgrade step from version 776 to version 777.
    Database ‘MyDatabase’ running the upgrade step from version 777 to version 778.
    Database ‘MyDatabase’ running the upgrade step from version 778 to version 779.
    Database ‘MyDatabase’ running the upgrade step from version 779 to version 780.
    Database ‘MyDatabase’ running the upgrade step from version 780 to version 781.

    Database ‘MyDatabase’ running the upgrade step from version 781 to version 782.

    RESTORE LOG successfully processed 13 pages in 0.088 seconds (1.109 MB/sec).

    As we can see in the sequence, first files are restored and then recovery process has upgraded version of database. What this step would do? There are few system tables within the database are altered accommodate changes due to new features/fixes etc. These changes are done as a part of “upgrade”. Few things can be seen via profiler but not everything. This version value can be seen in sys.sysdatabases. I have run query in SQL 2012 and SQL 2014 which is same as version seen in restore command (706 and 782)

    image

    In summary, WITH STANDBY is only supported when both SQL Servers are the same version. We can “see” the database table only by performing recovery. An alternative, if we need read access to the destination databases, is to use replication.

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks | Tagged: , , , , , | 3 Comments »