Copy SQL server database with .bak

Question

I'm trying to copy a SQL Server database in VBA (MS-ACCESS).

So I got this query to create the .bak

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 = 'D:\Backup\'  


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 


DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  


       FETCH NEXT FROM db_cursor INTO @name   
END   


CLOSE db_cursor   
DEALLOCATE db_cursor

then I got this one to restore the database

RESTORE DATABASE SMD
FROM DISK='\\SRV_FILESMTL\SMD\SMD.bak'
WITH 
MOVE 'smd' TO 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\SMD.mdf',
MOVE 'smd_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\SMD_log.ldf',
REPLACE

They are both called in the main form on a button click like this

On Error GoTo Error
    Dim os As String

    lblWait.Visible = True
    Me.Repaint

    CurrentDb.Execute ("qryToBackupServer")
    CurrentDb.Execute ("qryToRestoreDataW7")


    MsgBox ("Data imported successfully")
    lblWait.Visible = False
    Exit Sub
Error:
        MsgBox Err.Description
        lblWait.Visible = False
        Exit Sub

I thought it worked but I realized that the new data is not imported. So I checked if the .bak is created correctly and yes because if I copy the .bak on my computer and restore it using SQL Management studio, it works.

So is there a problem with my query to restore? There's no error, but is it supposed to take 30 secondes because when I copied the .bak, it was 5go and it took 5 minutes?

I was thinking maybe the database is locked because I call those functions in Access with tables that are linked to this database, but I got no error.

is there a problem because I get the .bak from another server?

Thank you

score 3 · Accepted Answer · answered May 01 '13 at 18:08

3

I found out that size of the .bak file was increasing every time I do a backup. So I deleted it and I made another one. It was 33 000 ko instead of 4go...

Now the backup works.

So in VBA, I delete the file before to do the backup.

answered May 01 '13 at 18:08

Marc

16,170
20
76
119

Copy SQL server database with .bak

1 Answers1

Linked