Sometimes, we might need to have a copy of database running on a different server for testing purpose. In that scenario creating a backup and restoring it in a different server would help. In this short tip, I am going to narrate on how to backup an existing database to a location in ‘.BAK’ format and then restore the same BAK file to another database name. Backup operation can be done using ‘BACKUP DATABASE’ and restore operation can be done using ‘RESTORE DATABASE’.
IMPORTANT: Make sure you have proper permissions on the drives you are trying to save BAK file and restoring it, or else you might land up with ‘ACCESS DENIED’ error. So make sure read/write (or else Full control, if required) permissions given to the user under which SQL Server is running.
Check out following code, pretty much self explanatory –
BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012.BAK' GO RESTORE DATABASE NewAdventureWorks2012 FROM DISK = 'C:\AdventureWorks2012.BAK' WITH MOVE 'AdventureWorks2012_Data' TO 'C:\NewAdventureWorks2012_Data.mdf' , MOVE 'AdventureWorks2012_Log' TO 'C:\NewAdventureWorks2012_log.ldf', REPLACE GO