{"id":2037,"date":"2012-05-31T20:34:30","date_gmt":"2012-05-31T20:34:30","guid":{"rendered":"http:\/\/craftydba.com\/?p=2037"},"modified":"2017-10-11T19:56:38","modified_gmt":"2017-10-11T19:56:38","slug":"upgrading-older-databases","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2037","title":{"rendered":"Upgrading Older Databases"},"content":{"rendered":"

A new version of SQL Server is released every few years. This year was no different with the launch<\/a> of SQL Server 2012 in March. It is important to migrate older, unsupported databases to a current version. I am going to examine 3 different techniques to upgrade the [PUBS] sample database from SQL Server 2000 to 2008 R2. <\/p>\n

The first two techniques require the database files to be copied from the older server to the new one. Use the ALTER DATABASE<\/a> command to kick off the users and set the database off-line. Then copy the files to the target server.<\/p>\n

\r\n-- Kick off users, roll back current work\r\nALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\nGO\r\n\r\n-- Set the database to off-line\r\nALTER DATABASE [WILDLIFE] SET OFFLINE\r\nGO\r\n<\/pre>\n<\/p>\n
For simplicity sake, the MSSQL directory on the C drive will hold both the data and log files.  The first technique uses the CREATE DATABASE<\/a> command with the FOR ATTACH clause.<\/p>\n
\r\n-- Create database - upgrades it\r\nCREATE DATABASE [PUBS] ON\r\n(FILENAME = 'C:\\MSSQL\\Data\\pubs.mdf'),\r\n(FILENAME = 'C:\\MSSQL\\LOG\\pubs.ldf')\r\nFOR ATTACH;\r\n<\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The second technique uses the sp_attach_db<\/a> command to add the database to the default instance.<\/p>\n
\r\n-- Attach database - upgrades it\r\nEXEC sp_attach_db @dbname = N'PUBS',\r\n    @filename1 = N'C:\\MSSQL\\Data\\pubs.mdf',\r\n    @filename2 = N'C:\\MSSQL\\LOG\\pubs.ldf';\r\n<\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The third and last technique depends upon a backup from the SQL Server 2000 server.  I am using the RESTORE FILELISTONLY<\/a> to get details on the backup.<\/p>\n
\r\n-- Get file list\r\nRESTORE FILELISTONLY \r\n   FROM DISK = 'C:\\MSSQL\\BACKUP\\pubs.bak';\r\n<\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The restore technique requires a blank database to exist.<\/p>\n
\r\n-- Define empty db \r\nCREATE DATABASE [PUBS]\r\nON PRIMARY\r\n( NAME = 'PubsData',\r\n    FILENAME = 'C:\\MSSQL\\DATA\\pubs.mdf',\r\n    SIZE = 5MB,\r\n    FILEGROWTH = 5MB)\r\nLOG ON\r\n ( NAME = 'PubsLog',\r\n    FILENAME = 'C:\\MSSQL\\LOG\\pubs.ldf',\r\n    SIZE = 2MB,\r\n    FILEGROWTH = 2MB)\r\nGO\r\n<\/pre>\n<\/p>\n
The RESTORE DATABASE<\/a> command will overwrite the empty database and upgrade the internal structures.<\/p>\n
\r\n-- Restore from backup\r\nRESTORE DATABASE [PUBS] FROM  \r\n    DISK = N'C:\\MSSQL\\BACKUP\\pubs.bak' WITH FILE = 1,  \r\n    MOVE N'pubs' TO N'C:\\MSSQL\\DATA\\PUBS.mdf',  \r\n    MOVE N'pubs_log' TO N'C:\\MSSQL\\LOG\\PUBS.ldf',  \r\n    NOUNLOAD, REPLACE,  STATS = 10\r\nGO\r\n<\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
With all techniques, make sure the capatibility level is set to SQL Server 2008 R2 (100).  It will be at SQL Server 2000 (80) after upgrading the database.  <\/p>\n
\"\"<\/a><\/p>\n
The system databases should never be moved from an older server to a newer one.  If you need objects such as jobs from msdb, script them out and install them on the target server by executing the script.  In summary, keep up to date with server versions so that you are not left in the dust when support is discontinued with your software.<\/p>\n","protected":false},"excerpt":{"rendered":"
A new version of SQL Server is released every few years. This year was no different with the launch of SQL Server 2012 in March. It is important to migrate older, unsupported databases to a current version. I am going to examine 3 different techniques to upgrade the [PUBS] sample database from SQL Server 2000 to 2008 R2. The first two techniques require the database files to be copied from the older server to the new one. Use the ALTER DATABASE command to kick off the users and set the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[50,12,15,386,430,383,28,29],"class_list":["post-2037","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-create-database","tag-free-code","tag-john-f-miner-iii","tag-restore-database","tag-restore-filelistonly","tag-sp_attach_db","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2037","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2037"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2037\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2037"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2037"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2037"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}