I use sql backup and restore frequently when testing. It’s a great way to ensure that every test starts at the same point, and the TRANSACT-SQL backup and restore commands make it easy to do.
Here are some scripts and commands that I frequently use with backup and restore.
Backup a database to a disk file
backup database caretrac to disk='c:\mybackupfile.bak'
If you need to (for example, if you lack enough local disk space) you can back up to a network path. However, remember that the sql server service will need permissions to that path. The account used by SQL Server will be the one in the services control panel, on the Log On tab.
Restore a database from a disk file
You can’t restore a database while it’s in use, so this script kills any active connections to the database then restores it. If you’re not sure about the logical names in the BAK file, you can use the RESTORE FILELISTONLY command to check them. (syntax is RESTORE FILELISTONLY from disk=’c:\mybackupfile.bak’)
use master go declare @spid varchar(25) declare xcn cursor local fast_forward read_only for select b.spid from master.dbo.sysdatabases a join master.dbo.sysprocesses b on b.dbid=a.dbid where a.[name]='caretrac' open xcn fetch next from xcn into @spid while @@FETCH_STATUS=0 begin exec ('kill ' + @spid) fetch next from xcn into @spid end close xcn deallocate xcn go restore database [caretrac] from disk='M:\backups\caretrac.bak' with replace, move 'caretrac' to 'M:\DATA\caretrac.MDF', move 'caretrac_log' to 'M:\DATA\caretrac_log.LDF'
After the restore completes…
User IDs in a database are identified with a SID. When you restore a database from a different server, the SIDs for user ids won’t match even if the user ids are the same. You’ll need to use this command to sync any affected database user ids:
EXEC sp_change_users_login 'Update_One', 'support_user', 'support_user'