All geek questions in one place

TSQL syntax for .bak recovery for new db

I need to automate the creation of a duplicate db from the .bak of my production db. I did the operation many times through the graphical interface, but when executed from the command line, various switches confuse me a bit, in particular, file names and confidence in replication.

Just find the TSQL syntax for RESTORE that will do this.

+4
source share
2 answers

Assuming you are using SQL Server 2005 or 2008, the easiest way is to use the "Script" button at the top of the recovery database dialog box in SQL Server Management Studio. This will automatically create a T-SQL script with all parameters / settings configured the way you filled the dialog box.

+10
source

see here: How to restore a database for a new location and name (Transact-SQL) , which has a good example:

This example creates a new database called MyAdvWorks. MyAdvWorks is a copy of the existing AdventureWorks database, which includes two files: AdventureWorks_Data and AdventureWorks_Log. This database uses a simple recovery model. The AdventureWorks database already exists on the server instance, so the files in the backup must be restored to a new location. RESTORATION A FILELISTONLY statement is used to determine the number and names of files in a restored database. Backing up a database is the first backup to a backup device.

USE master GO -- First determine the number and names of the files in the backup. -- AdventureWorks_Backup is the name of the backup device. RESTORE FILELISTONLY FROM AdventureWorks_Backup -- Restore the files for MyAdvWorks. RESTORE DATABASE MyAdvWorks FROM AdventureWorks_Backup WITH RECOVERY, MOVE 'AdventureWorks_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', MOVE 'AdventureWorks_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf' GO

It may also help: Backing up and restoring databases

+2
source

Source: https://habr.com/ru/post/1308133/

More articles:


All Articles