22 December 2010

Install SQL from Script

Install SQL from Script


Script 1:-

http://www.megaupload.com/?d=AN5UQOGN


Script 2:-

http://www.megaupload.com/?d=F3LQIVNZ



Thanks To :- http://www.databasejournal.com/features/mssql/article.php/3672926/Scripting-the-Installation-of-SQL-Server-2005.htm

16 December 2010

Microsoft SQL Server Denali - Service Packs

Microsoft SQL Server Denali description



A comprehensive data platform that is more secure, reliable, manageable and scalable

Microsoft SQL Server code-named “Denali” empowers organizations to be more agile in today’s competitive market. Customers can efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and new user experiences expand the reach of BI to enable meaningful insights.

With SQL Server code-named “Denali” customers will benefit from the following added investments:
· Enhanced Mission-Critical Platform: an enhanced highly available and scalable platform.
· Developer and IT Productivity: new innovative productivity tools and features.
· Pervasive Insight: expanding the reach of BI to business users and end-to-end data integration and management.

SQL Server provides a comprehensive data platform that is secure, reliable, manageable, and scalable for your mission critical applications.

Requirements:
· 32-bit systems:
· Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended.)
· 64-bit systems:
· 1.4 GHz or faster processor
· Minimum of 1 GB of RAM (2 GB or more is recommended.)
· 2.2 GB of available hard disk space

Microsoft SQL Server 2008 R2 RTM - Express

The Microsoft SQL Server 2008 R2 Express RTM is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded applications, lightweight Web Sites and applications, and local data stores. Designed for easy deployment and rapid prototyping, this download includes support for Sysprep, Microsoft's System Preparation Utility for Microsoft Windows operating system deployment.

SQL Server Express is designed for easy deployment and rapid prototyping. This edition is designed to integrate seamlessly with your other server infrastructure investments.

Download from here:
- SQLEXPR_x64
- SQLEXPR_x86
- SQLEXPR32_x86


Microsoft SQL Server2008 R2 RTM - Express with Advanced Services

The Microsoft SQL Server 2008 R2 RTM - Express is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded applications, lightweight Web Sites and applications, and local data stores. Designed for easy deployment and rapid prototyping, this download includes support for Sysprep, Microsoft's System Preparation Utility for Microsoft Windows operating system deployment.

SQL Server Express with Advanced Services is an easy-to-use version of the SQL Server Express data platform that includes the graphical management tool SQL Server Management Studio (SMSS) Express, powerful features for reporting, and advanced text-based search capabilities

Download from here:
- SQLEXPRADV_x64
- SQLEXPRADV_x86


Microsoft SQL Server 2008 R2 RTM - Management Studio Express

The Microsoft SQL Server 2008 R2 RTM - Express is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded applications, lightweight Web applications, and local data stores. Designed for easy deployment and rapid prototyping, this download includes support for Sysprep, Microsoft's System Preparation Utility for Microsoft Windows operating system deployment.

Microsoft SQL Server 2008 R2 Management Studio Express (SSMSE) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server Express. This edition combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

Download from here:
- SQLManagementStudio_x64
- SQLManagementStudio_x86


Microsoft SQL Server 2008 R2 RTM - Express with Management Tools

The Microsoft SQL Server 2008 R2 RTM - Express is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded applications, lightweight Web Sites and applications,, and local data stores. Designed for easy deployment and rapid prototyping, this download includes support for Sysprep, Microsoft's System Preparation Utility for Microsoft Windows operating system deployment.

SQL Server Express with Management Tools is an easy-to-use version of the SQL Server Express data platform that includes the graphical management tool SQL Server Management Studio (SMSS) Express

Download from here:
- SQLEXPRWT_x64
- SQLEXPRWT_x86


SQL Server 2008 Service Pack 2

Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download. These packages may be used to upgrade any edition of SQL Server 2008. We remain committed to providing our customers with essential changes via Service packs. Service Pack 2 contains updates for SQL Server Utility, Data-Tier Application (DAC), as well as integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies.

Service Pack 2 introduces support for a maximum of 15,000 partitions in a database, and includes SQL Server 2008 SP1 Cumulative Update 1 to 8. While keeping product changes contained, we have made significant investments to ease deployment and management of Service Packs. Microsoft SQL Server 2008 Service Pack 1 is not a prerequisite for installing SQL Server 2008 Service Pack 2.

SQL Server 2008 SP2 CTP contains the cumulative updates up to SQL Server 2008 SP1 cumulative update package 8, and fixes to issues that have been reported through our customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting.


