sql server database backup and restore

--Step 1: Check the logical file names with the help of the following command: RESTORE FILELISTONLY FROM DISK = 'E:\DBBackups\mydb.bak' --Step 2: Use the logical names you get from the above query in the below query: RESTORE DATABASE [mydb_new] FILE = N'<MDFLogicalName>' FROM DISK = N'E:\DBBackups\mydb.bak' WITH FILE = 1 ,NOUNLOAD ,STATS = 10 ,MOVE N'<MDFLogicalname>' TO N'E:\DBBackups\mydb_new.mdf' ,MOVE N'<LDFLogicalName>' TO N'E:\DBBackups\mydb_new_0.ldf'

sql server backup database

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
 
-- specify database backup directory
SET @path = 'V:\SQL_Backup\01_Diaria\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
   WITH INIT, COMPRESSION
 
   FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Posted by: Guest on March-04-2021

Code answers related to "sql server database backup and restore"

Browse Popular Code Answers by Language

Answers for "sql server database backup and restore"

Code answers related to "sql server database backup and restore"

Code answers related to "SQL"

Browse Popular Code Answers by Language

Popular Programming Languages

Advertisements

Company

Compilers

Help

Connect with us