Sql-server – SQL server compress backup for lower version databases

Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.

When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.

For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that xp_cmdshell is enabled on the instances, and then use a command line to compact using for e.g. WinRar.

I use a cmd file that looks something like this:

@echo off
Set "winrarPath=C:\Program Files\WinRAR"
"%winrarPath%\winrar.exe" a -r "ZippedBackup.zip" "BackupFile.BAK" 

You can then execute this cmd file from your 2000 / 2005 instances. You can also play around with passing %1-type variables to the cmd file, if your filenames are not generic.

The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it.

As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a simple scan of the database, such as TSQL embedded in applications. You can capture traces of TSQL using SQL Profiler on your SQL Server 2000 server during typical hours and analyze these traces using the Upgrade Advisor.

So the rest of the steps would be :

On the day of migration :

1. script our logins on 2000 server using sp_help_revlogin.
2. Script out jobs and linked servers from sql 2000 server.
3. stop webservers connecting to 2000 server. Make sure that no applications are connecting to the 2000 server.
4. backup your databases and restore on destination sql 2008 R2 server.(note: Do not detach/attach as things could go wrong and you will end up with detached database and no backups !)
5. Once your backups are restored on the 2008 R2 server, run the output from sp_help_revlogin on 2008 R2 server to recreate logins.
6. Sync up orphan users (If any) and recreate sql agent jobs and linked servers on the new server.
7. change compatibility level on the restored databases to 100.
8. Dbcc checkdb with all_errormsgs and data_purity options turned on : DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY
9. run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
10. Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN
11. Optional : Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's scripts.
12. Recompile all SP's using sp_recompile 'procedureName'
13. Refresh your views SP_REFRESHVIEW view_name
14. make sure to change the database option : page verify to CHECKSUM.
15. Change the recovery model (if different from sql 2000) to FULL. If you change to FULL recovery model, then MAKE SURE you do Transaction Log backups frequently. This will help you to recover point-in-time as well as not bloat your T-Log.

16. In SQL Server 2005 and up, Database Mail was introduced. So you have to migrate from SQLMail to Database Mail.

    USE [master]
    GO
    sp_configure 'show advanced options',1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    sp_configure 'Database Mail XPs',1
    GO
    RECONFIGURE 
    GO
    

Also, if you have any replication, then you have to reset it up. If any DR like logshipping or Mirroring (new in 2005 and up, but depreciated in 2012) then you have to reset it up as well.

Old DTS packages needs to be migrated to SSIS using C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSMigrationWizard.exe (command line) or using Package Migration Wizard.

Also, you can use my script found at https://dba.stackexchange.com/a/36701/8783. Though, it uses detach/attach method, I highly recommend you to use BACKUP/RESTORE method. Change the script accordingly.

---

As a side note:

• turn on Instant File Initialization on the new server.
• Have multiple tempdb data files with equal size.
• Enable Trace Flag 1118
• Configure max and min memory correctly. Especially Max memory away from default.
• Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
• Best is to install sp_Blitz from Brent Ozar. Run it and address the critical and high priority issues reported by it.
• You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
• Enable Optimize for ad-hoc workloads and Default backup compression options.

---

Lets address your questions ...

What else should I do to make migration complete?

Refer to my answer. It will help you properly come up with a migration plan. Always test your migration plan in a UAT (non production) along with proper application testing by business users.

use new features like checksumming and full recovery model.

CHECKSUM is new in SQL Server 2005 and up. I have covered it as a part of migration steps described above.

full recovery model is not new. It depends on your business type and dictates on how much data you can lose in case of disaster.

Full recovery mode with frequent Transaction log backups will allow you to restore point-in-time and there by reducing the amount of data loss.

make this database to be exactly as it was created in SQL Server 2008 R2.

make this database to be fully compatible, correct and be perfect suited for new, SQL 2008 R2 database engine.

