Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Anyone have any tips and tricks when it comes to restoring SQL Server databases?

I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the following issues (so far):

All of the identity insert values were reset to their original values (1 in my case). A pain in the butt until I found the following command to go through all tables, and reseed to the current value + 1:

 exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'

Permissions were gone. The database level accounts came over of course (with their role assignments), but most of the DB permissions for each Role were gone...meaning read,insert,delete,execute etc permissions were missing on each table,view, and stored procedure. I wrote some scripts like the below to rectify, but still a pain in the ass.

 select 'GRANT SELECT ON ' + TABLE_NAME + ' TO rolename' from Information_schema.tables      

Instance level permissions obviously aren't present if restoring to a new instance...but that was kind of a pain since it seems you can't recreate an instance level account if the account already exists at the database level. What's the best approach here? In my case I deleted the database account, the created the instance, then granted permissions using scripts like the above.

Generally I'm just curious about some best practices, when it comes to restoring SQL Server databases... and/or tips on somehow avoiding the issues I encountered. I'm just trying to pave the way for the most error free restores I can.

share|improve this question
2  
Can you give more detail as to how you restored the backup? This does not sound right at all. A backup taken and restored will contain all the objects and permissions. Was this perhaps a really old backup? –  datagod Nov 28 '11 at 20:33
    
I ran the backup on one server (right click on DB, All Tasts, Backup), created a .bak file, transferred the .BAK file, and then right clicked on the other server/instance, and selected Restore Database, and pointed it at the .BAK file. –  Albert Nov 30 '11 at 15:26
    
Try running the backup again using "with verify". You need to make sure the backup is valid before restoring it. Something is very wrong here. –  datagod Nov 30 '11 at 18:42

3 Answers 3

up vote 5 down vote accepted

It looks like you did not restore a backup but you just transferred the tables with some kind of bcp tool.

Common issues with backup/restore are, in my experience: a) Logins missing or mismatched, because they are in the master database b) Collation problems when the collation of the server on which you restore the backup is not the same as the one from which you made the backup.

I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION".

share|improve this answer
    
"I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION"." This was it. I did restore a backup...but it was a backup of the slave database in a log-shipping setup. Thanks! –  Albert Dec 7 '11 at 18:56

A database backup when restored will not do what you have described. Identity values will remain where they are. Permissions will all stay where they are.

Logins can be created to match the user if the user already exists. This can be done by specifying the SID for the login when it's created. Look up CREATE LOGIN in books online and it'll give you the syntax to do this. You can get the correct SID from the sys.database_principals system catalog in your database.

share|improve this answer

for a database that is NOT involved in replication I have added my notes below.

-- script to restore database IntEng2
-- to a point in time 22-aug-2014 at 11:30 AM
-- marcelo miorelli
-- 22-aug-2014

--================================================
-- 6 THINGS TO DO BEFORE DOING A RESTORE:
--================================================

---------------------------------------
--> 1 - DISABLE THE IEPs and other processes that might be using that database
---------------------------------------

-- in TEST 
-- IEBOCSSLON1.DEV.BODEN.LOCAL


-- in LIVE
-- to disable IEPs in live - use the website
-- to start - use the services as explained below:

-- to start them use the server IEBOCSSLON1.DEV.BODEN.LOCAL
-- connect to services on your local computer -> Action -> Connect to Another Computer -> Connecto to 
-- IEBOCSSLON1.DEV.BODEN.LOCAL and select one by one the services you want to start\restart - example - 
-- IEPSVC_ARCLON1-01

---------------------------------------
--> 2 - SCRIPT THE LOGINS, USERS AND PERMISSIONS ON THE CURRENT DATABASE
---------------------------------------

---------------------------------------
--> 3 - BACKUP THE CURRENT DATABASE
---------------------------------------
BACKUP DATABASE [IntEng2] TO  
DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup__DO_not_DELETE_2014_08_22.BAK' 
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'IntEng2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 1
GO

---------------------------------------
--> 4 - VERIFY THE BACKUP FILE -- SEE WHAT IS IN IT 
---------------------------------------
RESTORE FILELISTONLY FROM DISK=N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
--- CHECK MDF ,NDF AND log files with size

RESTORE LABELONLY FROM DISK=N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
--- MEDIA set,software name, software vendoreid

RESTORE HEADERONLY FROM DISK=N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
-- CHECK Backup present in compatibility level, software version major,software version major

RESTORE VERIFYONLY FROM DISK=N'C:\Backup\CreditBackup100\CREDITbACKUP100.BAK'
-- restore just verify the backup file

---------------------------------------
--BEFORE RESTORING - ALWAYS CHECK THE AVAILABLE DISK SPACE
--> 5 - Free space by drive.
EXEC master..xp_fixeddrives
---------------------------------------

---------------------------------------
--> 6 - see if the database is involved in replication
---------------------------------------
SELECT 
    name as [Database name],
    CASE is_published 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Published],
    CASE is_merge_published 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Merge Published],
    CASE is_distributor 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Distributor],
    CASE is_subscribed 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

---------------------------------------
-- 6.1 --> if the database is involved in replication 
-- Have a look at the documentation - in this same folder (Visual Source Safe) 
-- called - Restore - replicated database - SQLAPPLON1 - Product.sql
---------------------------------------


--================================================
-- RESTORE THE DATABASE TO A POINT IN TIME - the database IntEng2 is NOT replicated
--================================================

-- put database in single user mode - only me - the man doing the job ;)
USE [master]
ALTER DATABASE [IntEng2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- backup the tail of the log
BACKUP LOG [IntEng2] TO  DISK = N'H:\SQLBackups\IntEng2_LogBackup_2014-08-22_14-48-05.bak' WITH NOFORMAT, NOINIT,  NAME = N'IntEng2_LogBackup_2014-08-22_14-48-05', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5


-- restore the full backup - with norecovery
-- with replace on the log file because for some reason this database has 2 log files and we want to keep it like that
RESTORE DATABASE [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak' 
WITH  FILE = 1,  MOVE N'IntEng2_Log2' TO N'G:\SQLLog\IntEng2_Log2.ldf',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5


-- resotore the log files - IN THE SPECIFIC ORDER ---
-- with NORECOVERY

RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_030002_2577183.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_031501_5336409.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_033001_6524314.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_034501_7888108.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_040002_3541104.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_041506_9327770.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_043001_9532881.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_044502_0339177.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_050002_0942167.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_051506_3762732.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_053005_8234237.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_054505_2202118.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_060002_3445813.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_061502_1611989.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_063002_2204240.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_064501_8698190.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_070002_1582584.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_071501_8781090.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_073002_2857856.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_074502_4935206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_080001_8744757.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_081501_8779343.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_083001_7488322.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_084501_5171402.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_090002_4606122.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_091501_9724206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_093002_3221461.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_094502_0465413.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_100001_7103846.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_101501_8737945.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_103002_2375779.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_104501_8634658.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_110002_4519941.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_111501_5088114.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5


-- restore the log closest to the time when the disaster happened
-- this will be the restore with STOP AT
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_113001_5819154.trn' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5,  
STOPAT = N'2014-08-22T11:30:00'


---------------------------------------
--> 2 - FROM THE STEP 2 EARLIER ON -
-- APPLY THE SCRIPT THE OF LOGINS, USERS AND PERMISSIONS ON THE CURRENT DATABASE
-- DEAL WITH TEH PERMISSIONS
---------------------------------------


--================================================
-- BRING THE DATABASE BACK TO LIFE
--================================================
-- have a look at the database,
-- if everything is ok
-- bring the database back to multiuser
ALTER DATABASE [IntEng2] SET MULTI_USER

GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.