A Script A Day - Day 29 - The Importance of Being Idle

Today’s script is also one I used in my migration on Wednesday.  It again uses string manipulation to generate a script, this time the restore database script.  Now granted this quick script wouldn't work if there are any secondary data files and is reliant on the logical file names and file locations etc etc. 

The point of me using this script is because I am very anal when it comes to standards, I like to make sure drive letters and paths are consistent as well as naming conventions for all databases and objects.  Adhering to standards makes your life as a DBA much easier especially when it comes to tasks like migrations.

/*

      -----------------------------------------------------------------

      The Importance of Being Idle (Results To Text Ctrl+T)

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE master;

GO

-- Create restore database script

SELECT

      'RESTORE DATABASE ' + [name] + ' FROM DISK = ''D:\Migration\Backup\' + [name] + '_migration_20120301.bak''' +

      ' WITH REPLACE, MOVE ''' + [name] + '_Data''' + ' TO ''D:\Data\' + [name] + '_Data.mdf'',' + ' MOVE ''' + [name] + '_Log''' + ' TO ''L:\Log\' + [name] + '_Log.ldf'';'

FROM

      sysdatabases;

GO

Enjoy!

Chris

A Script A Day - Day 26 - RESTART a Database Restore

Today’s script is another one based on database restores.  There is a little known clause of the RESTORE DATABASE command called RESTART.  The name of this clause is deceptive, it does not RESTART anything and should actually be called RESUME or CONTINUE.  What it allows you to do is to resume a restore that has failed for a reason other than a SQL Server issue (backup consistancy, lack of space etc).

Take the following example which is simulated by the script.  You start a large backup lets say 500GB restoring before you leave the office one evening which you know from previous restores takes about 4 hours to run.  Now being a DBA you will of course log on ;) to check the restore is succesfull and finish any other tasks required before the database is ready to be used.  You notice the database is in a RESTORING state but your query window with the restore command has errored, after a bit of digging you find that 3 hours and 50 minutes into the restore the server lost power and went down (assume this is a development environment with no UPS or fail over) and the IT operations team brought the server back online.

If you where to restore the database again then it would take another 4 hours to run but by using the RESTART clause the database restore will only take about another 10 minutes!

Now I haven’t used this in anger but have tested it quite a few times on SQL Server 2008 and SQL Server 2008 R2 and have never had a problem.

/*

      -----------------------------------------------------------------

      RESTART a Database Restore

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE SQLServer365;

GO

-- Get a list of database files and locations for the restore

sp_helpfile;

GO

-- Change database context

USE master;

GO

-- Backup database

BACKUP DATABASE SQLServer365 TO DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

GO

--Restore Database

RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',

MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

-- STOP SQL SERVER SERVICE WHILE RESTORING

-- Error Message Received on client

-- START SQL SERVER SERVICE

-- There will be a message in SQL log like the below

-- The database 'SQLServer365' is marked RESTORING and is in a state that does not allow recovery to be run.

-- At this point he database is marked as RESTORING and is inaccesible.

-- Change database context

USE master;

GO

-- Restore the database with the RESTART option (this resumes the restore, honestly!)

RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'

WITH REPLACE, RESTART, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',

MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

-- Results

/*

Processed 18192 pages for database 'SQLServer365', file 'SQLServer365_Data' on file 1.

Processed 1 pages for database 'SQLServer365', file 'SQLServer365_Log' on file 1.

RESTORE DATABASE successfully processed 18193 pages in 19.852 seconds (7.159 MB/sec).

*/

Enjoy!

Chris

A Script A Day - Day 16 - Database Restore

Today’s script is one I have used more times that I care to remember.  As a DBA database backups and restores are your bread and butter, they are second nature (or should be;).  By this I mean good old T-SQL not using SSMS, I can honestly say I have never backed up or restored a database using SSMS, I have used EM in SQL 2000 in my pre DBA days though.

So for all you DBA’s forgive the basic nature of the script but not everyone knows T-SQL and thus wouldn’t be able to restore a database using it.  See my Database 101 post on humility. 

For everyone else the script will restore the SQLServer365 database from a backup and will overwrite the existing files.  It uses RESTORE FILELISTONLY to get the files in the backup and sp_helpfile to get the existing file locations.  The LocicalName column values from RESTORE FILELISTONLY are used to specify what we are MOVEing and the filename column values from sp_helpfile are used to specify where we are MOVEing them TO.

