Database Reference
In-Depth Information
create a Database Backup and restore Script
now we need to place files within the subfolder. Let's start by making a backup and restore script and then
use it to create a SQL backup file.
1.
If it is not open already, open SQL Server Management Studio; see Exercise 5-1 for
more details.
2.
using the code in Listing 5-14 to create the backup and restore script in a new
query window.
Tip:
If you want to use our script file, use the File ➤ open ➤ File menu in SQL Server Management Studio
to open the file
C:\_BookFiles\Chapter05Files\Listing 5-14. Backup and Restore DWPubsSales.
sql
”
Listing 5-14.
Backing Up and Restoring the DWPubsSales Database
/************************************************
1) Make a copy of the empty database
before starting the ETL process
************************************************/
BACKUP DATABASE [DWPubsSales]
TO DISK =
N'C:\_BISolutions\PublicationsIndustries\DWPubsSales\DWPubsSales_BeforeETL.bak'
GO
/************************************************
2) Send the file to other team members
and tell them they can restore the database
with this code. . .
************************************************/
-- Check to see if they already have a database with that name…
IF EXISTS (SELECT name FROM sys.databases WHERE name=N'DWPubsSales')
BEGIN
-- If they do, they need to close connections to the DWPubsSales database, with this code!
ALTER DATABASE [DWPubsSales] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END
-- Now they can restore the empty database. . .
USE Master
RESTORE DATABASE [DWPubsSales]
FROM DISK =
N'C:\_BISolutions\PublicationsIndustries\DWPubsSales\DWPubsSales_BeforeETL.bak'
WITH REPLACE
GO
3.
Save the script file into the new
DWPubsSales
folder as the
Backing up and
restoring database.sql
file (Figure
5-39
). To do this, use SQL Server Management
Studio and click the File ➤ Save ➤ current file name ➤ As . . . menu item.