System Requirements
- Supported Operating Systems:Windows 7;Windows Server 2003;Windows Server 2008;Windows Server 2008 R2;Windows Vista
Windows 7; Windows Server 2003; Windows Server 2008; Windows Server 2008 R2; Windows Vista
- 32-bit systems (x86)
- PC with Intel or compatible Pentium III 600 MHz or higher processor (1 GHz or faster recommended)
- 64-bit systems (x64, ia64)
- 1GHz or faster processor
- Minimum of 512 MB of RAM (1 GB or more recommended)
- 675 MB of available hard disk space

You can download from here:
- IA64 ;
- x64 ;
- x86 ;

SQL Server 2008 Books Online Release Candidate (RC0)

SQL Server 2008, the next release of Microsoft SQL Server, provides a comprehensive data platform. Books Online is the primary documentation for SQL Server 2008. Download a pre-release preview of Books Online for SQL Server 2008.

Books Online includes the following types of information:
· Setup and upgrade instructions.
· Information about new features and backward compatibility.
· Conceptual descriptions of the technologies and features in SQL Server 2008.
· Procedural topics describing how to use the various features in SQL Server 2008.
· Tutorials that guide you through common tasks.
· Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2008.
· Descriptions of the sample databases and applications that are available with SQL Server 2008.

Requirements:

· NET Framework version 2.0
· Internet Explorer 6.0 SP1 or later
· Windows Installer 4.5


Warning: Do not install this version of the SQL Server 2008 Books Online on a computer running the following:
· Any pre-release versions of Visual Studio 2005 or the .NET Framework 2.0.
· Any SQL Server 2005 Beta release.
· The SQL Server 2005 Community Technology Preview (CTP) June 2005 release.
· The SQL Server 2005 CTP September 2005 release.
Remove these versions from your system before installing this SQL Server 2008 Books Online CTP.

SQL Server 2008 Express Edition Service Pack 1 - 10.00.2531.00

Microsoft SQL Server 2008 Express is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded application clients, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, SQL Server 2008 Express is available at no cost, and you are free to redistribute it with applications. It is designed to integrate seamlessly with your other server infrastructure investments

System Requirements
- Supported Operating Systems: Windows Server 2003; Windows Server 2008; Windows Vista
- 32-bit systems
Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended. Only a single processor is supported.)
Minimum of 256 MB of RAM (1 GB or more is recommended)
1GB of free hard disk space
- 64-bit systems
1.4 GHz or higher processor (2 GHz or faster is recommended. Only a single processor is supported.)
Minimum of 256MB of RAM (1 GB or more is recommended)
1 GB of free hard disk space

You can download from here:


Microsoft SQL Server 2008 Express

Microsoft SQL Server 2008 Express is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded application clients, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, SQL Server 2008 Express is available at no cost, and you are free to redistribute it with applications. It is designed to integrate seamlessly with your other server infrastructure investments.

System Requirements
- Supported Operating Systems: Windows Server 2003 Service Pack 2; Windows Server 2008; Windows Vista; Windows Vista Service Pack 1; Windows XP Service Pack 2; Windows XP Service Pack 3
32-bit systems
- Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended. Only a single processor is supported.)
- Minimum of 256 MB of RAM (1 GB or more is recommended)
- 1GB of free hard disk space
64-bit systems
- 1.4 GHz or higher processor (2 GHz or faster is recommended. Only a single processor is supported.)
- Minimum of 256MB of RAM (1 GB or more is recommended)
- 1 GB of free hard disk space


You can download from here:
- SQLEXPR_x64 ;
- SQLEXPR_x86 ;
- SQLEXPR32_x86 ;

Microsoft SQL Server 2008 Express with Tools

Microsoft SQL Server 2008 Express with Tools (SQL Server 2008 Express) is a free, easy-to-use version of SQL Server Express that includes graphical management tools. SQL Server 2008 Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for small server applications and local data stores.

SQL Server 2008 Express with Tools has all of the features in SQL Server 2008 Express, plus you can easily manage and administer SQL Server 2008 Express with a easy-to-use graphical management tool - SQL Server 2008 Management Studio Basic.

Free to download, free to deploy, and free to redistribute as an embedded part of an application, SQL Server 2008 Express with Tools is the fast and easy way to develop and manage data-driven applications.


System Requirements
- Supported Operating Systems: Windows Server 2003 Service Pack 2; Windows Server 2008; Windows Vista; Windows Vista Service Pack 1; Windows XP Service Pack 2; Windows XP Service Pack 3
32-bit systems
- Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended. Only a single processor is supported.)
- Minimum of 512 MB of RAM (1 GB or more is recommended)
- 1.9GB of free hard disk space
64-bit systems
- 1.4 GHz or higher processor (2 GHz or faster is recommended. Only a single processor is supported.)
- Minimum of 512MB of RAM (1 GB or more is recommended)
- 1.9 GB of free hard disk space

