teradyne
1/31/2012 - 6:45 PM

SQL Script to backup database to a file path

SQL Script to backup database to a file path

//SQL Script to backup database to a path using Query Analyzer

declare @dbname varchar(50)
declare @filestamp varchar(20);
declare @backuppath varchar(50); 
declare @filepath varchar(200); 

set @backuppath = 'C:\backups\data\full\mylivedb\';
set @filestamp = replace(replace(convert(varchar, getdate(), 120), ' ', '_'), ':', '');
set @filepath = @path + @filestampname + '.bak';
backup database @dbname to disk = @filepath;


/* restore database from a pre-existing .bak file */
restore database mylivedb_20110708
from disk  = 'G:\Projects\projectname\data\myproject_2011-07-08_132621.bak'
with  
MOVE 'mylivedb' TO 'G:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Data\mylivedb_2011-07-08_Data.MDF',  
MOVE 'mylivedb_Log' TO 'G:\Program