Don't fully understand this ! But above migration steps will help you. You just have to restore the database and change the compatibility level 10 100 along with above steps.

I just want to know how to correctly and completely convert old SQL Server 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

You have to be careful with this, as this will require changes to your application code as well. If your application code is changed to use the new features in SQL Server 2008 R2, then you wont be encountering any issues - PROVIDED you have fully done a complete regression testing of your application in a UAT or DEV environment. This will give you best confidence when you do the actual migration in PROD.

---

Note: Above are steps that I could remember and I am pretty sure that nothing is left out. If I see that I have missed some stuff, then I will add it or other experts on this site -- feel free to add !

Everything that is outlined above needs to first replayed on a NON PRODUCTION environment to avoid any surprises during the actual migration.

----------

Few more questions:

You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem.

If everything worked fine and you were able to attach the database, then NO you wont be having any problems. Detach/Attach vs Backup/Restore is just a method on how you move your database/s to a different place. Just FYI .. Backup/Restore is more secure and reliable as if anything goes wrong (in worst cases) then atleast you have a backup to restore and recover your database/s.

About checksumming and full recovery model: it wasn't available/enabled on SQL Server 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking.

As I said, checksum is new in version 2005 and up. It is a mechanism by which SQL Server will detect page corruption especially due to I/O. Refer to my answer here for more details.

To enable CHECKSUM as well as change the recovery model to FULL, you can do it using below T-SQL code :

USE master;
GO
ALTER DATABASE [your_database_name] -- change this !!
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

Note : Once you set the database options, it will be persisted when you do migration from 2008R2 to 2012.

I'm preparing to migrate this database to SQL Server 2012 - so first it was from 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of 2000 databases in SQL Server 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right?

Yes please. As I said, backup restore is the preferred method, unless you have a good reason not to do it.

Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?

Backup/restore is ... similar to dump and load used in Sybase, Oracle or probably MySQL as well. Its just SQL Server calls it .. backup/restore.

A must read : Understanding SQL Server Backups by Paul Randall.

Simple Syntax(for Full syntax refer BOL) :

backup database database_name
to disk = 'D:\backup\database_name_full.bak'
with init, stats =10

Then restore can be done on destination server as :

-- assuming the destination's disk layout doesn't match the source server's

restore database database_name
from disk = 'D:\backup\database_name_full.bak'
move 'logical_data_fileName' to 'physical_path\database_name.mdf'
move 'logical_log_fileName' to 'physical_path\database_name_log.ldf'
with recovery, stats = 10

-- assuming the destination's disk layout matches the source server's

restore database database_name
from disk = 'D:\backup\database_name_full.bak'
with recovery, stats = 10

Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually?

backup/restore will not defragment your database. You have to use Alter Index Reorganize or Rebuild depending on your fragmentation level.

Since you are new to SQL Server I would highly recommend you to use Ola Hallengren's :

• SQL Server Index and Statistics Maintenance - for defragmenting your Indexes
• SQL Server Backup -- for taking care of your backups
• SQL Server Integrity Check -- for checking database consistency of your databases

As we were using SQL Server 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL Server 2008, isn't this still better than using backup function in Management Studio?

Stopping engine is the worse thing that you can do to do a backup !!

Read Paul's link about the backups that I mentioned and Use Ola's script. Microsoft has a KB article with the script to do automated backups - How to schedule and automate backups of SQL Server databases in SQL Server Express

Full recovery mode with frequent Transaction log backups - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly?

Every SQL Server database has a log that records all the transactions and database modifications made by each transaction. The transaction log is a critical component of any database.

The usual naming convention extension for Transaction log is '.LDF', but it can be any.

I am not going to write more on this as this will make the answer very leanthy. Refer to Transaction Log Management and my answer here has excellent links as well.

---

EDIT : 8/24/2016 .. This will help future readers :

If you are migrating your entire instance from one version to another version, I would highly recommend to use PowerShell based solution Start-SqlMigration