/*

      -----------------------------------------------------------------

      Restore Database

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Set database to the user database

USE SQLServer365;

GO

-- Get file list from backup

RESTORE FILELISTONLY FROM DISK = 'D:\Backups\SQLServer365_20120222.bak';

GO

-- Return database file locations

EXEC dbo.sp_helpfile;

GO

-- Set database context to master

USE master;

GO

-- Restore the database

RESTORE DATABASE SQLServer365

FROM DISK = 'D:\Backups\SQLServer365_20120222.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf', MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

Enjoy!

Chris

Database uplevel Restores Follow On

Today's post is a follow on from one earlier this month Database uplevel Restores.  I got round to testing restoring a SQL Server 2000 backup to a SQL Server 2008 R2 server and it worked!  Details Below;

-- Restore command

RESTORE DATABASE [SQL2000Test] FROM DISK = 'D:\SQL2000TestBackup.bak'

WITH REPLACE, MOVE 'SQL2000Test_Data' TO 'D:\Test\SQL2000Test_Data.mdf',

MOVE 'SQL2000Test_Log' TO 'D:\Test\SQL2000Test_Log.ldf';

Message Output;

Processed 856 pages for database 'SQL2000Test', file 'SQL2000Test_Data' on file 1.

Processed 1 pages for database 'SQL2000Test', file 'SQL2000Test_Log' on file 1.

Converting database 'SQL2000Test' from version 539 to the current version 661.

Database 'SQL2000Test' running the upgrade step from version 539 to version 551.

Database 'SQL2000Test' running the upgrade step from version 551 to version 552.

Database 'SQL2000Test' running the upgrade step from version 552 to version 611.

Database 'SQL2000Test' running the upgrade step from version 611 to version 621.

Database 'SQL2000Test' running the upgrade step from version 621 to version 622.

Database 'SQL2000Test' running the upgrade step from version 622 to version 625.

Database 'SQL2000Test' running the upgrade step from version 625 to version 626.

Database 'SQL2000Test' running the upgrade step from version 626 to version 627.

Database 'SQL2000Test' running the upgrade step from version 627 to version 628.

Database 'SQL2000Test' running the upgrade step from version 628 to version 629.

Database 'SQL2000Test' running the upgrade step from version 629 to version 630.

Database 'SQL2000Test' running the upgrade step from version 630 to version 631.

Database 'SQL2000Test' running the upgrade step from version 631 to version 632.

Database 'SQL2000Test' running the upgrade step from version 632 to version 633.

Database 'SQL2000Test' running the upgrade step from version 633 to version 634.

Database 'SQL2000Test' running the upgrade step from version 634 to version 635.

Database 'SQL2000Test' running the upgrade step from version 635 to version 636.

Database 'SQL2000Test' running the upgrade step from version 636 to version 637.

Database 'SQL2000Test' running the upgrade step from version 637 to version 638.

Database 'SQL2000Test' running the upgrade step from version 638 to version 639.

Database 'SQL2000Test' running the upgrade step from version 639 to version 640.

Database 'SQL2000Test' running the upgrade step from version 640 to version 641.

Database 'SQL2000Test' running the upgrade step from version 641 to version 642.

Database 'SQL2000Test' running the upgrade step from version 642 to version 643.

Database 'SQL2000Test' running the upgrade step from version 643 to version 644.

Database 'SQL2000Test' running the upgrade step from version 644 to version 645.

Database 'SQL2000Test' running the upgrade step from version 645 to version 646.

Database 'SQL2000Test' running the upgrade step from version 646 to version 647.

Database 'SQL2000Test' running the upgrade step from version 647 to version 648.

Database 'SQL2000Test' running the upgrade step from version 648 to version 649.

Database 'SQL2000Test' running the upgrade step from version 649 to version 650.

Database 'SQL2000Test' running the upgrade step from version 650 to version 651.

Database 'SQL2000Test' running the upgrade step from version 651 to version 652.

Database 'SQL2000Test' running the upgrade step from version 652 to version 653.

Database 'SQL2000Test' running the upgrade step from version 653 to version 654.

Database 'SQL2000Test' running the upgrade step from version 654 to version 655.

Database 'SQL2000Test' running the upgrade step from version 655 to version 660.

Database 'SQL2000Test' running the upgrade step from version 660 to version 661.

RESTORE DATABASE successfully processed 857 pages in 0.262 seconds (25.528 MB/sec).

Cheers

Chris

Database uplevel Restores

Today I've been reading Paul Randal's collection of Blogs from a series called A SQL Server DBA myth a day. If you haven't yet read it I recommend it there are a couple of misconceptions in there that I have fallen foul of in the past.  You can also find a link to the SQL Skills site in the SQL Resources section of my Blog.

Perhaps the most obvious misconception (obvious looking back and thinking about it) is listed by Paul as the below;

24x) you can restore a backup to any uplevel version of SQL Server

No. You can only restore a database from two versions back (i.e. you cannot directly restore
a SQL Server 7.0 database to SQL Server 2008).

This isn't something that I knew, I mean sure I've restored / upgraded plenty of databases in my time but never really given the number of uplevel versions a second thought. I imagine this is more a matter of luck rather than judgement on my part.

I don't have any SQL Server 7 databases to support any more but still have the odd SQL Server 2000 database. Going off this I shouldn't be able to restore a SQL Server 2000 database to a SQL Server 2008 R2 server right? I will give this a try and let you all know.

Chris