You can download from here:
- SQLEXPRWT_x64 ;
- SQLEXPRWT_x86 ;

Microsoft SQL Server 2005 Express Edition

Microsoft SQL Server 2005 Express Edition is the free, easy-to-use, lightweight version of SQL Server 2005.

SQL Server Express makes it easy to develop data-driven applications that are rich in capability, offer enhanced storage security, and are fast to deploy. SQL Server Express is free to redistribute and can be seamlessly upgraded to more sophisticated versions of SQL Server.

SQL Server 2005 Express Edition can be downloaded from here.

Microsoft SQL Server 2008 R2 November Community Technology Preview - Express Edition

The Microsoft SQL Server 2008 R2 Express November Community Technology Preview (CTP)is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded applications, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, this Community Technology Preview (CTP) includes support for Sysprep, Microsoft's System Preparation Utility for Microsoft Windows operating system deployment.

The CTP is being made available for your review in four Editions:
- Express Base : Designed for easy deployment and rapid prototyping, this edition is designed to integrate seamlessly with your other server infrastructure investments.

- Express with Tools : This edition is an easy-to-use version of the SQL Server® Express data platform that includes the graphical management tool SQL Server Management Studio (SMSS) Express. It is ideal for small server applications and local data stores.

- Express with Advanced Services : This edition is an easy-to-use version of the SQL
Server Express data platform that includes an advanced graphical management tool and powerful features for reporting and advanced text-based searches. It is ideal for small server applications and local data stores.

- Tools only : Also called Microsoft SQL Server 2008 R2 Management Studio Express (SSMSE), this is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. This edition combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels. Developers get a familiar experience, and database administrators get a single comprehensive utility that combines easy-to-use graphical tools with rich scripting capabilities.

SQL Server 2005 Express Edition November Community Technology Preview can be downloaded from here.

Here are some key features of "Microsoft SQL Server Denali":

Trusted, Scalable Platform :
· Supporting data consistency across heterogeneous systems through SQL Server Master Data Services, enabling high-scale complex event-stream processing through SQL Server StreamInsight, and supporting scale-up scenarios for the largest available x64 and Itanium hardware (up to 256 logical processors).

IT & Developer Efficiency :
· Enabling administrators to centrally monitor and manage multiple database applications, instances or servers, accelerating the development and deployment of applications and providing improved support for virtualization through Hyper-V with Live Migration in Windows Server 2008 R2.

Managed Self-Service BI:
· Expanding powerful BI tools to all users with SQL Server PowerPivot for Excel and empowering a new class of business users to build and share powerful BI solutions with little or no IT support, while still enabling IT to monitor and manage user-generated BI solutions.

What's New in This Release: [ read full changelog ]

· SQL Server Utility. After you apply SP2, an instance of the SQL Server 2008 Database Engine can be enrolled with a utility control point as a managed instance of SQL Server. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online.

· Data-tier Application (DAC). Instances of the SQL Server 2008 Database Engine support all DAC operations after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.


Thanks To :- http://www.softpedia.com/get/Internet/Servers/Database-Utils/Microsoft-SQL-Server.shtml

07 December 2010

Migration Checklist

Thanks to :- http://www.mssqltips.com/tip.asp?tip=1936

Pre-Migration Checklist

  1. Analyze the disk space of the target server for the new database, if the disk space is not enough add more space on the target server
  2. Confirm the data and log file location for the target server
  3. Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
  4. Collect the information of dependent applications, make sure application services will be stopped during the database migration
  5. Collect the information of database logins, users and their permissions. (Optional)
  6. Check the database for the Orphan users if any
  7. Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers)
  8. Check, if the database is part of any maintenance plan

Below are various scripts you can run to collect data.

Script to Check the Disk and Database Size

-- Procedure to check disc space
exec master..xp_fixeddrives
-- To Check database size
exec sp_helpdb [dbName]
or
use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2) + 'MB'
from dbo.sysfiles
GO

Script to Check Database Properties

select
sysDB.database_id,
sysDB.Name as 'Database Name',
syslogin.Name as 'DB Owner',
sysDB.state_desc,
sysDB.recovery_model_desc,
sysDB.collation_name,
sysDB.user_access_desc,
sysDB.compatibility_level,
sysDB.is_read_only,
sysDB.is_auto_close_on,
sysDB.is_auto_shrink_on,
sysDB.is_auto_create_stats_on,
sysDB.is_auto_update_stats_on,
sysDB.is_fulltext_enabled,
sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

