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'