Another Script to Check Database Properties

declare @dbdesc varchar(max)
declare @name varchar(10)
set @name='Master'
SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@name,'Version'))

-- These props only available if db not shutdown
IF DatabaseProperty(@name, 'IsShutdown') = 0
BEGIN
SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
END

-- These are the boolean properties
IF DatabasePropertyEx(@name,'IsAutoClose') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
IF DatabasePropertyEx(@name,'IsInStandby') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
IF DatabasePropertyEx(@name,'IsNullConcat') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
IF DatabasePropertyEx(@name,'IsMergePublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
IF DatabasePropertyEx(@name,'IsPublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
IF DatabasePropertyEx(@name,'IsSubscribed') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup'
SELECT @dbdesc

Script to List Orphan Users

sp_change_users_login 'report'
GO

Script to List Linked Servers

select  *
from sys.sysservers

Script to List Database Dependent Jobs

select
distinct
name,
database_name
from sysjobs sj
INNER JOIN sysjobsteps sjt on sj.job_id = sjt.job_id

Database Migration Checklist

These are the steps you would go through to make the change.

1. Stop the application services

2. Change the database to read-only mode (Optional)

-- Script to make the database readonly
USE [master]
GO
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [DBName] SET READ_ONLY
GO
3. Take the latest backup of all the databases involved in migration

4. Restore the databases on the target server on the appropriate drives

5. Cross check the database properties as per the database property script output, change the database properties as per the pre migration- checklist

Script to Change DB Owner

This will change the database owner to "sa". This can be used to change to any owner you would like.

 USE databaseName
EXEC sp_changedbowner 'sa'

Script to Turn on Trustworthy Option

If trustworthy option was set, this will turn it on for the database.

 ALTER DATABASE database_name SET TRUSTWORTHY ON

Script to Change the Database Compatibility Level

When you upgrade to a new version, the old compatibility level will remain. This script shows how to change the compatibility level to SQL Server 2005 compatibility .

ALTER DATABASE DatabaseName
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel DatabaseName, 90;
GO
ALTER DATABASE DatabaseName
SET MULTI_USER
GO

6. Execute the output of Login transfer script on the target server, to create logins on the target server you can get the code from this technet article: http://support.microsoft.com/kb/246133.

7. Check for Orphan Users and Fix Orphan Users

Script to Check and Fix Orphan Users

-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

8. Execute DBCC UPDATEUSAGE on the restored database.

Run the DBCC UPDATEUSAGE command against the migrated database when upgrading to a newer version of SQL Server.

DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
DBCC CHECKDB
OR
DBCC CHECKDB('database_name') WITH ALL_ERRORMSGS

9. Rebuild Indexes (Optional) As per the requirement and time window you can execute this option.

Take a look at this tip to rebuild all indexes.

This will rebuild or reorganize all indexes for a particular table.

Index Rebuild :- This process drops the existing Index and Recreates the index.
Index Reorganize :- This process physically reorganizes the leaf nodes of the index.

-- Script for Index Rebuild
USE [DBName];
GO
ALTER INDEX ALL ON [ObjectName] REBUILD
GO
-- Script for Index Reorganize
USE AdventureWorks;
GO
ALTER INDEX ALL ON [ObjectName] REORGANIZE
GO

10. Update index statistics

sp_updatestats

11. Recompile procedures

Take a look at this tip to recompile all objects.

This will recompile a particular stored procedure.

sp_recompile 'procedureName'

12. Start the application services, check the application functionality and check the Windows event logs.

13. Check the SQL Server Error Log for login failures and other errors

Take a look at this tip on how to read SQL Server error logs.

EXEC xp_readerrorlog 0,1,"Error",Null

14. Once the application team confirms that application is running fine take the databases offline on the source server or make them read only

-- Script to make the database readonly
USE [master]
GO
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [DBName] SET READ_ONLY
GO
-- Script to take the database offline
EXEC sp_dboption N'DBName', N'offline', N'true'
OR
ALTER DATABASE [DBName] SET OFFLINE WITH
ROLLBACK IMMEDIATE

Next Steps

  • Test the process to determine how much time and disk space would be needed by using the backup and recovery process.
  • Meet with your technical and business teams to find out how much time is available for the migration and plan the activity
  • Design the rollback plan, if the application is not working fine
  • Add more migration cases in your checklist, for example check if the database requires any server level change (For example CLR, XP_Cmdshell etc)
  • Some of these scripts give you the base command to update a portion of the data, enhance the process to hit each object in your database.